怎样group by一列 select多列

之前sql用的少 竟然不知道这个小技巧

1 将要查询的列 添加到group by后面(会影响查询结果)

 

2 使用聚合函数如 max

select a.accounttitlecode, max(b.currency),max(a.isqty)
  from bfacctitleaccrlat2019 a,
       (select case
                 when T.IsCrcy = 1 then
                  T.crcy
                 else
                  'maincurrency'
               end currency,
               T.Accounttitlecode,
               T.Ledger
          from bfacctitleaccrlat2019 T
         where T.Chartofaccount = 'f80ff706-4f66-45ba-bb0f-639f46712316'
           and T.Ledger = '4272a6b9-451a-44b0-954f-48944c31baca'
           and T.Accounttitlecode like '1111%'
           and not exists
         (select 1
                  from bfacctitleaccrlat2019 b
                 where b.ledger = T.ledger
                   and b.accounttitlecode like T.accounttitlecode || '_%')) b
 where b.accounttitlecode like a.accounttitlecode || '%'
 and a.ledger=b.ledger group by a.accounttitlecode,b.currency

 

转载于:https://www.cnblogs.com/wolbo/p/11285072.html