sql - CASE 条件和 SUM() 公用表表达式或 Group By
问题描述
希望有人能帮忙?我有一个第 3 方软件,我可以进行自定义 sql 查询,但不能更改任何数据库。努力得到我想要的结果。
我是否使用 GROUP BY 或 CTE 来获得我需要的结果?
WITH CTE AS (
SELECT tblTicketsSummary.fldDateScheduled AS [Scheduled],
tblTicketsRow.fldStartTime AS [TIME], tblTicketsRow.fldPs AS
[s],tblTicketsRow.fldPrice AS PR,
tblTicketsSummary.fldFirstName+' '+tblTicketsSummary.fldLastName
AS [Client], ROW_NUMBER() OVER
(PARTITION BY tblTicketsSummary.fldTicketID ORDER BY
tblTicketsRow.fldTicketID) AS C, tblTicketsSummary.fldDateClosed
AS [Date Closed],
CASE WHEN tblTicketsRow.fldPs ='p' THEN tblTicketsRow.fldPrice
ELSE £0.00 END AS Product,
CASE WHEN tblTicketsRow.fldPs ='s' THEN tblTicketsRow.fldPrice
ELSE £0.00 END AS Services,
FROM tblTicketsSummary INNER JOIN
tblTicketsRow ON tblTicketsSummary.fldTicketID = tblTicketsRow.fldTicketID
WHERE (fldDateVoided IS NULL) AND (NOT(fldDateClosed IS NULL))
GetUserDate('AND','fldDateClosed','') AND tblTicketsRow.fldEmployeeName ='Tina Young'
AND tblTicketsSummary.fldTotal >1 )
SELECT * FROM CTE ORDER BY Scheduled ASC, TIME ASC
这给了我下面的结果。我在最后一次选择中删除了 Where c=1 ,我将把它放回最终查询中,这给了我第一个结果,这是我想要的。我正在努力解决的问题是。我想将所有产品的价格和所有服务的价格加在一起,得出销售的产品和服务的总和。然后将服务和产品的总和放在一起进行最终查询。
Scheduled TIME s PR Client C Date Closed Product Services Total
17/07/2020 17:00 S £10.00 Ben Preston 1 17/07/2020 £0.00 £10.00 s+p
17/07/2020 17:45 S £1.00 Ben Preston 2 17/07/2020 £0.00 £1.00 s+p
17/07/2020 P £19.00 Ben Preston 3 17/07/2020 £19.00 £0.00 s+p
17/07/2020 P £10.00 Ben Preston 4 17/07/2020 £10.00 £0.00 s+p
我想要的结果如下。查询的最后一部分如下。我只能在 3rd 方软件中自定义查询。
SELECT * FROM CTE WHERE c=1 ORDER BY Scheduled ASC, TIME ASC
Scheduled TIME Client C Date Closed Product Services Total
17/07/2020 17:00 Ben Preston 1 17/07/2020 £29.00 £11.00 £40.00
我希望我已经格式化了这个权利以供人们理解。感谢您提供任何帮助。
解决方案
似乎您只需将两个组总和添加到您现有的 CTE 中,然后将它们添加到您的最终选择中:
WITH CTE AS (
SELECT tblTicketsSummary.fldDateScheduled AS [Scheduled],
tblTicketsRow.fldStartTime AS [TIME],
tblTicketsRow.fldPs AS [s],tblTicketsRow.fldPrice AS PR,
tblTicketsSummary.fldFirstName+' '+tblTicketsSummary.fldLastName AS [Client],
ROW_NUMBER()
OVER (PARTITION BY tblTicketsSummary.fldTicketID
ORDER BY tblTicketsRow.fldStartTime) AS C,
tblTicketsSummary.fldDateClosed AS [Date Closed],
SUM(CASE WHEN tblTicketsRow.fldPs ='p' THEN tblTicketsRow.fldPrice ELSE £0.00 END)
OVER (PARTITION BY tblTicketsSummary.fldTicketID) AS Product,
SUM(CASE WHEN tblTicketsRow.fldPs ='s' THEN tblTicketsRow.fldPrice ELSE £0.00 END)
OVER (PARTITION BY tblTicketsSummary.fldTicketID) AS Services
FROM tblTicketsSummary
INNER JOIN tblTicketsRow
ON tblTicketsSummary.fldTicketID = tblTicketsRow.fldTicketID
WHERE (fldDateVoided IS NULL) AND (NOT(fldDateClosed IS NULL))
--GetUserDate('AND','fldDateClosed','') AND tblTicketsRow.fldEmployeeName ='Tina Young'
AND tblTicketsSummary.fldTotal >1 )
SELECT cte.*,
Product + Services as total
FROM CTE
WHERE C = 1
ORDER BY Scheduled ASC, TIME ASC;
顺便说一句,ORDER BY tblTicketsRow.fldTicketID
您的 ROW_NUMBER 不是基于唯一列,因此不能保证行的顺序。你可能想要order by tblTicketsRow.fldStartTime
。
推荐阅读
- javascript - 防止 401 XHR 错误出现在客户端控制台中
- asp.net - 如何在 Web 应用程序的页面上引用被覆盖的服务器控件
- json - 如何将数据从节点 js 存储到 JSON 文件?
- sql-server - 在 SQL Server 中展开表的日期
- javascript - 错误:未知提供者:AuditServiceProvider <- AuditService
- c# - C#反射,调用不同参数类型的方法
- mysql - Percona 查询指纹 - 为什么选择列中的顺序很重要?
- python - bokeh.charts 不见了——什么库可以做交互式彩色散点图?
- r - formatC() 在 R 中的 round(x) 产生后认为 0 为负数?
- java - 如果用户没有明确修改 savedInstanceState ,它会一直为 null 吗?