多表联合查询

[snlixing]

A表：

日期 产量 工序
2018-9-1 50 A
2018-9-2 100 A
2018-9-4 100 A


B表：

日期 产量 工序
2018-9-1 80 B
2018-9-3 100 B
2018-9-4 50 B

C表：

日期 产量 工序
2018-9-1 80 C
2018-9-2 100 C
2018-9-3 50 C
2018-9-4 100 C

D表：

日期 产量 工序
2018-9-1 80 D
2018-9-2 100 D
2018-9-3 50 D


按日期得到如下表：

日期 A B C D
2018-9-1 50 80 80 80
2018-9-2 100 100
2018-9-3 50 100 50 50
2018-9-4 100 50 100

[mingqing6364]

WITH T AS
(
	SELECT * FROM A UNION ALL
	SELECT * FROM B UNION ALL
	SELECT * FROM C UNION ALL
	SELECT * FROM D
)
SELECT
	日期,
	SUM(CASE WHEN 工序 = 'A' THEN 产量 END) A,
	SUM(CASE WHEN 工序 = 'B' THEN 产量 END) B,
	SUM(CASE WHEN 工序 = 'C' THEN 产量 END) C,
	SUM(CASE WHEN 工序 = 'D' THEN 产量 END) D
FROM
	T
GROUP BY
	日期
/*
 日期         A      B      C     D
------------ ------ ------ ----- ------
 2018-09-01     50     80    80     80
 2018-09-02    100   NULL   100    100
 2018-09-03   NULL    100    50     50
 2018-09-04    100     50   100   NULL
(4 rows affected)
*/

[mingqing6364]

WITH T AS
(
	SELECT 日期 FROM A UNION
	SELECT 日期 FROM B UNION
	SELECT 日期 FROM C UNION
	SELECT 日期 FROM D
)
SELECT
	T.日期,
	A.产量 A,
	B.产量 B,
	C.产量 C,
	D.产量 D
FROM
	T
	LEFT JOIN A ON T.日期 = A.日期
	LEFT JOIN B ON T.日期 = B.日期
	LEFT JOIN C ON T.日期 = C.日期
	LEFT JOIN D ON T.日期 = D.日期
ORDER BY
	T.日期
/*
 日期         A      B      C     D
------------ ------ ------ ----- ------
 2018-09-01     50     80    80     80
 2018-09-02    100   NULL   100    100
 2018-09-03   NULL    100    50     50
 2018-09-04    100     50   100   NULL
(4 rows affected)
*/

[Lyongt]

Select 日期, SUM(A) A, SUM(B) B, SUM(C) C, SUM(D) D
From (
Select 日期, SUM(产量) A, 0 B, 0 C, 0 D From A Group By 日期, 工序
Union
Select 日期, 0 A, SUM(产量) B, 0 C, 0 D From B Group By 日期, 工序
Union
Select 日期, 0 A, 0 B, SUM(产量) C, 0 D From C Group By 日期, 工序
Union
Select 日期, 0 A, 0 B, 0 C, SUM(产量) D From D Group By 日期, 工序
) T
Group By T.日期

[二月十六]

日期 A B C D
2018-9-1 50 80 80 80
2018-9-2 100 100
2018-9-3 50 100 50 50
2018-9-4 100 50 100

A这列数据怎么来的？

[二月十六]

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([日期] Date,[产量] int,[工序] nvarchar(21))
Insert #A
select '2018-9-1',50,N'A' union all
select '2018-9-2',100,N'A' union all
select '2018-9-4',100,N'A'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([日期] Date,[产量] int,[工序] nvarchar(21))
Insert #B
select '2018-9-1',80,N'B' union all
select '2018-9-3',100,N'B' union all
select '2018-9-4',50,N'B'
GO
if not object_id(N'Tempdb..#C') is null
	drop table #C
Go
Create table #C([日期] Date,[产量] int,[工序] nvarchar(21))
Insert #C
select '2018-9-1',80,N'C' union all
select '2018-9-2',100,N'C' union all
select '2018-9-3',50,N'C' union all
select '2018-9-4',100,N'C'
GO
if not object_id(N'Tempdb..#D') is null
	drop table #D
Go
Create table #D([日期] Date,[产量] int,[工序] nvarchar(21))
Insert #D
select '2018-9-1',80,N'D' union all
select '2018-9-2',100,N'D' union all
select '2018-9-3',50,N'D'
Go
--测试数据结束
SELECT COALESCE(a1.日期,b1.日期,c1.日期,d1.日期) 日期,a1.产量 A,b1.产量 B,c1.产量 C,d1.产量 D FROM 
(
Select *,ROW_NUMBER()OVER(ORDER BY 日期) rn from #A)a1
FULL JOIN(
Select *,ROW_NUMBER()OVER(ORDER BY 日期) rn from #B)b1 ON b1.rn = a1.rn 
FULL JOIN(
Select *,ROW_NUMBER()OVER(ORDER BY 日期) rn from #C)c1 ON c1.rn = a1.rn 
FULL JOIN(
Select *,ROW_NUMBER()OVER(ORDER BY 日期) rn from #D)d1 ON d1.rn = a1.rn 

[123321...]

用case when then 可以做到

[anjie_jie]

学习了，都是大佬

[美丽的小妮子]

如图所示：



代码如下：
with C as
(
select Data, ChanLiang, GongXu from ChanLiangA
union all
select Data, ChanLiang, GongXu from ChanLiangB
union all
select Data, ChanLiang, GongXu from ChanLiangC
union all
select Data, ChanLiang, GongXu from ChanLiangD
)
select Data 日期,ISNULL(A,0) A,ISNULL(B,0) B,ISNULL(C,0) C,ISNULL(D,0) D from C PIVOT( sum(ChanLiang) for GongXu in(A,B,C,D)) as p

主要使用了窗口函数：数据透视
透视是一种通过聚合和旋转把数据行转换成数据列的技术

[jimson0904]

看了评论，列名都是固定的，我来个动态的

Create table #A([日期] Date,[产量] int,[工序] nvarchar(21))
Insert #A
select '2018-9-1',50,N'A' union all
select '2018-9-2',100,N'A' union all
select '2018-9-4',100,N'A'
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([日期] Date,[产量] int,[工序] nvarchar(21))
Insert #B
select '2018-9-1',80,N'B' union all
select '2018-9-3',100,N'B' union all
select '2018-9-4',50,N'B'
GO
if not object_id(N'Tempdb..#C') is null
drop table #C
Go
Create table #C([日期] Date,[产量] int,[工序] nvarchar(21))
Insert #C
select '2018-9-1',80,N'C' union all
select '2018-9-2',100,N'C' union all
select '2018-9-3',50,N'C' union all
select '2018-9-4',100,N'C'
GO
if not object_id(N'Tempdb..#D') is null
drop table #D
Go
Create table #D([日期] Date,[产量] int,[工序] nvarchar(21))
Insert #D
select '2018-9-1',80,N'D' union all
select '2018-9-2',100,N'D' union all
select '2018-9-3',50,N'D'
GO

DECLARE @str VARCHAR (8000)
SET @str ='SELECT 日期 '
SELECT @str =@str + ', sum ( CASE WHEN 工序='''+ 工序 +''' THEN 产量 ELSE 0 END ) AS ' + 工序 + ' ' FROM (
SELECT DISTINCT a.工序 FROM (
SELECT * FROM #a UNION all
SELECT * FROM #b UNION all
SELECT * FROM #c UNION all
SELECT * FROM #D
) a ) a
SET @str =@str + ' FROM (
SELECT * FROM #a UNION all
SELECT * FROM #b UNION all
SELECT * FROM #c UNION all
SELECT * FROM #D) a GROUP BY 日期'
EXEC ( @str )

[grace8]

查一下 sql 行列转置 。一般用 with cte 表达式或者 case when。
另分享个资料供参考：SQL Server 2012编程入门经典(第4版)

[tommyleeyoyo]

用LEFT JOIN 比较方便快捷，，，，，，，，，

[ken2002]

select 日期, sum(case when 工序 = 'A' then 产量 end) A, sum(case when 工序 = 'B' then 产量 end) B, sum(case when 工序 = 'C' then 产量 end) C, sum(case when 工序 = 'D' then 产量 end) D from ( select * from TA union all select * from TB union all select * from TC union all select * from TD ) A group by 日期 日期 A B C D -------------------------------------------------- ----------- ----------- ----------- ----------- 2018-9-1 50 80 80 80 2018-9-2 100 NULL 100 100 2018-9-3 NULL 100 50 50 2018-9-4 100 50 100 NULL 警告: 聚合或其他 SET 操作消除了 Null 值。 (4 行受影响)

[又又0916]

SELECT p.日期 ,[A] AS A ,[B] AS B ,[C] AS C ,[D] AS D FROM( SELECT * FROM #A ----#A为A表，#B为B表，以此类推 UNION ALL SELECT * FROM #B UNION ALL SELECT * FROM #C UNION ALL SELECT * FROM #D ) a PIVOT (SUM(产量) FOR a.工序 IN ([A],[B],[C],[D])) AS P