我有两个表
a
id time
1 2011-12-22 10:45:01.000
2 2011-11-22 10:45:01.000
3 2011-12-22 10:45:01.000b
id time
1 2011-12-22 10:45:01.000
1 2011-11-22 10:45:01.000
2 2011-02-22 10:45:01.000
2 2011-12-22 10:45:01.000
3 2011-11-22 10:45:01.000
3 2011-12-22 10:45:01.000
本来左连接是这么写的
select a.id,a.time,b.id,b.time from
a left join b on a.id=b.id结果:
id time id time
1 2011-12-22 10:45:01.000 1 2011-12-22 10:45:01.000
1 2011-12-22 10:45:01.000 1 2011-11-22 10:45:01.000
2 2011-11-22 10:45:01.000 2 2011-02-22 10:45:01.000
2 2011-11-22 10:45:01.000 2 2011-12-22 10:45:01.000
3 2011-12-22 10:45:01.000 3 2011-11-22 10:45:01.000
3 2011-12-22 10:45:01.000 3 2011-12-22 10:45:01.000现在只想取到b表中每个相同id的最后一行数据
结果:
id time id time
1 2011-12-22 10:45:01.000 1 2011-12-22 10:45:01.000
2 2011-11-22 10:45:01.000 2 2011-12-22 10:45:01.000
3 2011-12-22 10:45:01.000 3 2011-12-22 10:45:01.000
求sql怎么写
a
id time
1 2011-12-22 10:45:01.000
2 2011-11-22 10:45:01.000
3 2011-12-22 10:45:01.000b
id time
1 2011-12-22 10:45:01.000
1 2011-11-22 10:45:01.000
2 2011-02-22 10:45:01.000
2 2011-12-22 10:45:01.000
3 2011-11-22 10:45:01.000
3 2011-12-22 10:45:01.000
本来左连接是这么写的
select a.id,a.time,b.id,b.time from
a left join b on a.id=b.id结果:
id time id time
1 2011-12-22 10:45:01.000 1 2011-12-22 10:45:01.000
1 2011-12-22 10:45:01.000 1 2011-11-22 10:45:01.000
2 2011-11-22 10:45:01.000 2 2011-02-22 10:45:01.000
2 2011-11-22 10:45:01.000 2 2011-12-22 10:45:01.000
3 2011-12-22 10:45:01.000 3 2011-11-22 10:45:01.000
3 2011-12-22 10:45:01.000 3 2011-12-22 10:45:01.000现在只想取到b表中每个相同id的最后一行数据
结果:
id time id time
1 2011-12-22 10:45:01.000 1 2011-12-22 10:45:01.000
2 2011-11-22 10:45:01.000 2 2011-12-22 10:45:01.000
3 2011-12-22 10:45:01.000 3 2011-12-22 10:45:01.000
求sql怎么写
a left join b on a.id=b.id
and not exists(select 1 from b t where t.id=b.id and t.time>b.time)
go
create table [a]([id] int,[time] datetime)
insert [a]
select 1,'2011-12-22 10:45:01.000' union all
select 2,'2011-11-22 10:45:01.000' union all
select 3,'2011-12-22 10:45:01.000'
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int,[time] datetime)
insert [b]
select 1,'2011-12-22 10:45:01.000' union all
select 1,'2011-11-22 10:45:01.000' union all
select 2,'2011-02-22 10:45:01.000' union all
select 2,'2011-12-22 10:45:01.000' union all
select 3,'2011-11-22 10:45:01.000' union all
select 3,'2011-12-22 10:45:01.000'select a.id,a.time,b.id,b.time from
a left join b on a.id=b.id
and not exists(select 1 from b t where t.id=b.id and t.time>b.time)
/**
id time id time
----------- ----------------------- ----------- -----------------------
1 2011-12-22 10:45:01.000 1 2011-12-22 10:45:01.000
2 2011-11-22 10:45:01.000 2 2011-12-22 10:45:01.000
3 2011-12-22 10:45:01.000 3 2011-12-22 10:45:01.000(3 行受影响)
**/
go
create table [a]([id] int,[time] datetime)
insert [a]
select 1,'2011-12-22 10:45:01.000' union all
select 2,'2011-11-22 10:45:01.000' union all
select 3,'2011-12-22 10:45:01.000'
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int,[time] datetime)
insert [b]
select 1,'2011-12-22 10:45:01.000' union all
select 1,'2011-11-22 10:45:01.000' union all
select 2,'2011-02-22 10:45:01.000' union all
select 2,'2011-12-22 10:45:01.000' union all
select 3,'2011-11-22 10:45:01.000' union all
select 3,'2011-12-22 10:45:01.000'select a.id,a.time,b.id,b.time from
a left join (select id,MAX(time)as time from b group by id )b on a.id=b.id
(3 行受影响)(6 行受影响)
id time id time
----------- ----------------------- ----------- -----------------------
1 2011-12-22 10:45:01.000 1 2011-12-22 10:45:01.000
2 2011-11-22 10:45:01.000 2 2011-12-22 10:45:01.000
3 2011-12-22 10:45:01.000 3 2011-12-22 10:45:01.000(3 行受影响)
create table a(id int, time datetime)insert into a
select 1, '2011-12-22 10:45:01.000' union all
select 2, '2011-11-22 10:45:01.000' union all
select 3, '2011-12-22 10:45:01.000'create table b(id int, time datetime)insert into b
select 1, '2011-12-22 10:45:01.000' union all
select 1, '2011-11-22 10:45:01.000' union all
select 2, '2011-02-22 10:45:01.000' union all
select 2, '2011-12-22 10:45:01.000' union all
select 3, '2011-11-22 10:45:01.000' union all
select 3, '2011-12-22 10:45:01.000'
select *
from a
inner join
(select b1.id,b1.time from
(select row_number() over(partition by id order by (select 0)) rn,
* from b) b1
inner join
(select id,count(*) idcount from b group by id) b2
on b1.id=b2.id and b1.rn=b2.idcount) b3
on a.id=b3.idid time id time
----------- ----------------------- ----------- -----------------------
1 2011-12-22 10:45:01.000 1 2011-11-22 10:45:01.000
2 2011-11-22 10:45:01.000 2 2011-12-22 10:45:01.000
3 2011-12-22 10:45:01.000 3 2011-12-22 10:45:01.000(3 row(s) affected)
from a left join (select id, time from b group by id, time) bb on a.id=bb.id
select 1, '2011-12-22 10:45:01.000' union all
select 2, '2011-11-22 10:45:01.000' union all
select 3, '2011-12-22 10:45:01.000'create table [tb](id int, time datetime)insert into [tb]
select 1, '2011-12-22 10:45:01.000' union all
select 1, '2011-11-22 10:45:01.000' union all
select 2, '2011-02-22 10:45:01.000' union all
select 2, '2011-12-22 10:45:01.000' union all
select 3, '2011-11-22 10:45:01.000' union all
select 3, '2011-12-22 10:45:01.000'select k.* from ta a left join (select i.id,i.time from (select row_number() over(order by time) row,* from tb) i
,(select id,max(row) row from (select row_number() over(order by time) row,* from tb) i group by id) h
where i.id=h.id and i.row=h.row) k on a.id=k.id