我有一個task表，它包含一些DateTime型別的列，如已完成、待處理、例外。它與user表相連接，我想把
userName, AllTaskCount, AllPendingTaskCount, AllCompletedTaskCount, AllExceptionTaskCount。
如果Completed列有值，那么它將被視為已完成的任務，而如果Completed列為空，且exception列有值，那么它就是ExceptionTask，如果Completed列和exception都沒有值，那么它就是待定任務。
所以我想在上述條件下有條件地應用多個計數。請幫助......
uj5u.com熱心網友回復：
你可以使用CTEs來獲得結果。
WITH Result AS(
SELECT *, COUNT(1) OVER ( ORDER BY Id） AS AllTasksCount
FROM User U
JOIN TASK T ON U.Id = T.UserId
),PendingTasks AS (
SELECT [NAme], COUNT(1) AS AllPendingTaskCount FROM Result
WHERE Completed IS NULL and ExceptionIS NULL
GROUP BY [Name].
), CompletedTasks AS (
SELECT [NAme], COUNT(1) AS AllCompletedTaskCount FROM Result
WHERE Completed IS NOT NULL and Exception IS NULL
GROUP BY [名稱]
),ExceptionTasks AS (
SELECT [NAme], COUNT(1) AS AllExecptionTaskCount FROM Result
WHERE Completed IS NULL and Exception IS NOT NULL
GROUP BY [名稱]
)
SELECT DISTINCT R. [Name].
,所有任務數
,所有待處理的任務數
,所有已完成的任務數
,所有執行的任務數
FROM Result R。
JOIN PendingTasks PT
ON PT.[Name] = R.[Name] 。
JOIN CompletedTasks CT
ON CT.[Name] = R.[Name] 。
JOIN ExceptionTasks ET.
ON ET.[Name] = R.[Name] 。
添加了示例模式和資料。請根據您的要求進行更新。 SQLFiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/314824.html
標籤：