mysql根据字段分组去重数据
8.0以上版本
select a.* from(select t.*,row_number() over(partition by t.单据编号,t.驾驶员) rn
from 派车单 t) a
where a.rn = 1;
5.0以上8.0以下版本
SELECT a.* from(SELECT t.*,
@row_number :=IF(@FIRST = t.单据编号 and @SECOND =t.驾驶员, @row_number + 1, 1 ) AS rn,
@FIRST := t.单据编号 as `分组字段1`,
@SECOND :=t.驾驶员 as `分组字段2`
FROM 派车单 t,(SELECT @row_number := 0, @FIRST := NULL,@SECOND := NULL ) b) a
where a.rn <=1;
mysql行转列
select group_concat(distinct table_feild) from table_name
达梦行转列
SELECT LISTAGG(QY_ID,',') FROM ST.table_name GROUP BY NF;
oracle行转列
SELECT wm_concat(ID) FROM DG.table_name GROUP BY ID
分组次数
SELECT ID,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATETIME) rn FROM DG.table_name
mysql版本不同去重方法不同
于 2024-04-28 10:49:32 首次发布