Помогите составить sql запрос
Доброго дня всем.
Пол дня сидел, пытаясь сформулировать запрос. Не выходит и всё тут.
Вобщем, есть 2 таблицы:
phpbb_users:
user_id | user_rating | ...
И есть songs:
song_id | user_id | rating | ...
Задача - выбрать и показать всех юзеров с их рейтингами. Фича в том, что рейтинг юзера считается по формуле: user_rating+сумма_всех_рейтингов_его_песен (rating для песни с соответствующим user_id из songs).
Мой запрос, на данный момент вытаскивает и показывает инфу только по тем юзерам, чьи user_id есть в табле songs (то-бишь тех юзеров, кто уже залил песни).
SELECT u.*, s.*, (SUM(s.rating)+u.user_rating)as user_total_rating FROM phpbb_users u, songs s WHERE u.user_type!='2' AND u.user_id=s.user_id GROUP BY u.user_id ORDER BY user_total_rating DESC
Пол дня сидел, пытаясь сформулировать запрос. Не выходит и всё тут.
Вобщем, есть 2 таблицы:
phpbb_users:
user_id | user_rating | ...
И есть songs:
song_id | user_id | rating | ...
Задача - выбрать и показать всех юзеров с их рейтингами. Фича в том, что рейтинг юзера считается по формуле: user_rating+сумма_всех_рейтингов_его_песен (rating для песни с соответствующим user_id из songs).
Мой запрос, на данный момент вытаскивает и показывает инфу только по тем юзерам, чьи user_id есть в табле songs (то-бишь тех юзеров, кто уже залил песни).
SELECT u.*, s.*, (SUM(s.rating)+u.user_rating)as user_total_rating FROM phpbb_users u, songs s WHERE u.user_type!='2' AND u.user_id=s.user_id GROUP BY u.user_id ORDER BY user_total_rating DESC
Может так:
SELECT u.user_id, (SUM(s.rating)+u.user_rating) AS user_total_rating
FROM phpbb_users u
LEFT JOIN songs s ON u.user_id=s.user_id
WHERE u.user_type!='2'
GROUP BY u.user_id
ORDER BY user_total_rating DESC
SELECT u.user_id, (SUM(s.rating)+u.user_rating) AS user_total_rating
FROM phpbb_users u
LEFT JOIN songs s ON u.user_id=s.user_id
WHERE u.user_type!='2'
GROUP BY u.user_id
ORDER BY user_total_rating DESC
а зачем делать один сложный запрос, когда можно сделать два маленьких?
squirL: например? Сначала вытащить всех тех кто есть в songs, а потом оставшихся из users? если так - то геморр. Потому что там ещё стоит LIMIT (который я не стал указывать в запросе, дабы не усложнять), и морочиться с этими кусками результатов не хочется.
suff84: То что надо! Пробовал JOIN, тока вот основной таблицей (перед left join) ставил songs. А так, добавил там ещё один ORDER - и всё работает как надо :)
suff84: То что надо! Пробовал JOIN, тока вот основной таблицей (перед left join) ставил songs. А так, добавил там ещё один ORDER - и всё работает как надо :)
Самое простое и думаю быстрое это составить два запроса.
1.
и сохранить этот запрос как rating_query
затем
может там ошибка будет с группироской но вроде все должно заработать
1.
Код:
select user_id,sum(rating) as sum_rating from songs group by user_id
и сохранить этот запрос как rating_query
затем
Код:
select phpbb_users.user_id,phpbb_users.user_rating+rating_query.sum_rating
from phpbb_users pu
inner join rating_query rq on pu.user_id=rq.user_id
group by user_id
from phpbb_users pu
inner join rating_query rq on pu.user_id=rq.user_id
group by user_id
может там ошибка будет с группироской но вроде все должно заработать
А зачем разбивать на 2 запроса, если можно сделать все одним, при том что довольно элементарным?)
maxFM, запрос получается такой же по сути. Только нужно не INNER JOIN, а LEFT JOIN, иначе опять пропадут users, которых нет в songs.
Дополнение к своему варианту:
Дополнение к своему варианту:
Цитата: suff84
Может так:
SELECT u.user_id, (SUM(s.rating)+u.user_rating) AS user_total_rating
FROM phpbb_users u
LEFT JOIN songs s ON u.user_id=s.user_id
WHERE u.user_type!='2'
GROUP BY u.user_id
ORDER BY user_total_rating DESC
SELECT u.user_id, (SUM(s.rating)+u.user_rating) AS user_total_rating
FROM phpbb_users u
LEFT JOIN songs s ON u.user_id=s.user_id
WHERE u.user_type!='2'
GROUP BY u.user_id
ORDER BY user_total_rating DESC
Чуть не забыл, здесь также нужно помнить, что в songs есть не все user_id из users, что может привести к появлению NULL-значений в сумме. Так что правильнее будет:
SELECT u.user_id, (ISNULL(SUM(s.rating),0)+ISNULL(u.user_rating,0)) AS user_total_rating
FROM phpbb_users u
LEFT JOIN songs s ON u.user_id=s.user_id
WHERE u.user_type!='2'
GROUP BY u.user_id
ORDER BY user_total_rating
А можно я тут тоже спрошу?) :)
Вобщем запарился соображать, работаю с аксцессом первый раз, так что прошу помощи)
Есть таблица "Книжки" с двумя полями: Количество и Название. В первом прописывается сколько раз читатель брал книгу, а во втором - название этой книги.
Задача у меня в общем такая: Написать запрос на выборку книг, которые брали столько же раз, сколько, например, "Три мушкетера".
Пробовал group-ом, и прочим, но чет всё никак...:confused:
Вобщем запарился соображать, работаю с аксцессом первый раз, так что прошу помощи)
Есть таблица "Книжки" с двумя полями: Количество и Название. В первом прописывается сколько раз читатель брал книгу, а во втором - название этой книги.
Задача у меня в общем такая: Написать запрос на выборку книг, которые брали столько же раз, сколько, например, "Три мушкетера".
Пробовал group-ом, и прочим, но чет всё никак...:confused:
Цитата: Neo1031
А можно я тут тоже спрошу?) :)
Есть таблица "Книжки" с двумя полями: Количество и Название. В первом прописывается сколько раз читатель брал книгу, а во втором - название этой книги.
Задача у меня в общем такая: Написать запрос на выборку книг, которые брали столько же раз, сколько, например, "Три мушкетера".
Есть таблица "Книжки" с двумя полями: Количество и Название. В первом прописывается сколько раз читатель брал книгу, а во втором - название этой книги.
Задача у меня в общем такая: Написать запрос на выборку книг, которые брали столько же раз, сколько, например, "Три мушкетера".
я бы так писала :) :
Код:
select * from Books b1 where exists (select number from Books b2 (where(b1.number = b2.number and b2.title="need mame"));
здесь у меня Books -- это таблица книг
number-- поле сколько раз книгу брали
title -- название книги
need mame -- соответственно нужное название
WOW, it works! Понян-понян, пасибо Вам, Irene_001, буду копать подзапросы значит)) :D
Я что то не очень понял только вот чем тебя sort by и order by неустраивает , просто пишеь запрос а в конце например sort by id , и все у тебя по номеру остсортированы. Или может я чего то не догнал
И, как это обычно бывает, спустя время - формула поменялась... И опять сижу и ломаю голову.. ступор какой-то.
Вобщем, таблицы:
та-же таблица users,
songs и новая таблица albums.
Задача - вытащить рейтинг всех юзеров согласно след формуле:
user_raiting = сумма_рейтингов_всех_песен_конкретного_стиля + сумма_рейтингов_альбомов_конкретного_стиля.
Запрос пока что такой:
Прикол в том, что вытаскиваются только те id юзеров и их рейтинги, у которых есть песни с style_id=3 И альбомы с style_id=3 . Если-же у юзера есть только песни, а альбомов нету (или наоборот) - их не вытаскивает.
Это-то и логично, т.к. стоит условие a.style_id='3' AND s.style_id='3' , однако это условие больше нужно для подсчёта нужных SUM... Опять-таки всё это дело надо реализовать одним единственным запросом.
Вобщем, таблицы:
та-же таблица users,
songs и новая таблица albums.
Задача - вытащить рейтинг всех юзеров согласно след формуле:
user_raiting = сумма_рейтингов_всех_песен_конкретного_стиля + сумма_рейтингов_альбомов_конкретного_стиля.
Запрос пока что такой:
Код:
SELECT u.user_id as user_real_id, ( IFNULL(SUM(DISTINCT s.rating),0) + IFNULL( SUM(DISTINCT a.rating),0 )) as user_total_rating FROM users u, songs s, albums a WHERE s.user_id=u.user_id AND a.user_id=u.user_id AND a.style_id='3' AND s.style_id='3' GROUP BY u.user_id ORDER BY user_total_rating DESC LIMIT 0,10
Прикол в том, что вытаскиваются только те id юзеров и их рейтинги, у которых есть песни с style_id=3 И альбомы с style_id=3 . Если-же у юзера есть только песни, а альбомов нету (или наоборот) - их не вытаскивает.
Это-то и логично, т.к. стоит условие a.style_id='3' AND s.style_id='3' , однако это условие больше нужно для подсчёта нужных SUM... Опять-таки всё это дело надо реализовать одним единственным запросом.
Более того приведенный выше запрос еще и суммы неправильно посчитает даже если у пользователей будут записи и в таблице альбомы и в таблице песни, т.к. декартово произведение таблиц, будет сочетать каждую строку с каждой в результате чего при подсчете суммы для каждого пользователя по колонке рейтинг значение суммы увеличится пропорционально кол-ву строк в таблицах альбомы и песни для этого пользователя.
По-моему тут без подзапросов никак.
Не знаю позволяет ли ваша субд так делать, но я бы написал такой запрос в MS SQL вот так:
По-моему тут без подзапросов никак.
Не знаю позволяет ли ваша субд так делать, но я бы написал такой запрос в MS SQL вот так:
Код:
declare @users table(user_id int, user_rating int)
declare @songs table(song_id int, user_id int, style_id int, rating int)
declare @albums table(album_id int, user_id int, style_id int, rating int)
select
u.user_id,
u.user_rating + isnull(s.rating,0) + isnull(a.rating,0)
from
@users u
left outer join
(
select s.user_id, SUM(s.rating) as rating from @songs s where s.style_id='3' group by s.user_id
) s on (u.user_id = s.user_id)
left outer join
(
select a.user_id, SUM(a.rating)as rating from @albums a where a.style_id='3' group by a.user_id
) a on (u.user_id = a.user_id)
declare @songs table(song_id int, user_id int, style_id int, rating int)
declare @albums table(album_id int, user_id int, style_id int, rating int)
select
u.user_id,
u.user_rating + isnull(s.rating,0) + isnull(a.rating,0)
from
@users u
left outer join
(
select s.user_id, SUM(s.rating) as rating from @songs s where s.style_id='3' group by s.user_id
) s on (u.user_id = s.user_id)
left outer join
(
select a.user_id, SUM(a.rating)as rating from @albums a where a.style_id='3' group by a.user_id
) a on (u.user_id = a.user_id)
Сочетания каждой строки с каждой не произойдёт, там ведь стоит условие - суммировать рейтинги определённых песен и альбомов.
В итоге всё разрешилось следующим запросом:
В итоге всё разрешилось следующим запросом:
Код:
SELECT u.user_id as user_real_id, ( IFNULL(SUM(DISTINCT s.rating),0) + IFNULL( SUM(DISTINCT a.rating),0 )) as user_total_rating
FROM users u
LEFT JOIN songs s ON s.style_id='3' AND s.user_id=u.user_id
LEFT JOIN albums a ON a.style_id='3' AND a.user_id=u.user_id
GROUP BY u.user_id
ORDER BY user_total_rating DESC
FROM users u
LEFT JOIN songs s ON s.style_id='3' AND s.user_id=u.user_id
LEFT JOIN albums a ON a.style_id='3' AND a.user_id=u.user_id
GROUP BY u.user_id
ORDER BY user_total_rating DESC
Ну если гарантируется что значения поля рейтинг уникальны, то оно конечно возможно...но вот если рейтинги песен ведутся или будут вестись в рамках какой либо группы, например "альбома" или "жанра" или "по версии...", будет то что сказао выше...
Не, в том-то и дело что рейтинг Всегда будет считаться отдельно для каждого юзера по той схеме которую я описывал выше