楼上的方法只能得到一条记录,就是 表1 ID NAME AXXX BXXX 1 中 AB A_SS 我是说.相应的第一条记录... ID NAME AXXX BXXX 1 中 AB A_SS 2 挂 SS A_AB 3 百 SD A_DD..........................
--测试数据 if object_id('t1') is not null drop table t1 go create table t1(id int, a_id int, b_id int, name varchar(10)) insert t1 select 1, 100, 122, '中' union all select 2, 132, 134, '挂' union all select 3, 333, 234, '百' if object_id('t2') is not null drop table t2 go create table t2(id int, lx_id int, aa varchar(10), time datetime) insert t2 select 1, 100, 'AB', '2005-3-3 12:00' union all select 2, 132, 'SS', '2005-3-3 11:34' union all select 3, 100, 'AE', '2005-3-3 11:22' union all select 4, 100, 'SD', '2005-3-3 11:00' union all select 5, 333, 'SD', '2005-3-3 10:00' union all select 6, 333, 'DD', '2005-3-3 09:00' go if object_id('t3') is not null drop table t3 go create table t3(id int, lx_id int, aa varchar(10), time datetime) insert t3 select 1, 134, 'A_AB', '2005-3-3 12:00' union all select 2, 122, 'A_SS', '2005-3-3 11:34' union all select 3, 134, 'A_AE', '2005-3-3 11:22' union all select 4, 134, 'A_SD', '2005-3-3 11:00' union all select 5, 133, 'A_SD', '2005-3-3 10:00' union all select 6, 234, 'A_DD', '2005-3-3 09:00'
--查询语句 select table1.id, table1.name, table1.axx, table1.bxx from (select t1.id, name, t2.aa as axx, t3.aa as bxx, t2.time as atime, t3.time as btime from t1 left join t2 on t1.a_id=t2.lx_id left join t3 on t1.b_id=t3.lx_id)table1, (select t1.id, name, max(t2.time) as atime, max(t3.time) as btime from t1 left join t2 on t1.a_id=t2.lx_id left join t3 on t1.b_id=t3.lx_id group by t1.id, name)table2 where table1.id=table2.id and table1.name=table2.name and table1.atime=table2.atime and table1.btime=table2.btime
表1
ID NAME AXXX BXXX
1 中 AB A_SS
我是说.相应的第一条记录...
ID NAME AXXX BXXX
1 中 AB A_SS
2 挂 SS A_AB
3 百 SD A_DD..........................
if object_id('t1') is not null drop table t1
go
create table t1(id int, a_id int, b_id int, name varchar(10))
insert t1 select 1, 100, 122, '中'
union all select 2, 132, 134, '挂'
union all select 3, 333, 234, '百'
if object_id('t2') is not null drop table t2
go
create table t2(id int, lx_id int, aa varchar(10), time datetime)
insert t2 select 1, 100, 'AB', '2005-3-3 12:00'
union all select 2, 132, 'SS', '2005-3-3 11:34'
union all select 3, 100, 'AE', '2005-3-3 11:22'
union all select 4, 100, 'SD', '2005-3-3 11:00'
union all select 5, 333, 'SD', '2005-3-3 10:00'
union all select 6, 333, 'DD', '2005-3-3 09:00'
go
if object_id('t3') is not null drop table t3
go
create table t3(id int, lx_id int, aa varchar(10), time datetime)
insert t3 select 1, 134, 'A_AB', '2005-3-3 12:00'
union all select 2, 122, 'A_SS', '2005-3-3 11:34'
union all select 3, 134, 'A_AE', '2005-3-3 11:22'
union all select 4, 134, 'A_SD', '2005-3-3 11:00'
union all select 5, 133, 'A_SD', '2005-3-3 10:00'
union all select 6, 234, 'A_DD', '2005-3-3 09:00'
select table1.id, table1.name, table1.axx, table1.bxx
from (select t1.id, name, t2.aa as axx, t3.aa as bxx,
t2.time as atime, t3.time as btime
from t1 left join t2 on t1.a_id=t2.lx_id
left join t3 on t1.b_id=t3.lx_id)table1,
(select t1.id, name, max(t2.time) as atime, max(t3.time) as btime
from t1 left join t2 on t1.a_id=t2.lx_id
left join t3 on t1.b_id=t3.lx_id
group by t1.id, name)table2
where table1.id=table2.id
and table1.name=table2.name
and table1.atime=table2.atime
and table1.btime=table2.btime