我有一個 SQL 查詢，我正在運行 3 次（3 次僅更改日期范圍）并希望將結果合并到一個表中，而不是運行 3 個不同的查詢并嘗試在 SQL 之外加入。我試圖找出每天/每月/每年發生的事情的次數。我在 SQL Server 中運行它。
我有 2 張桌子；一個有交易日期，另一個有我需要的資訊（InventoryNumber表格的前 3 個字符），所以我必須加入這些表格。然后我想按庫存編號的前 3 個字符分組，并在列中添加計數。
最終目標是擁有如下所示的東西：
InvNum | DayCount | MonthCount | YearCount
abc | 2 | 10 | 40
def | 0 | 2 | 6
xyz | 0 | 0 | 2
這是我對第一天的查詢。這完全像我想要的那樣作業。但是現在，我想在其中添加本月的計數，然后還要添加一年的計數。此查詢和其他 2 個查詢之間唯一會改變的是計數列名，然后是日期。：
SELECT
LEFT(LINEITEM.InventoryNumber, 3) AS InvNum,
COUNT(*) AS DailyCount
FROM
INVOICE
INNER JOIN
LINEITEM ON INVOICE.InvoiceID = LINEITEM.InvoiceID
WHERE
InventoryNumber IS NOT Null
AND InventoryNumber != 'Misc'
AND DateCreated > '5-20-2022'
GROUP BY
LEFT(LINEITEM.InventoryNumber, 3)
ORDER BY
InvNum ASC;
我已經查看了與此類似的其他一些問題，但他們的查詢要簡單得多，而且我無法用我的查詢復制相同的內容。
任何幫助表示贊賞。
uj5u.com熱心網友回復：
在這里沒有足夠的了解，您應該考慮條件聚合
Declare @D Date='2022-05-20';
SELECT InvNum = LEFT(LINEITEM.InventoryNumber, 3)
,DayCount = sum( case when DateCreated = @D then 1 else 0 end )
,MonthCount = sum( case when month(DateCreated) = month(@D) then 1 else 0 end )
,YearCount = sum( case when year(DateCreated) = year(@D) then 1 else 0 end )
FROM INVOICE
INNER JOIN LINEITEM ON INVOICE.InvoiceID = LINEITEM.InvoiceID
WHERE InventoryNumber IS NOT Null
AND InventoryNumber <> 'Misc'
AND DateCreated >= format(@D,'yyyy-01-01')
GROUP BY LEFT(LINEITEM.InventoryNumber, 3)
ORDER BY InvNum ASC;
uj5u.com熱心網友回復：
看起來您需要具有三個不同開始日期的條件聚合。
DECLARE @D date = '2022-05-20';
DECLARE @M date = '2022-04-21';
DECLARE @Y date = '2021-05-21';
SELECT InvNum = LEFT(li.InventoryNumber, 3)
,DayCount = COUNT(CASE WHEN i.DateCreated >= @D THEN 1 END)
,MonthCount = COUNT(CASE WHEN i.DateCreated >= @M THEN 1 END)
,YearCount = COUNT(*)
FROM INVOICE i
INNER JOIN LINEITEM li ON i.InvoiceID = li.InvoiceID
WHERE li.InventoryNumber <> 'Misc'
AND i.DateCreated >= @Y
GROUP BY
LEFT(li.InventoryNumber, 3)
ORDER BY
InvNum;
請注意，這
<> 'Misc'也排除了空值，這ASC是默認設定。
您還可以動態計算這些開始日期
DECLARE @D date = DATEADD(day, -1, CAST(GETDATE() AS date));
DECLARE @M date = DATEADD(month, -1, CAST(GETDATE() AS date));
DECLARE @Y date = DATEADD(year, -1, CAST(GETDATE() AS date));
uj5u.com熱心網友回復：
這可能會為您完成作業：
SELECT ISNULL(DailyQuery.InvNum, ISNULL(MonthlyQuery.InvNum, YearlyQuery.InvNum)) as InvNum,
ISNULL(DailyCount,0) as DailyCount,
ISNULL(MonthlyCount,0) as MonthlyCount,
ISNULL(YearlyCount,0) as YearlyCount
FROM
(SELECT
LEFT(LINEITEM.InventoryNumber, 3) AS InvNum,
COUNT(*) AS DailyCount
FROM
INVOICE
INNER JOIN
LINEITEM ON INVOICE.InvoiceID = LINEITEM.InvoiceID
WHERE InventoryNumber != 'Misc'
AND DateCreated > DATEADD(day, DATEDIFF(day, 0, GETDATE()-1), 0) --first hour day before
GROUP BY
LEFT(LINEITEM.InventoryNumber, 3)
) DailyQuery
FULL JOIN
(SELECT
LEFT(LINEITEM.InventoryNumber, 3) AS InvNum,
COUNT(*) AS MonthlyCount
FROM
INVOICE
INNER JOIN
LINEITEM ON INVOICE.InvoiceID = LINEITEM.InvoiceID
WHERE InventoryNumber != 'Misc'
AND DateCreated > DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) --first day of current month
GROUP BY
LEFT(LINEITEM.InventoryNumber, 3) ) MonthlyQuery ON DailyQuery.InvNum = MonthlyQuery.InvNum
FULL JOIN
(SELECT
LEFT(LINEITEM.InventoryNumber, 3) AS InvNum,
COUNT(*) AS YearlyCount
FROM
INVOICE
INNER JOIN
LINEITEM ON INVOICE.InvoiceID = LINEITEM.InvoiceID
WHERE InventoryNumber != 'Misc'
AND DateCreated > DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0) --first day of current month
GROUP BY
LEFT(LINEITEM.InventoryNumber, 3) ) YearlyQuery ON MonthlyQuery.InvNum = YearlyQuery.InvNum
uj5u.com熱心網友回復：
您需要根據 where 子句中的列和日期在這些表中撰寫三個查詢
只需更改每個表的 where 子句中的列和日期值
第一個查詢結果
InvNum | DayCount
abc | 2
def | 0
xyz | 0
第二個查詢結果：
InvNum | MonthCount
abc | 10
def | 2
xyz | 0
第三個查詢結果：
InvNum | YearCount
abc | 40
def | 6
xyz | 2
然后在 InvNum 列上加入這三個表
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/479619.html