如下:
TableA里面有一条叫做 ABC的数据,TableB里面有两条关联TableA里面ABC的数据Select * From TableA ta Left Join TableB tb on tb.TableAID=ta.ID 时候会查出两条关于ABC数据的结果,其实只是想一条关于ABC的数据
TableA里面有一条叫做 ABC的数据,TableB里面有两条关联TableA里面ABC的数据Select * From TableA ta Left Join TableB tb on tb.TableAID=ta.ID 时候会查出两条关于ABC数据的结果,其实只是想一条关于ABC的数据
另外,这个好像不太对:
Select * From TableA ta Left Join TableB tb on tb.TableAID=ta.ID
ABC 在哪儿?
DECLARE @T1 TABLE(COL1 VARCHAR(1),COL4 VARCHAR(1))
DECLARE @T2 TABLE(COL2 VARCHAR(5),COL3 VARCHAR(1))
INSERT @T2 SELECT 'red','A'
UNION ALL SELECT 'red','B'
UNION ALL SELECT 'red','C'
UNION ALL SELECT 'black','D'
INSERT @T1(COL1) SELECT COL3 FROM @T2 WHERE COL2='red'
SELECT * FROM @T1SELECT COL1,COL4,COL2 FROM @T1 T1 LEFT JOIN @T2 T2 ON T1.COL1=T2.COL3 COL1 COL4 COL2
---- ---- -----
A NULL red
B NULL red
C NULL red
TableA
ID Title
1 ABC
2 标题2标题2标题2TableB
ID TableAID TName CreateTime
1 1 10 2008-11-18 12:12:12
2 1 20 2008-11-19 12:12:12
3 1 30 2008-11-19 13:12:12
4 2 10 2008-11-20 12:12:12要求:
查TableA表里面所有的数据,但要联上TableB的表,其联上TableB表的条件是 CreateTime为2008-11-19天(这个时候会有两条数据出来,这时再按CrateTime降序,只要排在前面的TableB表ID为3的行),其最终要的结果是:ID Title ID(TableB) TableAID(TableB) TName(TableB) CreateTime(TableB)
1 ABC 3 1 30 2008-11-19 13:12:12
2 标题2... 4 2 10 2008-11-20 12:12:12
insert into tablea select 1,'ABC'
insert into tablea select 2,'标题2标题2标题2'
create table TableB (ID int,TableAID int,TName int,CreateTime datetime)
insert into tableb select 1,1,10,'2008-11-18 12:12:12'
insert into tableb select 2,1,20,'2008-11-19 12:12:12'
insert into tableb select 3,1,30,'2008-11-19 13:12:12'
insert into tableb select 4,2,10,'2008-11-20 12:12:12'
select a.*,b.* from tablea a inner join tableb b on a.id=b.tableaid
where b.id in(select max(id) from tableb group by tableaid)
go
drop table tablea,tableb
/*
ID Title ID TableAID TName CreateTime
----------- -------------------- ----------- ----------- ----------- -----------------------
1 ABC 3 1 30 2008-11-19 13:12:12.000
2 标题2标题2标题2 4 2 10 2008-11-20 12:12:12.000
*/
条件不是id,而是CreateTime字段,还有就是不要受ID 字段1、2、3、4的影响
if object_id('tablea')is not null drop table tablea
go
create table TableA(ID int,Title varchar(20))
insert tablea select 1, 'ABC'
insert tablea select 2, '标题2标题2标题2'
if object_id('tableb')is not null drop table tableb
go
create table TableB(ID int,TableAID int,TName int ,CreateTime datetime)
insert tableb select 1, 1, 10, '2008-11-18 12:12:12'
insert tableb select 2, 1, 20, '2008-11-19 12:12:12'
insert tableb select 3, 1, 30, '2008-11-19 13:12:12'
insert tableb select 4, 2, 10, '2008-11-20 12:12:12'
select a.*,t.* from tablea a inner join(
select * from tableb b where not exists(select 1 from tableb where tableaid=b.tableaid and
createtime>b.createtime ))t
on a.id=t.tableaid
/*ID Title ID TableAID TName CreateTime
----------- -------------------- ----------- ----------- ----------- ------------------------------------------------------
1 ABC 3 1 30 2008-11-19 13:12:12.000
2 标题2标题2标题2 4 2 10 2008-11-20 12:12:12.000*/
这样也不对,在TableB里面还有其它好多类似的数据