希望有人能帮忙？我有一个第三方软件，我可以做自定义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这给了我下面的结果。我已经删除了在最后一个select中的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我想要的结果如下。查询的最后一部分如下所示。我只能在第三方软件中自定义查询。
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我希望我已经格式化了这个权利，让人们理解。谢谢你的帮助。
1条答案
按热度按时间tp5buhyn1#
似乎您只需在现有cte中添加两个组和，然后将它们添加到最终选择中：
顺便说一句
ORDER BY tblTicketsRow.fldTicketID在您的行上，行号不是基于唯一的列，因此不能保证行的顺序。你可能想要order by tblTicketsRow.fldStartTime相反。