SELECT 日期,SUM([1001]) AS [1001],
SUM([1002]) AS [1002],SUM([1003]) AS [1003]
FROM (
SELECT 日期,数量 AS [1001],0 AS [1002],0 AS [1003] FROM tb1
UNION ALL
SELECT 日期,0 AS [1001],数量 AS [1002],0 AS [1003] FROM tb2
UNION ALL
SELECT 日期,0 AS [1001],0 AS [1002],数量 AS [1003] FROM tb3
) AS T
GROUP BY 日期;
SUM([1002]) AS [1002],SUM([1003]) AS [1003]
FROM (
SELECT 日期,数量 AS [1001],0 AS [1002],0 AS [1003] FROM tb1
UNION ALL
SELECT 日期,0 AS [1001],数量 AS [1002],0 AS [1003] FROM tb2
UNION ALL
SELECT 日期,0 AS [1001],0 AS [1002],数量 AS [1003] FROM tb3
) AS T
GROUP BY 日期;
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-06-24 21:06:08
-------------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (日期 DATETIME,数量 INT)
INSERT INTO @tb1
SELECT '20090601',30 UNION ALL
SELECT '20090602',30 UNION ALL
SELECT '20090605',30 UNION ALL
SELECT '20090606',30 UNION ALL
SELECT '20090610',30
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (日期 DATETIME,数量 INT)
INSERT INTO @tb2
SELECT '20090601',1 UNION ALL
SELECT '20090603',30 UNION ALL
SELECT '20090604',22 UNION ALL
SELECT '20090606',30 UNION ALL
SELECT '20090608',30
--> 生成测试数据: @tb3
DECLARE @tb3 TABLE (日期 DATETIME,数量 INT)
INSERT INTO @tb3
SELECT '20090602',1 UNION ALL
SELECT '20090603',20 UNION ALL
SELECT '20090604',22 UNION ALL
SELECT '20090607',30 UNION ALL
SELECT '20090609',30--SQL查询如下:SELECT 日期,SUM([1001]) AS [1001],
SUM([1002]) AS [1002],SUM([1003]) AS [1003]
FROM (
SELECT 日期,数量 AS [1001],0 AS [1002],0 AS [1003] FROM @tb1
UNION ALL
SELECT 日期,0 AS [1001],数量 AS [1002],0 AS [1003] FROM @tb2
UNION ALL
SELECT 日期,0 AS [1001],0 AS [1002],数量 AS [1003] FROM @tb3
) AS T
GROUP BY 日期;/*
日期 1001 1002 1003
----------------------- ----------- ----------- -----------
2009-06-01 00:00:00.000 30 1 0
2009-06-02 00:00:00.000 30 0 1
2009-06-03 00:00:00.000 0 30 20
2009-06-04 00:00:00.000 0 22 22
2009-06-05 00:00:00.000 30 0 0
2009-06-06 00:00:00.000 30 30 0
2009-06-07 00:00:00.000 0 0 30
2009-06-08 00:00:00.000 0 30 0
2009-06-09 00:00:00.000 0 0 30
2009-06-10 00:00:00.000 30 0 0(10 row(s) affected)
*/