下面两张表,#TMP01,#TMP02根据ID 来关联,
比如:根据每一天,这里以(2010-10-04)这一天为例 TMP01与TMP02 根据ID关联 COLUMN1相同的数据只显示一条
ID COLUMN1 COLUMN2 DATE_TIME
1 AAAA 00001 2010-10-04
1 00002 2010-10-04最终显示效果,如下:
ID COLUMN1 COLUMN2 DATE_TIME
1 AAAA 00001 2010-10-04
1 00002 2010-10-04
1 AAAA 00003 2010-10-05
2 BBBB 2010-10-05
3 CCCC 2010-10-05
4 99999 2010-10-04CREATE TABLE #TMP01
(
ID INT,
COLUMN1 VARCHAR(10),
DATE_TIME VARCHAR(10)
)INSERT INTO #TMP01
SELECT 1,'AAAA','2010-10-04'
UNION
SELECT 2,'BBBB','2010-10-05'
UNION
SELECT 3,'CCCC','2010-10-05'CREATE TABLE #TMP02
(
ID INT,
COLUMN2 VARCHAR(10),
DATE_TIME VARCHAR(10)
)INSERT INTO #TMP02
SELECT 1,'00001','2010-10-04'
UNION
SELECT 1,'00002','2010-10-04'
UNION
SELECT 1,'00003','2010-10-05'
UNION
SELECT 4,'99999','2010-10-04'
比如:根据每一天,这里以(2010-10-04)这一天为例 TMP01与TMP02 根据ID关联 COLUMN1相同的数据只显示一条
ID COLUMN1 COLUMN2 DATE_TIME
1 AAAA 00001 2010-10-04
1 00002 2010-10-04最终显示效果,如下:
ID COLUMN1 COLUMN2 DATE_TIME
1 AAAA 00001 2010-10-04
1 00002 2010-10-04
1 AAAA 00003 2010-10-05
2 BBBB 2010-10-05
3 CCCC 2010-10-05
4 99999 2010-10-04CREATE TABLE #TMP01
(
ID INT,
COLUMN1 VARCHAR(10),
DATE_TIME VARCHAR(10)
)INSERT INTO #TMP01
SELECT 1,'AAAA','2010-10-04'
UNION
SELECT 2,'BBBB','2010-10-05'
UNION
SELECT 3,'CCCC','2010-10-05'CREATE TABLE #TMP02
(
ID INT,
COLUMN2 VARCHAR(10),
DATE_TIME VARCHAR(10)
)INSERT INTO #TMP02
SELECT 1,'00001','2010-10-04'
UNION
SELECT 1,'00002','2010-10-04'
UNION
SELECT 1,'00003','2010-10-05'
UNION
SELECT 4,'99999','2010-10-04'
select Max(#TMP01.ID) as ID, #TMP01.COLUMN1, Max(#TMP02.COLUMN2) as COLUMN2, Max(#TMP01.DATE_TIME) as DATE_TIME
from #TMP01 inner join #TMP02 on #TMP02.ID = #TMP01.ID group by #TMP01.COLUMN1
当在表2中出现同一天同ID的数据时,处理方式是:得到COLUMN2最小的一天数据
同时我这个是已表2为主,我觉得你表1主要是存放表2对应ID所在的名称的
测试如下:
CREATE TABLE #TMP01
(
ID INT,
COLUMN1 VARCHAR(10),
DATE_TIME VARCHAR(10)
)
INSERT INTO #TMP01
SELECT 1,'AAAA','2010-10-04'
UNION
SELECT 2,'BBBB','2010-10-05'
UNION
SELECT 3,'CCCC','2010-10-05'CREATE TABLE #TMP02
(
ID INT,
COLUMN2 VARCHAR(10),
DATE_TIME VARCHAR(10)
)
INSERT INTO #TMP02
SELECT 1,'00001','2010-10-04'
UNION
SELECT 1,'00002','2010-10-04'
UNION
SELECT 1,'00003','2010-10-05'
UNION
SELECT 2,'00001','2010-10-04'
UNION
SELECT 2,'00003','2010-10-04'
UNION
SELECT 2,'00008','2010-10-04'
UNION
SELECT 2,'00007','2010-10-05'
UNION
SELECT 2,'00003','2010-10-06'
UNION
SELECT 3,'00003','2010-10-05'select b.id,c.COLUMN1,b.COLUMN2,b.DATE_TIME from #TMP01 c right join
(select * from #TMP02 a where not exists
(select 1 from #TMP02 where #TMP02.COLUMN2<a.COLUMN2 and #TMP02.id=a.id and #TMP02.DATE_TIME=a.DATE_TIME))b
on c.id=b.id/*
id COLUMN1 COLUMN2 DATE_TIME
----------- ---------- ---------- ----------
1 AAAA 00001 2010-10-04
1 AAAA 00003 2010-10-05
2 BBBB 00001 2010-10-04
2 BBBB 00003 2010-10-06
2 BBBB 00007 2010-10-05
3 CCCC 00003 2010-10-05
*/
--Create table
CREATE TABLE t1
(
ID INT,
COLUMN1 VARCHAR(10),
DATE_TIME VARCHAR(10)
)
CREATE TABLE t2
(
ID INT,
COLUMN2 VARCHAR(10),
DATE_TIME VARCHAR(10)
)--Insert test data
INSERT INTO t1
SELECT 1,'AAAA','2010-10-04'
UNION
SELECT 2,'BBBB','2010-10-05'
UNION
SELECT 3,'CCCC','2010-10-05'INSERT INTO t2
SELECT 1,'00001','2010-10-04'
UNION
SELECT 1,'00002','2010-10-04'
UNION
SELECT 1,'00003','2010-10-05'
UNION
SELECT 4,'99999','2010-10-04'--Exec sql
select isnull(t1.id,t2.id) as id
,isnull((case when t3.column3 is null then t1.column1 else null end),'') as COLUMN1
,isnull(t2.COLUMN2,'')as COLUMN2
,isnull(t2.DATE_TIME,t1.DATE_TIME) as DATE_TIME
from t1 full join t2 on t1.id=t2.id
left join (
select id,max(column2) as column3,DATE_TIME from t2 group by id,DATE_TIME having count(1)>1
) t3 on t1.id=t3.id and t1.date_time=t3.date_time and t2.column2=t3.column3--Results
/*
id COLUMN1 COLUMN2 DATE_TIME
1 AAAA 00001 2010-10-04
1 00002 2010-10-04
1 AAAA 00003 2010-10-05
2 BBBB 2010-10-05
3 CCCC 2010-10-05
4 99999 2010-10-04
*/