如何将3个独立的SQL语句组合成1组结果

Question

我有一个SQL查询，我正在运行3次(3只改变日期范围)，并希望将结果合并到一个表中，而不是运行3个不同的查询，并试图加入SQL之外。我试图找出每天/每月/每年发生的次数。我在SQL Server中运行这个。

我有两个表;一个有交易的日期，另一个有我需要的信息(InventoryNumber表的前3个字符)，所以我必须连接这些表。然后，我要按库存编号的前3个字符分组，并在列中添加计数。

最终目标是得到如下所示的内容:

InvNum | DayCount | MonthCount | YearCount
abc    | 2        | 10         | 40
def    | 0        | 2          | 6
xyz    | 0        | 0          | 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;

我看过一些其他类似的问题，但他们的查询更简单，我无法复制同样的事情与我的查询。

任何帮助都是感激的。

Answer 1

在没有足够了解的情况下，您应该考虑使用条件聚合

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;

Answer 2

看起来您需要三个不同开始日期的条件聚合。

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));

Answer 3

这可能会为你做的工作:

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

Answer 4

您需要根据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列

上连接这三个表