查询一个表的数据 根据表中数据重复出现的次数来排序 并且distinct掉重复的数据
例如表table 有三列 L1 L2 L3 L1为主键
表中数据如下: 1 0001 张
2 0002 王
3 0001 张
4 0001 张
5 0002 王
6 0003 朱
查询后的结果为:
0001 张
0002 王
0003 朱
例如表table 有三列 L1 L2 L3 L1为主键
表中数据如下: 1 0001 张
2 0002 王
3 0001 张
4 0001 张
5 0002 王
6 0003 朱
查询后的结果为:
0001 张
0002 王
0003 朱
select l2,l3,count(1) as cnt from table
group by l2,l3
)
order by cnt,l2
这样返回的结果是L3在前面的,要是想返回你那样的,就用
select distinct L3 from Table
然后再跟原表并一下,返回L2.
WITH TEST AS (
SELECT '1' AS L1,'0001' AS L2,'張' AS L3 FROM DUAL
UNION ALL
SELECT '2' AS L1,'0002' AS L2,'王' AS L3 FROM DUAL
UNION ALL
SELECT '3' AS L1,'0003' AS L2,'朱' AS L3 FROM DUAL
UNION ALL
SELECT '4' AS L1,'0001' AS L2,'張' AS L3 FROM DUAL
UNION ALL
SELECT '5' AS L1,'0002' AS L2,'王' AS L3 FROM DUAL
UNION ALL
SELECT '6' AS L1,'0003' AS L2,'朱' AS L3 FROM DUAL
)
SELECT L1,L2,L3 FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY L3 ORDER BY L1) AS RN ,T.* FROM TEST T)
WHERE RN = 1 ORDER BY L1