我的桌子看起来像:
cust_ref | account_num
123456 | 001132
321234 | 123213
325123 | 412312
123456 | 312321
我基本上想做的是将重复的cust_ref行排序在一起,并对其进行排序,以便所有重复项都从第1行开始按降序排列。也就是说,如果有一个cust_ref编号对应于3个account_num,则它将在cust_ref的较高行中对应于2个account_num
例如
cust_ref | account_num
123456 | 001132
123456 | 312321
321234 | 123213
325123 | 412312
我当前的查询是:
select cust_ref,
account_num
from (
select cust_ref,
account_num,
max(phone_num)
from table_name
group by cust_ref, account_num
)
如果您的RDBMS支持,window functions (analytic functions)
则可以使用以下命令:
SELECT
SUM(COUNT(*)) OVER (PARTITION BY cust_ref) AS cnt,
cust_ref, account_num,
MAX(phone_num) AS max_phone_num
FROM table_name
GROUP BY cust_ref, account_num
ORDER BY 1 DESC, 2, 3; --cnt DESC, cust_ref, account_num
如果您也想在每个组中订购cust_ref, account_num
,请使用以下命令:
SELECT
SUM(COUNT(*)) OVER (PARTITION BY cust_ref) AS cnt,
COUNT(*) AS cnt_in_group,
cust_ref, account_num,
MAX(phone_num) AS max_phone_num
FROM table_name
GROUP BY cust_ref, account_num
ORDER BY cnt DESC, cnt_in_group DESC, cust_ref, account_num;
链接右旋
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句