查询分组后相同值和不同值统计，分组后查询前几条、某字段拼接

1、查询分组后相同值和不同值统计的个数

相同值
select cust_code, count(*) from CRC_RATE_RESULT where type = ‘1’ group by cust_code ;
不同值
select count(distinct cust_code) from CRC_RATE_RESULT where type = ‘1’;

2、查询分组后相同内容的前几条

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY 分组的字段名 order by 排序的字段名) AS rnk,cc.*
FROM 表名 cc
) t
WHERE t.rnk<3;

笨方法
–先查出来都有什么
select menu_name from (all) group by menu_name;
–把该列挨个复制到‘’内
select * from (all) ta menu_name = ‘’ and rownum < 3
union select * from (all) ta menu_name = ‘’ and rownum < 3
union select * from (all) ta menu_name = ‘’ and rownum < 3;

3、listagg() WITHIN GROUP () group by 分组后将某字段多行合并成一行

select ldname,nvl(pnametext,’–’),listagg(fname_L2,’,’)within group (order by ldname) dd from(
select A.Ldname,C.FNAME_L2,D.PNAMETEXT from tblearningdata A
left join tbLSLearningData B on A.LDID = B.LDID
left join TBLEARNINGSYSTEM C on B.LSID=C.LSID
left join VW302COURSE D on A.LDNAME = D.cname
where A.status = 0
) group by ldname,pnametext;