sqlserver 汇总行转列

最新推荐文章于 2023-08-08 13:18:50 发布

原创最新推荐文章于 2023-08-08 13:18:50 发布 · 268 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

本文介绍了一种通过SQL查询来获取不同币种下各账户区域的总余额的方法。使用了左连接和子查询的方式从数据库中提取MOP（澳门元）、HKD（港元）和其他货币的数据，并对每个账户区域的余额进行了汇总。

select t.AccArea , (select accbal from v1 where accccy='MOP' and v1.accarea =t.accarea) as MOP ,
(select accbal from v1 where accccy='HKD' and v1.accarea =t.accarea) as HKD ,
(select accbal from v1 where accccy='OTHER' and v1.accarea =t.accarea) as OTHER
FROM [GBD_DW].[dbo].[F_ACCBAL] t
group by t.AccArea

select t.AccArea , max(t1.accbal) as MOP , max(t2.accbal) as HKD ,max(t3.accbal) as OTHER
   FROM [GBD_DW].[dbo].[F_ACCBAL] t
   left join (select * from v1 where accccy='MOP') t1 on t1.accarea = t.AccArea
   left join (select * from v1 where accccy='HKD') t2 on t2.accarea = t.AccArea
   left join (select * from v1 where accccy='OTHER') t3 on t3.accarea= t.AccArea
   group by t.AccArea