IF OBJECT_ID('TEMPDB.DBO.#T1') IS NOT NULL DROP TABLE #T1
GO
IF OBJECT_ID('TEMPDB.DBO.#T2') IS NOT NULL DROP TABLE #T2
GO
CREATE TABLE #T1
(
UID VARCHAR(6),
DT DATE,
ITEM01 FLOAT
)
CREATE TABLE #T2
(
UID VARCHAR(6),
DT DATE,
ITEM02 FLOAT
)
INSERT INTO #T1
VALUES
('000001','2014-05-30',1.2),
('000001','2014-05-31',10),
('000001','2014-06-01',7),
('000002','2013-01-02',2.2),
('000002','2013-01-03',0),
('000002','2013-01-04',3.5)
INSERT INTO #T2
VALUES('000001','2014-06-01',10),
('000001','2014-05-01',11),
('000002','2013-02-08',5.6)
将上面这两张表进行合并，实现结果如下所示：
UID DT ITEM01 ITEM02
000001 2014-05-01 0 11
000001 2014-05-30 1.2 0
000001 2014-05-31 10 0
000001 2014-06-01 7 10
000002 2013-01-03 2.2 0
000002 2013-01-04 3.5 0
000002 2013-02-08 0 5.6
------解决方案--------------------
IF OBJECT_ID('TEMPDB.DBO.#T1') IS NOT NULL DROP TABLE #T1
GO
IF OBJECT_ID('TEMPDB.DBO.#T2') IS NOT NULL DROP TABLE #T2
GO
CREATE TABLE #T1
(
UID VARCHAR(6),
DT DATE,
ITEM01 FLOAT
)
CREATE TABLE #T2
(
UID VARCHAR(6),
DT DATE,
ITEM02 FLOAT
)
INSERT INTO #T1
VALUES
('000001','2014-05-30',1.2),
('000001','2014-05-31',10),
('000001','2014-06-01',7),
('000002','2013-01-02',2.2),
('000002','2013-01-03',0),
('000002','2013-01-04',3.5)
INSERT INTO #T2
VALUES('000001','2014-06-01',10),
('000001','2014-05-01',11),
('000002','2013-02-08',5.6)
;with t as
(
select uid, dt, item01, 0 item02 from #T1
union all
select uid, dt, 0 item01, item02 from #T2
)
select uid, dt, max(item01) item01, max(item02) item02
from t
where item01<>0 or item02<>0
group by uid, dt
order by uid, dt
/*---------结果--------------
uid dt item01 item02
-----------------------------
000001 2014-05-01 0 11
000001 2014-05-30 1.2 0
000001 2014-05-31 10 0
000001 2014-06-01 7 10
000002 2013-01-02 2.2 0
000002 2013-01-04 3.5 0
000002 2013-02-08 0 5.6
----------------------------*/