проблема с sql запросом

[xInOrK]

проблема с sql запросом

Есть три таблицы: users, users_pictures, pictures_votes
Так вот у каждого пользователя может быть N-ое количество фоток, на каждой фотке есть число голосов и сумма оценок.

Мне надо выбрать 100 фотографий, с самой высокой оценкой по одной от пользователя.
Сейчас я делаю так:

SELECT `id`, `login`, `birthday`  FROM `users` WHERE `sex`=1
while(list(...)){
SELECT up.`id`, up.`thumbnail`, (pv.`sum`/pv.`num`) as `score`, pv.`num`
 FROM `users_pictures` as `up`
 LEFT JOIN `pictures_votes` as `pv` ON up.`id`=pv.`pid`
WHERE up.`uid`='$uid' AND up.`accepted`=1 AND (pv.`sum`/pv.`num`)>='5' AND pv.`num`>='5'
 ORDER by (pv.`sum`/pv.`num`) DESC, pv.`num` DESC LIMIT 1
//тут записываю полученные данные в отдельную таблицу (предположим top)
}

Потом сортирую данные и уже ставлю LIMIT 100 при выборке из той таблицы top

Можно ли как-то это сделать одним запросом? Просто когда людей станет очень много и фотографий тоже то очень много времени будет забирать создание таблицы top.

-~{}~ 25.02.06 10:46:

ап. не реально ?

 

[Фанат]

за апанье можно и в корзину улететь.

-~{}~ 25.02.06 12:31:

В таблице pictures_votes есть поле user_id? А почему?

 

[xInOrK]

В таблице pictures_votes есть только pid || sum || num

 

[Фанат]

ну так добавь юзера.
и будешь группировать по юзерам и сортировать сразу

и вообще - что у тебя за корявый подход - выбирать сначала всех юзероы зачем-то...
зачем тебе все юзеры?

 

[xInOrK]

Ну так просто сначала такую структуру сделал вот теперь и мучаюсь

 

[Фанат]

ну так переделай и не мучайся
здесь форум программистов, а не мазохистов.

 

[xInOrK]

Да вот теперь возникла небольшая проблема. Надо как то добавить поле тогда и заполнить его. Так как там в таблице уже существует много данных.

 

[Фанат]

ну так добавь поле и заполни его.

 

[xInOrK]

А вот тут вторая проблема. Переделал я всё как надо. Делаю запрос вот такой:

SELECT pv.`uid` , up.`id` , up.`thumbnail` , (
pv.`sum` / pv.`num` 
) AS `score` , pv.`num`
FROM `users_pictures` AS `up`
LEFT JOIN `pictures_votes` AS `pv` ON up.`id` = pv.`pid` 
WHERE up.`accepted` =1
AND (
pv.`sum` / pv.`num` 
) >= '4'
AND pv.`num` >= '5'
ORDER BY (
pv.`sum` / pv.`num` 
) DESC , pv.`num` DESC

Это так он показывает по несколько фотографий в рейтинге. Если ставлю группировку по uid то он показывает фотку не лучшую у этого пользователя. Как от группировать и достать только по одной самой лучшей ?

 

[Фанат]

да погоди ты с джойнами.
сделай запрос из ОДНОЙ таблицы.
из pictures_votes
построй сначала свой топ.
без юзеров, без картинок. просто ид фотки и рейтинг.

а потом уже привешивай к нему остальное.

 

[xInOrK]

SELECT `pid`, `uid`, `sum`, `num`, (`sum` / `num`) AS `score` FROM `pictures_votes` WHERE `num`>='5' AND (`sum` / `num`)>='4' ORDER by (`sum` / `num`) DESC, num DESC

Такое ты имеешь ввиду?
Ну а как теперь отсюда взять только по одной самой лучшей фотки ? Просто мне надо взять 100 самых лучших, чтобы по одной от пользователя. И ещё хорошо бы чтобы по полу разделять

Просто как видно шас uid повторяются.
pid uid sum num score
2184 493 31 5 6.20
2179 493 32 6 5.33
2174 437 31 6 5.17
1173 277 97 19 5.11
1061 277 100 20 5.00
1261 277 65 13 5.00
1260 277 45 9 5.00
1057 277 118 24 4.92
81 27 54 11 4.91
77 27 44 9 4.89
75 27 58 12 4.83

 

[Фанат]

по одной - группировкой, разумеется

 

[xInOrK]

Ну после группировки получается фотка не с лучшим Рейтингом.

-~{}~ 25.02.06 14:57:

Тоесть как сказать uid на правильном месте а pid фотки не с лучшим рейтингом.

 

[Фанат]

мда, я не подумал.
так мы получим только uid...

 

[xInOrK]

А есть ещё какойнить вариант ? Как вообше такие веши делают ? Может есть вариант структуры таблиц более удобный ?

-~{}~ 27.02.06 07:18:

Так что никто незнает как решить проблемку ?

 

[Фанат]

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

 

[zarus]

SELECT `pid`, `uid`, `sum`, `num`, MAX(`sum` / `num`) AS `score` FROM `pictures_votes` WHERE `num`>='5' AND (`sum` / `num`)>='4' GROUP BY `uid` ORDER by (`sum` / `num`) DESC, num DESC

?

 

[Фанат]

нт, я ж говорю - джойнить.
то есть, сделать джойн в этом запросе.
а джойнить по условию соответствия рейтинга И юзера.
Не уверен, что сработает, но и вопрос твой не форумного формата.
Попробуй

 

[zarus]

Дайте дамп части таблиц, попробуем и так, и так.

 

[Bukk]

Если твой тоp100 показывается чаще, чем непосредственно голосуется как один из вариантов могу предложить сделать следующее:
1) сделать таблицу, например, top (uid, pid, rate) где пишется максимальний рейтинг каринки юзера и id самой этой картинки.
2) При голосовании определяешь рейтинг картинки, по которой проголосовали. Если он больше чем rate, то меняешь pid и rate.
3) Остальное дело техники.

-~{}~ 27.02.06 10:18:

В 1) имеется ввиду картинка с максимальным рейтингом.

-~{}~ 27.02.06 10:23:

Фанат
Понятно, что сработает, но проблемы не снимет, потому что у одного юзера может быть много картинок с одним рейтингом.