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

表：

$list = self::field('pid,count(distinct price) as price')->group('pid')->having('price > 1')->select()->toArray();

打印：

这样就筛选出不同价格的产品了

我本来想做个功能，产品有多个规格，查规格表按产品id分组后想知道同个产品不同规格价格不一样的产品有哪些。参考了下面的资料

参考下面的

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;