之前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