34,590
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(source_code varchar(8), name varchar(8), update_date datetime, remark varchar(8))
insert into #a
select 'A1', 'name1', null, 'test1' union all
select 'B1', 'name1', null, 'test2'
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(source_code varchar(8), name varchar(8), update_date datetime, remark varchar(8))
insert into #b
select 'A1', 'name1', '2010-10-15', 'test3'
;with cte as
(
select id=row_number()over(partition by source_code,name order by update_date desc), *
from (select * from #a union all select * from #b) t
)
select * from cte where id = 1
/*
id source_code name update_date remark
-------------------- ----------- -------- ----------------------- --------
1 A1 name1 2010-10-15 00:00:00.000 test3
1 B1 name1 NULL test2
*/
--更正
with cte as
(select * from A
union all
select * from B)
--方法一
select *from cte t
where not exists
(select 1 from cte where source_code=t.source_code and name=t.name and update_date
>t.update_date )
--方法二
select *from cte t
where update_date=(select max(update_date) from cte where source_code=t.source_code and name=t.name )
--直接+一列试试
select source_code,name,max(update_date) update_date,remark
from (select * from A
union all
select * from B) aa
group by source_code,name,remark
with cte as
(select * from A
union all
select * from B)
--方法一
select source_code,name,max(update_date) update_date,remark
from cte group by source_code,name,remark
--方法二
select *from cte t
where not exists
(select 1 from cte where source_code=t.source_code and name=t.name and remark=t.remark and update_date
>t.update_date )
--方法三
select *from cte t
where update_date=(select max(update_date) from cte where source_code=t.source_code and name=t.name and remark=t.remark)
--> 测试数据:#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(source_code varchar(8), name varchar(8), update_date datetime, remark varchar(8))
insert into #a
select 'A1', 'name1', null, 'test1' union all
select 'B1', 'name1', null, 'test2'
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(source_code varchar(8), name varchar(8), update_date datetime, remark varchar(8))
insert into #b
select 'A1', 'name1', '2010-10-15', 'test3'
with cte as (select * from #a union all select * from #b)
select a.source_code,b.name,a.update_date,b.remark
from (select source_code,max(update_date) as update_date from cte group by source_code) a,cte b
where a.source_code=b.source_code and isnull(a.update_date,1)=isnull(b.update_date,1)
/*
source_code name update_date remark
----------- -------- ----------------------- --------
A1 name1 2010-10-15 00:00:00.000 test3
B1 name1 NULL test2
*/
--去掉isnull,就没有update_date为NULL的记录
with cte as (select * from #a union all select * from #b)
select a.source_code,b.name,a.update_date,b.remark
from (select source_code,max(update_date) as update_date from cte group by source_code) a,cte b
where a.source_code=b.source_code and a.update_date=b.update_date
/*
source_code name update_date remark
----------- -------- ----------------------- --------
A1 name1 2010-10-15 00:00:00.000 test3
*/
select * into #tt
from (select * from tA
union all
select * from tB) aa
select source_code,max(update_date),name,max(remark) from #tt group by source_code,name
--结果:
/*
source_code update_date name remark
------------- ---------------- --------------- -------------------
A1 2010-10-15 name1 test3
B1 NULL name1 test2
*/