序:前段时间用MySQL做项目,需去重并取最新的一条,这是就想到了用 row_number() over 这个函数,但是天不遂人愿,MySQL里居然没有,就上网查询了以下,发现row_number() over 这个函数只能再Oracle中使用,唉,只能再问问度娘,发现大神很多啊,确实有方法解决,这里把方法在梳理一遍,以巩固:
先亮一下代码:
SELECT result.* FROM (
SELECT a.*,@rownum:=@rownum+1 inde, IF(@pxydm=a.TYXYDM,@rank:=@rank+1,@rank:=1) AS rank, @pxydm:=a.TYXYDM
FROM (
SELECT QYMC,TYXYDM,CREAT_TIME FROM tb_cstable g ORDER BY tyxydm DESC,CREAT_TIME DESC) a,
(SELECT @rownum :=0, @pxydm := NULL,@rank:=0) b
) result
WHERE result.rank=1
- 先构造测试表和数据:
##构造新表 ##DROP TABLE tb_cstable; CREATE TABLE `tb_cstable` ( `CS_ID` VARCHAR(100) NOT NULL COMMENT '表主键', `QYMC` VARCHAR(255) NULL DEFAULT '' COMMENT '企业名称', `CREAT_TIME` VARCHAR(255) NULL DEFAULT '' COMMENT '创建时间', `TYXYDM` VARCHAR(255) NULL DEFAULT NULL COMMENT '统一社会信用代码', PRIMARY KEY (`CS_ID`), INDEX `TYXYDM` (`TYXYDM`), INDEX `QYMC` (`QYMC`) ) COMMENT='测试table' COLLATE='utf8_general_ci' ENGINE=InnoDB ; ##插入数据 INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('5abd3f7879254020aa4745387253fc12', 'zojan0011876652519', 'zojan0011876652519', '2019-03-04 16:39:40'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('8f6bfcbc085b45e6993ed761a5b19604', '邱淑贞医疗厂家', 'XXXXXXXXXXXXXXXXX1', '2019-03-25 14:38:17'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('f57061d2e6974e468eda4ab8745cc625', '山东威高集团医用高分子制品股份有限公司', '931370000726685299F', '2019-01-09 22:24:55'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('2dfb2c8e0a4245f39834bb40a3440162', '山东威高集团医用高分子制品股份有限公司', '931370000726685299F', '2019-01-08 04:02:26'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('3df2ab9deed448aa85e3683defadad04', '西安市沣东新城众宝莱医疗器械厂', '91611105MA6TW1D201', '2019-01-09 20:59:29'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('8d198435089d41bf88c09f016c0a6a95', '西安维康实业有限公司', '91610136710122950L', '2018-09-29 14:01:20'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('f40afc1f652f40129d289053ff90bfb0', '珠海和佳医疗设备股份有限公司', '914404001925952982', '2018-07-02 00:33:31'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('4f05270f68cb433dac4ca211f5a79132', '深圳安特医疗股份有限公司', '91440300758649954X', '2019-01-08 09:29:01'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('1f12f61925934f0d8694f1f4230e0c14', '深圳市中核海得威生物科技有限公司', '91440300279261266A', '2018-11-09 12:17:45'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('56927b2bbdd344eeaa0d7fa2e49fcae4', '广州耀远实业有限公司', '9144011376190408X7', '2019-01-08 13:14:19'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('9a230a07a3bd481caf267a4de40d1cc8', '广州雅夫生物科技有限公司', '91440112552379581N', '2018-07-06 11:54:25'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('404e7126c10f4febb53a269acb03e815', '广州中达福瑞医疗科技有限公司', '91440106685242589H', '2018-10-31 13:30:28'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('7bf9576ec80342daaeb74bbfeead8a26', '广州安必平医药科技股份有限公司', '914401017756768239', '2018-06-28 08:56:49'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('d0d4a1f9479e4abd929c5e0df4b6ea7d', '广州市达诚医疗科技有限公司', '91440101773342838H', '2018-07-02 19:01:11'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('0f15ea94911c46688f421d6f3919bd85', '衡阳市丽马医疗器械有限公司', '91430407785381121B', '2019-01-09 09:17:05'); INSERT INTO `tb_cstable` (`CS_ID`, `QYMC`, `TYXYDM`, `CREAT_TIME`) VALUES ('61649ff18ff8455094383bc6350ffe8c', '衡阳市丽马医疗器械有限公司', '91430407785381121B', '2019-01-08 11:59:05');
- 语句拆解,语句包含目标语句、变量定义语句、合整语句实现row_number() over
【目标语句】:排序时把需要分组的字段排在前面
【变量定义语句】:SELECT QYMC,TYXYDM,CREAT_TIME FROM tb_cstable g ORDER BY tyxydm DESC,CREAT_TIME DESC
##@rownum 用来记录查询数据排序后的行号 ##@pxydm 用来分组的字段 ##@rank 用来记录分组后,同一个组内的排序行号 SELECT @rownum :=0, @pxydm := NULL,@rank:=0
【合整语句】:
SELECT a.*,@rownum:=@rownum+1 inde, IF(@pxydm=a.TYXYDM,@rank:=@rank+1,@rank:=1) AS rank, @pxydm:=a.TYXYDM FROM (SELECT QYMC,TYXYDM,CREAT_TIME FROM tb_cstable g ORDER BY tyxydm DESC,CREAT_TIME DESC) a, (SELECT @rownum :=0, @pxydm := NULL,@rank:=0) b
把【目标语句】和【变量定义语句】作为两个子表,然后把分组字段赋值给@pxydm,整合查询时,如果目标语句中有TYXYDM等于@pxydm,然后@rank自加,只有一条就是1,多条就会自加1,给查询的所有数据自动赋值行号@rownum:=@rownum+1;
这样就实现了row_number() over 分组排序的MySQL写法 - 以上就是实现分组排序了,如果想取每组里的第一条(按照自己需求排序后的),在【合整语句】外在包一层select,条件是rank=1就可以了
SELECT result.* FROM ( SELECT a.*,@rownum:=@rownum+1 inde, IF(@pxydm=a.TYXYDM,@rank:=@rank+1,@rank:=1) AS rank, @pxydm:=a.TYXYDM FROM (SELECT QYMC,TYXYDM,CREAT_TIME FROM tb_cstable g ORDER BY tyxydm DESC,CREAT_TIME DESC) a, (SELECT @rownum :=0, @pxydm := NULL,@rank:=0) b ) result WHERE result.rank=1
【拓展oracle里row_number() over】的用法
select * from(select QYMC,TYXYDM,CREAT_TIME,row_number()over(partition by TYXYDM order by CREAT_TIME desc) rank from tb_cstable t) where rank <2
Oracle里存在各种高级函数,可以省去很多繁琐的语句,row_number()over(partition by 分组字段 order by 排序字段 desc)