PLSQL：在一个查询中获取一周中每一天的总和和一周的总和

比方说,我有一个表ClientClde,就像这样：

ClientName,TradeDate,Quantity

我想在Oracle PLSQL中创建一个查询,它应该返回如下结果：
(天数来自TradeDate列,Mon = Sum(数量)为Mon,Tue = sum(Quantity)为Tue ……等)

ClientName  Mon Tue Wed Thu Fri Sat Sun TotalForWeek
ABC         10  15  5   2   4   0   0   34
XYZ         1   1   2   1   2   0   0   7

假设此报告始终具有使其运行一周的条件,是否可以在单个查询中创建此报告？

解决方法

SELECT ClientName,SUM(CASE WHEN to_char(TradeDate,'DY')='MON' THEN Quantity ELSE NULL END) AS Mon,'DY')='TUE' THEN Quantity ELSE NULL END) AS Tue,'DY')='WED' THEN Quantity ELSE NULL END) AS Wed,'DY')='THU' THEN Quantity ELSE NULL END) AS Thu,'DY')='FRI' THEN Quantity ELSE NULL END) AS Fri,'DY')='SAT' THEN Quantity ELSE NULL END) AS Sat,'DY')='SUN' THEN Quantity ELSE NULL END) AS Sun,SUM(Quantity) AS TotalForWeek
FROM  ClientTrade
GROUP BY ClientName

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!