PLSQL:在一个查询中获取一周中每一天的总和和一周的总和
发布时间:2021-04-02 04:41:18 所属栏目:站长百科 来源:网络整理
导读:比方说,我有一个表ClientClde,就像这样: ClientName,TradeDate,Quantity 我想在Oracle PLSQL中创建一个查询,它应该返回如下结果: (天数来自TradeDate列,Mon = Sum(数量)为Mon,Tue = sum(Quantity)为Tue ……等) ClientName Mon Tue Wed Thu Fri Sat Sun To
比方说,我有一个表ClientClde,就像这样: ClientName,TradeDate,Quantity 我想在Oracle PLSQL中创建一个查询,它应该返回如下结果: 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 (编辑:好传媒网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |