有两个表A,B,我为用户表,B为用量表,有如下数据
a
uid uname ucode...
1 a 001
2 c 003
3 b 002
==========
b
uid dvalue dvalue tdate
1 455 454 2009-12-12
1 435 434 2009-11-12
2 365 364 2009-12-12
2 355 354 2009-11-12
3 565 564 2009-12-12
4 555 554 2009-11-12
======================
我想求每个用户最新日期的一条数据,并按uname,dvalue,dvalue1某一个排序,即应该有如下结果
按uname
uid uname ucode dvalue dvalue tdate
1 a 001 455 454 2009-12-12
2 b 002 365 364 2009-12-12
3 c 003 565 564 2009-12-12
=======
或者按dvalue
uid uname ucode dvalue dvalue tdate
2 b 002 365 364 2009-12-12
1 a 001 455 454 2009-12-12
3 c 003 565 564 2009-12-12应该怎么写呀
a
uid uname ucode...
1 a 001
2 c 003
3 b 002
==========
b
uid dvalue dvalue tdate
1 455 454 2009-12-12
1 435 434 2009-11-12
2 365 364 2009-12-12
2 355 354 2009-11-12
3 565 564 2009-12-12
4 555 554 2009-11-12
======================
我想求每个用户最新日期的一条数据,并按uname,dvalue,dvalue1某一个排序,即应该有如下结果
按uname
uid uname ucode dvalue dvalue tdate
1 a 001 455 454 2009-12-12
2 b 002 365 364 2009-12-12
3 c 003 565 564 2009-12-12
=======
或者按dvalue
uid uname ucode dvalue dvalue tdate
2 b 002 365 364 2009-12-12
1 a 001 455 454 2009-12-12
3 c 003 565 564 2009-12-12应该怎么写呀
b
uid dvalue dvalue tdate
1 455 454 2009-12-12
1 435 434 2009-11-12
2 365 364 2009-12-12
2 355 354 2009-11-12
3 565 564 2009-12-12
3 555 554 2009-11-12
if object_id('a') is not null drop table a
go
create table a([uid] INT,[uname] varchar(50),[ucode] varchar(50))
insert into a
select 1,'a','001' union all
select 2,'c','003' union all
select 3,'b','002'
goif object_id('b') is not null drop table b
go
create table b([uid] INT,[dvalue] INT,[dvalue1] INT,[tdate] datetime)
insert into b
select 1,455,454,'2009-12-12' union all
select 1,435,434,'2009-11-12' union all
select 2,365,364,'2009-12-12' union all
select 2,355,354,'2009-11-12' union all
select 3,565,564,'2009-12-12' union all
select 3,555,554,'2009-11-12'
goselect a.*,dvalue,dvalue1,tdate from a join
(select * from b b1 where not exists(select * from b where uid = b1.uid and tdate > b1.tdate)) b
on a.uid=b.uid/*
uid uname ucode dvalue dvalue1 tdate
----------- -------------------------------------------------- ------------------
1 a 001 455 454 2009-12-12 00:00:00.000
2 c 003 365 364 2009-12-12 00:00:00.000
3 b 002 565 564 2009-12-12 00:00:00.000(3 行受影响)
*/
*
from
a,b t
where
a.uid = t.uid and tdate = (select max(tdate) from b where uid = t.uid) order by t.uname
from a inner join b t on a.uid=t.uid
where not exists (select 1 from b where uid=t.uid and tdate>t.tdate)
order by a.uname