Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите с запросом  [new]
Centraloff
Member

Откуда: Екатеринбург
Сообщений: 138
Добрый день, помогите правильно составить запрос возвращающий сумму балов.

Есть две таблички:
CREATE TABLE [dbo].[Photo](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](150) NOT NULL,
	[dateadd] [smalldatetime] NOT NULL,
	[whoadd] [nvarchar](150) NOT NULL,
	[category] [int] NOT NULL,
	[description] [nvarchar](max) NULL,
	[extension] [nchar](5) NULL,
	[display] [bit] NULL

CREATE TABLE [dbo].[Votes](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[id_photo] [int] NOT NULL,
	[who_votes] [nvarchar](150) NOT NULL,
	[datevote] [smalldatetime] NOT NULL,
	[hostname] [nvarchar](150) NULL,
	[ipadress] [nchar](15) NULL,
	[ball] [smallint] NULL  (это кол-во баллов)


И есть запрос который должен возвращать с записи с суммой баллов
SELECT p.id, [name], [description], [extension], 
CASE WHEN who_votes = @user OR whoadd = @user THEN 0 ELSE 1 END as display_vote, 
ISNULL (SUM (v.ball),0) as vote 
FROM [Photo] as p left join votes as v on v.id_photo=p.id 
WHERE p.category = 1 AND [display]=1 
GROUP BY id_photo,p.id,p.name, p.description, p.extension, who_votes, whoadd order by id


но по запрос не суммирует баллы, а дублирует строки, сколько раз голосовали за эту фотографию, столько строк он и вернет.
Помогите пожалуйста переписать запрос чтобы он правильно работал.
14 дек 13, 13:25    [15292525]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
sdet
Member

Откуда:
Сообщений: 463
Centraloff,

Потому что группировка идет по who_votes, whoadd, а надо только по полям относящимся к фотографии
14 дек 13, 13:40    [15292563]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Centraloff
Member

Откуда: Екатеринбург
Сообщений: 138
sdet,

Вот что говорит на это

Msg 8120, Level 16, State 1, Line 2
Column 'votes.who_votes' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'Photo.whoadd' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
14 дек 13, 13:45    [15292576]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
sdet
Member

Откуда:
Сообщений: 463
Centraloff
sdet,

Вот что говорит на это

Msg 8120, Level 16, State 1, Line 2
Column 'votes.who_votes' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'Photo.whoadd' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Все правильно говорит
Группировку надо делать отдельно (через cross aplly например) если требуется выводить другие поля
14 дек 13, 13:48    [15292582]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Centraloff
Member

Откуда: Екатеринбург
Сообщений: 138
sdet,

Спасибо, поразбираюсь
14 дек 13, 13:51    [15292585]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
sdet
Member

Откуда:
Сообщений: 463
sdet,

Или если версия позволяет используйте sum over()
14 дек 13, 13:51    [15292587]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Centraloff
Member

Откуда: Екатеринбург
Сообщений: 138
sdet,

Чета никак не выходит, пришел вот к таком запросу

SELECT p.id, [name], [description], [extension], sum_vote, display_vote FROM Photo as p left join 
(select ISNULL (SUM (ball),0) as sum_vote, id_photo from votes group by id_photo) as v on p.id = v.id_photo left join
(select CASE WHEN who_votes = @user OR whoadd = @user THEN 0 ELSE 1 END as display_vote, p.id as id_photo
FROM Photo as p left join Votes as v on p.id=v.id_photo) as d on p.id = d.id_photo 
WHERE p.category = 1 AND [display]=1 order by id


возвращает тоже самое, но тут хотя бы понятно что причина в этом подзапросе
select CASE WHEN who_votes = @user OR whoadd = @user THEN 0 ELSE 1 END as display_vote, p.id as id_photo
FROM Photo as p left join Votes as v on p.id=v.id_photo


надо как-то прийти к тому чтобы он возвращал только одну строку привязанную к общему набору данных для обоих случаев, уже голова плохо соображает(((
14 дек 13, 16:47    [15292835]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
sdet
Member

Откуда:
Сообщений: 463
Centraloff,
Версия ms sql какая? И как конечный вариант должен выглядить ( представьте несколько строчек с данными)
14 дек 13, 17:03    [15292875]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Centraloff
Member

Откуда: Екатеринбург
Сообщений: 138
sdet,

версия 2005

сейчас возвращается такой набор

id name description extension sum_vote display_vote
142 namephoto1 description1 .jpg 4 0
142 namephoto1 description1 .jpg 4 1


т.е. строки дублирутся потому что не этот пользователь добавил фотографию и скл возвращает оба варианта
в первой строке display_vote - 0 потому что выполняется условие что этот пользователь уже проголосовал и ему больше нельзя голосовать, а во второй строке display_vote - 1 потому что не он добавил эту фотографию поэтому ему вроде как можно проголосовать

поле display_vote определяет можно ли показывать голосовалку, если этот пользователь добавил фотографию или этот пользователь уже голосовал за неё то её ему показывать нельзя. Вот такая логика
14 дек 13, 17:22    [15292918]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Centraloff
Member

Откуда: Екатеринбург
Сообщений: 138
sdet,

Конкретно этот запрос из за которого проблема
select CASE WHEN who_votes = @user OR whoadd = @user THEN 0 ELSE 1 END as display_vote, p.id as id_photo
FROM Photo as p left join Votes as v on p.id=v.id_photo

возвращает такой набор:

display_vote id_photo
1 141
0 142
1 142
1 143


а должен вернуть такой

display_vote id_photo
1 141
0 142
1 143


вне зависимости от того какое из условий сработало
14 дек 13, 17:53    [15292963]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
sdet
Member

Откуда:
Сообщений: 463
Centraloff,

Приблизительно так, если правильно понял
SELECT p.id, p.[name], p.[description], p.[extension], v.vote_sum,CASE WHEN v.is_vote = 1 OR p.whoadd = @user THEN 0 ELSE 1 END as display_vote 
from [Photo] as p
outer apply (select ISNULL(SUM (ball),0) vote_sum,SUM(CASE WHEN who_votes = @user THEN 1 ELSE 0 END) is_vote  from votes where id_photo=p.id) as v
14 дек 13, 18:18    [15293024]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Centraloff
Member

Откуда: Екатеринбург
Сообщений: 138
sdet,

Спасибо, решил по своему, хотя конечно мне кажется не очень оптимально

SELECT p.id, [name], [description], [extension], sum_vote, display_vote FROM Photo as p left join 
(select ISNULL (SUM (ball),0) as sum_vote, id_photo from votes group by id_photo) as v on p.id = v.id_photo left join
(select MIN (display_vote) as display_vote, p.id as id_photo from Photo as p left join (
select CASE WHEN who_votes = @user OR whoadd = @user THEN 0 ELSE 1 END as display_vote, p.id as id_photo
FROM Photo as p left join Votes as v on p.id=v.id_photo) as v on p.id = v.id_photo GROUP BY p.id
) as d on p.id = d.id_photo 
WHERE p.category = 1 AND [display]=1 order by id
14 дек 13, 18:28    [15293049]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
sdet
Member

Откуда:
Сообщений: 463
Centraloff,
А мой запрос такой же рез возвращает?
SELECT p.id, p.[name], p.[description], p.[extension], v.vote_sum,CASE WHEN v.is_vote >= 1 OR p.whoadd = @user THEN 0 ELSE 1 END as display_vote 
from [Photo] as p
outer apply (select ISNULL(SUM (ball),0) vote_sum,SUM(CASE WHEN who_votes = @user THEN 1 ELSE 0 END) is_vote  from votes where id_photo=p.id) as v
WHERE p.category = 1 AND [display]=1 order by id
14 дек 13, 18:33    [15293055]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Centraloff
Member

Откуда: Екатеринбург
Сообщений: 138
sdet,

Да, спасибо, результат абсолютно одинаковый, ваш вариант конечно заметно компактней, да и наверное быстрей работает, хотя скорость для меня не так важна, записей всего несколько сотен

Еще раз огромное спасибо за помощь
14 дек 13, 18:36    [15293061]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить