来自同一个表但条件不同的多个相似查询的连接结果

Question

我对SQL完全陌生。

我需要获得这个查询的结果：

Select
Date,
SUM(SumPrice) as Price1,
SUM(SumAmount) as Amount1,
SUM(SumPrice)/SUM(SumAmount) as AVGprice1

FROM [BiSandbox].[dbo].[database]
where Condition = '1'  
group by Date

要与此查询的结果对齐，请执行以下操作：

Select
Date,
SUM(SumPrice) as Price2,
SUM(SumAmount) as Amount2,
SUM(SumPrice)/SUM(SumAmount) as AVGprice2

FROM [BiSandbox].[dbo].[database]
where Condition = '2'  
group by Date

它们应该排成一列，因为它们是按公共日期列分组的。我无法通过google/搜索找到解决方案，因为这是一个非常狭义的问题。连接这两个结果表的正确语法是什么？我能加入三人组吗？

Answer 1

如果需要，您可以将表连接到自身，如下所示：

SELET
d1.Date,
SUM(d1.SumPrice) as Price1,
SUM(d1.SumAmount) as Amount1,
SUM(d1.SumPrice)/SUM(d1.SumAmount) as AVGprice1
SUM(d2.SumPrice) as Price2,
SUM(d2.SumAmount) as Amount2,
SUM(d2.SumPrice)/SUM(d2.SumAmount) as AVGprice2
FROM [BiSandbox].[dbo].[database] d1
    -- or the primary key column you have instead of id
    INNER JOIN [BiSandbox].[dbo].[database] d2 on d1.id = d2.id
WHERE d1.Condition = '1'
AND d2.Condition = '2' -- this can be an OR if needed
GROUP BY d1.Date

如果此答案不令人满意，请提供更多详细信息。