create table table1(id int)
create table table2(id int, mydate int,name char(1))insert table1 values(1200)
insert table1 values(1300)insert table2 values(1200, 4 , 'A')
insert table2 values(1200 , 5 , 'B')
insert table2 values(1200 , 7 , 'C')
insert table2 values(1200 ,6 , 'D')declare @id int
select @id = 1200
select table1.id,mydate,name from table2,table1
where table2.id =* table1.id and table2.mydate = (select max(a.mydate) from table2 a,table1 b where a.id =* b.id and b.id = @id)
and table1.id = @id
id mydate name
----------- ----------- ----
1200 7 C(所影响的行数为 1 行)
@id 取1300时
id mydate name
----------- ----------- ----
1300 NULL NULL(所影响的行数为 1 行)
create table table2(id int, mydate int,name char(1))insert table1 values(1200)
insert table1 values(1300)insert table2 values(1200, 4 , 'A')
insert table2 values(1200 , 5 , 'B')
insert table2 values(1200 , 7 , 'C')
insert table2 values(1200 ,6 , 'D')declare @id int
select @id = 1200
select table1.id,mydate,name from table2,table1
where table2.id =* table1.id and table2.mydate = (select max(a.mydate) from table2 a,table1 b where a.id =* b.id and b.id = @id)
and table1.id = @id
id mydate name
----------- ----------- ----
1200 7 C(所影响的行数为 1 行)
@id 取1300时
id mydate name
----------- ----------- ----
1300 NULL NULL(所影响的行数为 1 行)
數據準備:
create table t1(id int primary key);
create table t2(id int,mydate int,[name] varchar(10));
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);
insert into t2 values (1,7,'A');
insert into t2 values (1,6,'B');
insert into t2 values (3,9,'C');
insert into t2 values (3,1,'A');
insert into t2 values (3,11,'D');查詢:
Select t1.ID , t2.mydate, t2.name From t1 Left Join (
Select t2.* From t2 Join (
Select ID,Max(mydate) mydate From t2 Group By ID
) t2Tmp On t2.ID=t2Tmp.ID And t2.mydate=t2Tmp.mydate
) t2 On t1.ID=t2.ID結果:
id mydate name
-------------------------
1 7 A
2 NULL NULL
3 11 D
4 NULL NULLSQL共有5行,兩次包裝,第2行-第4行可單獨使用,它在t2表上進行自連接實現按ID選取MYDATE列最大的一行資料,形成的結果集再與T1進行左聯從而達到效果。
select a.id,isnull(mydate,0) mydate,isnull(name,null) name from t1 a left join
(
select id,mydate,name
from t2 a
where not exists(select id from t2 where id=a.id and mydate>a.mydate group by id)
) b on a.id=b.id
LZ说话自相矛盾,不是说不能用函数,那你还用ISNULL?