将查询合并为一个查询

Question

我有以下查询：

查询1

SELECT so.ClientID, 'All Channels' as CustomerGroup, so.StatementID, so.Brand, so.Product,
Sum(so.Amount) Amount, Sum(so.Value_CP) Value_CP
into #t1
FROM RG_SalesOut_Report so
WHERE so.Block=0 AND so.[All Sources]='SalesOUT'AND so.Value_CP>0 AND so.Amount>0 AND
so.Brand in('Brand 1', 'Brand 2')
GROUP BY so.ClientID, so.CustomerGroup, so.StatementID, so.Brand, so.Product 

查询2

select t1.ClientID, t1.CustomerGroup, t1.StatementID, t1.Brand, t1.Product,
Sum(t1.Amount) AS Amount, Sum(t1.Value_CP) AS Value_CP
into #t2
from #t1 t1
group by t1.ClientID, t1.CustomerGroup, t1.StatementID, t1.Brand, t1.Product

查询 3

select ROW_NUMBER() over(order by t2.ClientID desc) as ID, *, CONCAT(t2.ClientID, t2.Product) AS Code
into #t3
from #t2 t2
group by t2.ClientID, t2.CustomerGroup, t2.StatementID, t2.Brand, t2.Product, t2.Amount, t2.Value_CP, CONCAT(t2.ClientID, t2.Product)
ORDER BY t2.ClientID DESC, t2.Product, t2.StatementID desc

查询 4

select tab1.ClientID, tab1.CustomerGroup, convert(varchar,(CONVERT(date,tab1.StatementID,104)),104) AS StatementID, tab1.Brand,
tab1.Product, tab1.Amount, tab1.Value_CP, IIF(tab1.code=tab2.code, DATEDIFF(MONTH,tab2.StatementID, tab1.StatementID), 0) AS M_SALES
FROM #t3 tab1
RIGHT JOIN #t3 tab2
ON tab1.ID=tab2.ID-1
where tab1.StatementID >= '01.01.2013'
order by tab1.ID asc

如何将它们合并为一个查询？ 我需要第四个查询的结果。

- Habib Karimov

---

如果您想保留重复项（如果有），请在查询之间使用UNION或UNION ALL。 - Milen

联合？只要所有查询返回相同的列，就在查询之间使用 UNION 语句，使它们返回一个包含所有数据的单个结果集。 - HaukurHaf

大家能否停止编辑破坏这篇文章？我现在会修复它，然后请停止混乱。 - Lundin

Answer 1

如果您使用SQLServer，可以使用公共表达式而无需插入临时表。此外，在#T3查询中不需要order by类：

WITH T1 AS 
(
SELECT so.ClientID, 'All Channels' as CustomerGroup, so.StatementID, so.Brand, so.Product,
Sum(so.Amount) Amount, Sum(so.Value_CP) Value_CP
FROM RG_SalesOut_Report so
WHERE so.Block=0 AND so.[All Sources]='SalesOUT'AND so.Value_CP>0 AND so.Amount>0 AND
so.Brand in('Brand 1', 'Brand 2')
GROUP BY so.ClientID, so.CustomerGroup, so.StatementID, so.Brand, so.Product 
),
T2 AS
(
select t1.ClientID, t1.CustomerGroup, t1.StatementID, t1.Brand, t1.Product,
Sum(t1.Amount) AS Amount, Sum(t1.Value_CP) AS Value_CP
from T1
group by t1.ClientID, t1.CustomerGroup, t1.StatementID, t1.Brand, t1.Product
),
T3 AS
(
select ROW_NUMBER() over(order by t2.ClientID desc) as ID, *, CONCAT(t2.ClientID, t2.Product) AS Code
from t2
group by t2.ClientID, t2.CustomerGroup, t2.StatementID, t2.Brand, t2.Product, t2.Amount, t2.Value_CP, CONCAT(t2.ClientID, t2.Product)
)

select tab1.ClientID, tab1.CustomerGroup, convert(varchar,(CONVERT(date,tab1.StatementID,104)),104) AS StatementID, tab1.Brand,
tab1.Product, tab1.Amount, tab1.Value_CP, IIF(tab1.code=tab2.code, DATEDIFF(MONTH,tab2.StatementID, tab1.StatementID), 0) AS M_SALES
FROM T3 tab1
RIGHT JOIN T3 tab2
ON tab1.ID=tab2.ID-1
where tab1.StatementID >= '01.01.2013'
order by tab1.ID asc

- valex

---

谢谢您的回答，我正在使用SQL Server 2012。我必须在#T3中使用order by，因为我在计算字段（M_Sales）中得到了错误的数据。您有什么想法吗？ - Habib Karimov

#T3的顺序完全无关紧要 - 您的连接基于ROW_NUMBER() over(order by t2.ClientID desc)的结果。这就影响了您的结果 - 如果结果不符合您的期望，则需要更改此函数中的排序方式。由于我们不知道您当前的情况，为什么是错误的以及您的期望是什么，因此很难建议纠正措施。 - GarethD