比如有两个表,大概结构如下:
table1:
id uid name other
1 1 name1 other1
2 2 name2 other2
3 3 name3 other3
4 4 name4 other4
5 5 name5 other5 table2:
id uid gid score
1 1 gid1 score1
2 1 gid2 score2
3 1 gid3 score3
4 3 gid4 score4
5 5 gid5 score5 我想从table2中查询出uid出现次数最多的数据,比如上面的3次,然后求这3次的score之和,并根据出现次数,调用table1中相对应的name值,并按所得的score之和来排序top10.请高手指教这样的语句该如何写。
table1:
id uid name other
1 1 name1 other1
2 2 name2 other2
3 3 name3 other3
4 4 name4 other4
5 5 name5 other5 table2:
id uid gid score
1 1 gid1 score1
2 1 gid2 score2
3 1 gid3 score3
4 3 gid4 score4
5 5 gid5 score5 我想从table2中查询出uid出现次数最多的数据,比如上面的3次,然后求这3次的score之和,并根据出现次数,调用table1中相对应的name值,并按所得的score之和来排序top10.请高手指教这样的语句该如何写。
SELECT t1.name,
t2.total
FROM table1 t1
JOIN (SELECT t22.uid,
sum(t22.score) total
FROM table2 t22
GROUP BY t22.uid
HAVING COUNT(*) = (
SELECT MAX(COUNT(*))
FROM table2 t21
GROUP BY t21.uid)) t2
ON t1.uid = t2.uid
WHERE rownum < 11
ORDER BY t2.total
稍微改动下应该可以了
t2.total
FROM test.table1 t1
JOIN (SELECT t21.uid,
SUM(t21.score) total
FROM test.table2 t21
GROUP BY t21.uid
HAVING COUNT(*) = (SELECT MAX(t23.num)
FROM (SELECT COUNT(*) num
FROM test.table2 t22
GROUP BY t22.uid) t23 )) t2
ON t1.uid = t2.uid
ORDER BY t2.total
LIMIT 10mysql 测试通过