分别从表A和表B取出相关数据然后处理.
表A:
bh sph sl sj
01 20 5 2006-6-1
01 35 7 2006-6-2
01 30 25 2006-6-4
01 20 6 2006-6-5表B:
bh sph sl sj
01 21 5 2006-6-1
01 35 7 2006-6-5
01 30 25 2006-6-22
01 24 6 2006-6-5现在要合并表AB中的数据,然后从汇总的数据中按sph分组,只保留时间最大的一条数据,最后在按时间排序正确的结果是:
bh sph sl sj
01 30 25 2006-6-22
01 24 6 2006-6-5
01 20 6 2006-6-5
01 35 7 2006-6-5
01 21 5 2006-6-1要求用sql语句select出正确的结果,最好不要新建表或用临时表
因为表AB的数据量都比较大,所以效率是第一考虑的.
表A:
bh sph sl sj
01 20 5 2006-6-1
01 35 7 2006-6-2
01 30 25 2006-6-4
01 20 6 2006-6-5表B:
bh sph sl sj
01 21 5 2006-6-1
01 35 7 2006-6-5
01 30 25 2006-6-22
01 24 6 2006-6-5现在要合并表AB中的数据,然后从汇总的数据中按sph分组,只保留时间最大的一条数据,最后在按时间排序正确的结果是:
bh sph sl sj
01 30 25 2006-6-22
01 24 6 2006-6-5
01 20 6 2006-6-5
01 35 7 2006-6-5
01 21 5 2006-6-1要求用sql语句select出正确的结果,最好不要新建表或用临时表
因为表AB的数据量都比较大,所以效率是第一考虑的.
select * from (SELECT *
FROM 表A
UNION
SELECT *
FROM 表B
) as TABA
WHERE sj in (select top 1 sj from (SELECT *
FROM 表A
UNION
SELECT *
FROM 表B
) as TABB where TABB.SPH=TABA.SPH order by SJ desc )
order by SJ desc
insert into #a(bh,sph,sl,sj)
select '01',20,5,'2006-6-1'
union all select '01',35,7,'2006-6-2'
union all select '01',30,25,'2006-6-4'
union all select '01',20,6,'2006-6-5'create table #b(bh char(2),sph int,sl int,sj datetime)
insert into #b(bh,sph,sl,sj)
select '01',21,5,'2006-6-1'
union all select '01',35,7,'2006-6-5'
union all select '01',30,25,'2006-6-22'
union all select '01',24,6,'2006-6-5'select t1.* from (select bh,sph,sl,sj
from #a
union all
select bh,sph,sl,sj
from #b)t1
inner join (
select sph,max(sj) as sj from (
select bh,sph,sl,sj
from #a
union all
select bh,sph,sl,sj
from #b)t2
group by sph) t3 on t1.sph = t3.sph and t1.sj = t3.sj
order by t1.sj desc
FROM 表A
UNION
SELECT *
FROM 表B
) as TABA
Inner Join
(Select SPH,Max(SJ) As SJ From
(SELECT *
FROM 表A
UNION
SELECT *
FROM 表B
) as TABB Group By SPH) TABC
On TABA.SPH=TABC.SPH And TABA.SJ=TABC.SJ
order by TABA.SJ desc
SELECT *
FROM 表A
UNION
SELECT *
FROM 表B) Aselect TABA.* from #T as TABA
Inner Join
(Select SPH,Max(SJ) As SJ From #T as TABB Group By SPH) TABC
On TABA.SPH=TABC.SPH And TABA.SJ=TABC.SJ
order by TABA.SJ descDrop Table #T
谢谢速度非常快只要1秒,正在验证结果是否正确
(select t.sph,max(t.sj) as sj from
(select * from A
union all
select * from B)t
group by t.sph) c left join
(
select * from A
union all
select * from B
)d
on c.sph=d.sph and c.sj=d.sj order by d.sj desc
不知道要多久 :)
SELECT *
into #t
FROM 表A a
where sj=(select max(sj) from 表A where sph=a.sph)
UNION ALL
SELECT *
FROM 表B b
where sj=(select max(sj) from 表B where sph=b.sph)SELECT *
FROM #t a
where sj=(select max(sj) from #t where sph=a.sph)drop table #t
谢谢,结果正确
to paoluo(一天到晚游泳的鱼)
谢谢,验证中
to xyxfly(消极)
谢谢,验证中谢谢各位,学习完毕再结帖啊
好久不见了,你也来帮我了,正在学习,不多说了
以目前表的数据量来看你的语句和gahade(沙果)的看不出差别都是小于1秒,并且结果正确
谢谢