這是原始數據,并希望根據得分(count(tbl_1.id))對它們進行排名.
[tbl_1]
===========
id | name
===========
1 | peter
2 | jane
1 | peter
2 | jane
3 | harry
3 | harry
3 | harry
3 | harry
4 | ron
因此,制作臨時表(tbl_2)來計算每個id的分數.
SELECT id, name, COUNT( id ) AS score
FROM tbl_1
GROUP BY id
ORDER BY score DESC;
LIMIT 0, 30;
然后結果是;
[tbl_2]
===================
id | name | score
===================
3 | harry | 4
1 | peter | 2
2 | jane | 2
4 | ron | 1
然后查詢這個;
SELECT v1.id, v1.name, v1.score, COUNT( v2.score ) AS rank
FROM votes v1
JOIN votes v2 ON v1.score < v2.score
OR (
v1.score = v2.score
AND v1.id = v2.id
)
GROUP BY v1.id, v1.score
ORDER BY v1.rank ASC, v1.id ASC
LIMIT 0, 30;
然后結果是;
==========================
id | name | score | rank
==========================
3 | harry | 4 | 1
1 | peter | 2 | 2
2 | jane | 2 | 2
4 | ron | 1 | 4
是否可以很好地在一個事務(查詢)中執行此操作？
最佳答案 是的,可以在單個查詢中執行此操作.但它在MySQL中是一個完整的毛球,因為MySQL沒有簡單的ROWNUM操作,你需要一個用于排名計算.
這是您顯示排名的投票查詢. @ranka變量用于對行進行編號.
SELECT @ranka:=@ranka+1 AS rank, id, name, score
FROM
(
SELECT id,
name,
COUNT( id ) AS score
FROM tbl_1
GROUP BY id
ORDER BY score DESC, id
) votes,
(SELECT @ranka:=0) r
正如您已經發現的那樣,您需要自行加入此項以獲得正確的排名(正確處理關系).因此,如果您接受查詢并將兩個引用替換為您的投票表,每個引用都有自己的子查詢版本,那么您將獲得所需的內容.
SELECT v1.id,
v1.name,
v1.score,
COUNT( v2.score ) AS rank
FROM (
SELECT @ranka:=@ranka+1 AS rank,
id,
name,
score
FROM
(
SELECT id,
name,
COUNT( id ) AS score
FROM tbl_1
GROUP BY id
ORDER BY score DESC, name
) votes,
(SELECT @ranka:=0) r) v1
JOIN (
SELECT @rankb:=@rankb+1 AS rank,
id,
name,
score
FROM
(
SELECT id,
name,
COUNT( id ) AS score
FROM tbl_1
GROUP BY id
ORDER BY score DESC, name
) votes,
(SELECT @rankb:=0) r) v2
ON (v1.score < v2.score) OR
(v1.score = v2.score AND v1.id = v2.id)
GROUP BY v1.id, v1.score
ORDER BY v1.rank ASC, v1.id ASC
LIMIT 0, 30;
告訴你這是一個毛球.請注意,在您自行加入的子查詢的兩個版本中需要不同的@ranka和@rankb變量,以使行編號正常工作：這些變量在MySQL中具有連接范圍,而不是子查詢范圍.
編輯：使用PostgreSQL的RANK()函數更容易做到這一點.
SELECT name, votes, rank() over (ORDER BY votes)
FROM (
SELECT name, count(id) votes
FROM tab
GROUP BY name
)x
)
)
