并列排序大全
學(xué)習(xí)過(guò)mysql的同學(xué)，大都體會(huì)過(guò)mysql的一個(gè)的痛點(diǎn)，那就是mysql沒(méi)有支持分組排序的開(kāi)窗函數(shù)。但是我們能夠通過(guò)使用變量來(lái)達(dá)到這個(gè)目的。
1 不分組的情況
CREATE TABLE sql_rank (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
user_id int(11) unsigned NOT NULL,
score tinyint(3) unsigned NOT NULL,
add_time date NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
插入執(zhí)行語(yǔ)句
INSERT INTO talents.sql_rank (id, user_id, score, add_time) VALUES ('1', '100', '50', '2016-05-01');
INSERT INTO talents.sql_rank (id, user_id, score, add_time) VALUES ('2', '101', '30', '2016-05-01');
INSERT INTO talents.sql_rank (id, user_id, score, add_time) VALUES ('3', '102', '20', '2016-05-01');
INSERT INTO talents.sql_rank (id, user_id, score, add_time) VALUES ('4', '103', '60', '2016-05-01');
INSERT INTO talents.sql_rank (id, user_id, score, add_time) VALUES ('5', '104', '80', '2016-05-01');
INSERT INTO talents.sql_rank (id, user_id, score, add_time) VALUES ('6', '105', '50', '2016-05-01');
INSERT INTO talents.sql_rank (id, user_id, score, add_time) VALUES ('7', '106', '70', '2016-05-01');
INSERT INTO talents.sql_rank (id, user_id, score, add_time) VALUES ('8', '107', '85', '2016-05-01');
INSERT INTO talents.sql_rank (id, user_id, score, add_time) VALUES ('9', '108', '60', '2016-05-01');
自然排名
SELECT
id, user_id, score,
@rownum:=@rownum +1 as rownum
FROM
(SELECT @rownum:=0) r ,
sql_rank
ORDER BY score DESC;
并列非連續(xù)排名
SELECT id, user_id, score,
@rownum := @rownum + 1 AS tmp,
@incrnum := CASE
WHEN @rowtotal = score THEN @incrnum
WHEN @rowtotal := score THEN @rownum
END AS rownum
FROM
(SELECT id, user_id, score FROM sql_rank ORDER BY score DESC) AS a,
(SELECT @rownum := 0, @rowtotal := NULL, @incrnum := 0) b
并列連續(xù)排名
SELECT
obj.user_id,
obj.score,
CASE
WHEN @rowtotal = obj.score THEN
@rownum
WHEN @rowtotal := obj.score THEN
@rownum :=@rownum + 1
WHEN @rowtotal = 0 THEN
@rownum :=@rownum + 1
END AS rownum
FROM
(
SELECT
user_id,
score
FROM
`sql_rank`
ORDER BY
score DESC
) AS obj,
(SELECT @rownum := 0 ,@rowtotal := NULL) r
2 分組的情況
CREATE TABLE sc (
SId varchar(10) DEFAULT NULL,
CId varchar(10) DEFAULT NULL,
score decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
插入執(zhí)行語(yǔ)句
INSERT INTO sc (SId, CId, score) VALUES ('01', '01', '80.0');
INSERT INTO sc (SId, CId, score) VALUES ('01', '02', '90.0');
INSERT INTO sc (SId, CId, score) VALUES ('01', '03', '99.0');
INSERT INTO sc (SId, CId, score) VALUES ('02', '01', '70.0');
INSERT INTO sc (SId, CId, score) VALUES ('02', '02', '60.0');
INSERT INTO sc (SId, CId, score) VALUES ('02', '03', '80.0');
INSERT INTO sc (SId, CId, score) VALUES ('03', '01', '80.0');
INSERT INTO sc (SId, CId, score) VALUES ('03', '02', '80.0');
INSERT INTO sc (SId, CId, score) VALUES ('03', '03', '80.0');
INSERT INTO sc (SId, CId, score) VALUES ('04', '01', '50.0');
INSERT INTO sc (SId, CId, score) VALUES ('04', '02', '30.0');
INSERT INTO sc (SId, CId, score) VALUES ('04', '03', '20.0');
INSERT INTO sc (SId, CId, score) VALUES ('05', '01', '76.0');
INSERT INTO sc (SId, CId, score) VALUES ('05', '02', '87.0');
INSERT INTO sc (SId, CId, score) VALUES ('06', '01', '31.0');
INSERT INTO sc (SId, CId, score) VALUES ('06', '03', '34.0');
INSERT INTO sc (SId, CId, score) VALUES ('07', '02', '89.0');
INSERT INTO sc (SId, CId, score) VALUES ('07', '03', '98.0');
自然排名
SELECT cid,score,rank
FROM
(
SELECT sc.*,IF(@p=cid,@r:=@r+1,@r:=1) AS rank,@p:=cid
FROM sc,(SELECT @p:=NULL,@r:=0)r
ORDER BY cid,score desc
)s;
并列非連續(xù)排名
SELECT cid,score,rank
FROM(
SELECT cid,sid,@rr:=@rr+1,
CASE
WHEN (@c=cid AND @s=score) THEN @r
WHEN @c=cid THEN @r:=@rr
WHEN (@c:=cid) AND (@rr:=1) THEN @r:=1
END AS rank,
@s:=score AS score
FROM (
SELECT *
FROM sc
ORDER BY cid ,score DESC
)a JOIN (SELECT @c:=-1,@s:=-1,@r:=-1,@rr:=-1) temp
)b ;
并列連續(xù)排名
SELECT cid,score,rank
FROM
(
SELECT *,
IF(@p=cid,
CASE
WHEN @s=score THEN @r
WHEN @s:=score THEN @r:=@r+1
END,
@r:=1 ) AS rank,
@p:=cid,
@s:=score
FROM sc,(SELECT @p:=NULL,@s:=NULL,@r:=0)r
ORDER BY cid,score
)s;
答疑解惑注解
[圖片上傳失敗...(image-c212a6-1587375560746)]
[圖片上傳失敗...(image-ebc471-1587375560746)]
[圖片上傳失敗...(image-64667b-1587375560746)]
第一條數(shù)據(jù)判斷@p≠cid，輸直接出了@r=1，
第二條數(shù)據(jù)判斷@p=cid成立,然后判斷@s=score成立，輸出@r=1，
但是第三條數(shù)據(jù)，在@p=cid成后，是怎么判斷@s：=score成立的呢，，，一個(gè)賦值的語(yǔ)句怎么就可以用來(lái)做判斷呢
[圖片上傳失敗...(image-123deb-1587375560746)]
【這個(gè)是C語(yǔ)言的邏輯】
是這個(gè)邏輯嗎？先賦予變量值，如果不等于0則判斷成立
已經(jīng)驗(yàn)證了，是這個(gè)邏輯，當(dāng)改為0的時(shí)候，第一條數(shù)據(jù)輸出的是null，而改為1的時(shí)候就可以成立
[圖片上傳失敗...(image-81c308-1587375560746)]
彩蛋番外篇
/分組后取前兩個(gè)/
查詢每門(mén)功成績(jī)最好的前兩名
-- 牛逼的寫(xiě)法
SELECT * FROM sc c
WHERE (
SELECT count(*) FROM sc
WHERE cid=c.cid AND score>c.score )<2
ORDER BY cid,score DESC
