如何按srcaccountid分组并按total_count降序展示SQL查询结果

原SQL代码 #

select distinct 
      srcaccountid,
      srccharid,
      srccharname,
      action, 
      itemname, 
      sum(itemcount) over (partition by srccharname, itemname) 
         as total_count, 
      sum(price) over(partition by srccharname, itemname) 
         as total_price 
   from 
      itemlog
   where 
          action = 6 
      and logtime >='2023-02-13'
   order by 
      total_count desc, 
      srcaccountid

当前查询结果 #

srcaccountid  srccharid  srccharname  action  itemname  total_count  total_price
1             21         abc          6       dog       2222         231
2             22         sdd          6       cat       1234         122
1             21         abc          6       cat        324          77
1             21         abc          6       mouse      122          32
2             22         sdd          6       mouse       12           3

期望结果 #

srcaccountid  srccharid  srccharname  action  itemname  total_count  total_price
1             21         abc          6       dog       2222         231
1             21         abc          6       cat        324         77
1             21         abc          6       mouse      122         32
2             22         sdd          6       cat       1234         122
2             22         sdd          6       mouse       12         3

问题分析 #

当前SQL的排序逻辑是先按total_count降序、再按srcaccountid排序，导致同一账号的记录被其他账号的高销量记录打断。需要调整排序规则，既要让同一账号的记录归为一组不拆分，又要保证整体按账号的最高销量降序排列，组内也按销量降序展示。

解决方案 #

方案一：用窗口函数直接调整排序逻辑 #

修改order by子句，先按账号分组，再按该账号下的最高销量降序，最后组内按销量降序：

select distinct 
      srcaccountid,
      srccharid,
      srccharname,
      action, 
      itemname, 
      sum(itemcount) over (partition by srccharname, itemname) 
         as total_count, 
      sum(price) over(partition by srccharname, itemname) 
         as total_price 
   from 
      itemlog
   where 
          action = 6 
      and logtime >='2023-02-13'
   order by 
      srcaccountid,
      max(total_count) over (partition by srcaccountid) desc,
      total_count desc

方案二：用CTE预计算账号最高销量再关联排序 #

先通过CTE计算每个账号的最高销量，再关联原表进行排序，逻辑更清晰：

with account_max_sales as (
    select 
        srcaccountid,
        max(sum(itemcount) over (partition by srccharname, itemname)) as max_total_count
    from itemlog
    where action = 6 and logtime >= '2023-02-13'
    group by srcaccountid
)
select distinct 
      i.srcaccountid,
      i.srccharid,
      i.srccharname,
      i.action, 
      i.itemname, 
      sum(i.itemcount) over (partition by i.srccharname, i.itemname) as total_count, 
      sum(i.price) over(partition by i.srccharname, i.itemname) as total_price 
from itemlog i
join account_max_sales ams on i.srcaccountid = ams.srcaccountid
where 
    i.action = 6 
    and i.logtime >= '2023-02-13'
order by 
    ams.max_total_count desc,
    i.srcaccountid,
    total_count desc

说明 #

两种方案都能实现需求：同一账号的记录集中展示，整体按账号的最高销量降序排列，组内也按单条记录的销量降序展示。方案一代码更简洁，方案二逻辑更直观，适合复杂场景下的扩展。

内容的提问来源于stack exchange，提问作者Nicholas Goh