我正在嘗試在 1 個表上運行 3 個不同的表，在該表中我回傳基于created_date 和modified_date的已創建、已完成、未決select query任務的計數，我可以根據欄位狀態在modified_date
中區分已完成和未決任務
當我單獨運行每個查詢時，它會回傳正確的答案。
但我想要的是將這 3 個查詢組合在一個表中，如下所示：
---------------------- ------------------ ---------------------
| Total_created_files | Total_done_files | Total_pending_files |
----------------------- ------------------ ---------------------
| 14 | 40 | 9 |
----------------------- ------------------ ---------------------
代碼：
select
count([create_date]) as Total_created_files
FROM [TEC_APP].[dbo].[case_to_do]
where [create_date] >='2022-05-01 00:00:00.000'
AND
CAST([create_date] AS date) <= CAST(GETDATE() AS date)
select
count([modified_date]) as Total_done_files
FROM [TEC_APP].[dbo].[case_to_do]
where [modified_date] >='2022-05-01 00:00:00.000'
AND
CAST([modified_date] AS date) <= CAST(GETDATE() AS date)
AND
status = 'DONE'
select
count([modified_date]) as Total_pending_files
FROM [TEC_APP].[dbo].[case_to_do]
where [modified_date] >='2022-05-01 00:00:00.000'
AND
CAST([modified_date] AS date) <= CAST(GETDATE() AS date)
AND
status = 'Pending'
uj5u.com熱心網友回復：
根據需要使用條件聚合COUNT。我在這里做了幾個假設，但這應該是你所追求的：
USE TEC_APP;
GO
SELECT COUNT(CASE WHEN [create_date] < DATEADD(DAY,1,CONVERT(date,GETDATE())) THEN 1 END) AS Total_created_files,
COUNT(CASE WHEN [modified_date] >= '20220501' AND [modified_date] < DATEADD(DAY,1,CONVERT(date,GETDATE())) AND Status = 'DONE' THEN 1 END) AS Total_done_files,
COUNT(CASE WHEN [modified_date] >= '20220501' AND [modified_date] < DATEADD(DAY,1,CONVERT(date,GETDATE())) AND Status = 'Pending' THEN 1 END) AS Total_pending_files
FROM [dbo].[case_to_do]
WHERE create_date >= '20220501'; --Presumably something can't be modified before it's created.
uj5u.com熱心網友回復：
使用條件聚合case expression：
SELECT SUM(case when <First Condition here> then 1 else 0 end) as Total_created_files,
SUM(case when <Second Condition here> then 1 else 0 end) as Total_done_files,
SUM(case when <Third Condition here> then 1 else 0 end) as Total_pending_files
FROM [TEC_APP].[dbo].[case_to_do]
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/484681.html