sql查询问题

[skylight778]

有个表:
id user yw ss date
1 a 87 90 2011-2-5
2 a 45 89 2011-2-6
3 b 91 45 2011-2-7
4 c 89 69 2011-2-8
5 c 89 74 2011-2-9
6 b 91 45 2011-2-10
7 a 34 12 2011-2-11
8 b 24 53 2011-2-12
9 c 55 34 2011-2-13
我需要取出每个用户yw最大的值,当一个用户的最大值有两条以上,取ss最大的,如果yw ss最大的值都一样再按id或是时间这种唯一的值取

SELECT * FROM tbl T WHERE NOT EXISTS(SELECT 1 FROM tbl WHERE user=T.user AND yw>T.yw ) order by yw desc,ss desc,id desc

这个结果该是这样
1 a 87 90 2011-2-5
6 b 91 45 2011-2-10
3 b 91 45 2011-2-7
5 c 89 74 2011-2-9
4 c 89 69 2011-2-8
像这种 找出来的是当 yw最大的值(像c 的两条记录都是89都会出现)有多条相同时多会出现.而我希望的是,当有两条或是多条记录时,只出现大这几条只ss最大的.如果有yw ss最大值有几条都是一样的就以唯一值(比如添加的时间或是ID)排序

1 a 87 90 2011-2-5
6 b 91 45 2011-2-10
5 c 89 74 2011-2-9

网上找到的都是在没有出现重复的情况 ,希望各位帮忙谢谢.数据库是access ,不要用户sql2005 或是没用户的函数像row_number()

[君_洛洛]

SELECT * FROM tbl T
WHERE NOT EXISTS(SELECT 1 FROM tbl WHERE user=T.user AND (yw>T.yw or yw=T.yw and id>t.id) order by yw desc,ss desc,id desc

[yyoinge]

SELECT * FROM tbl T  
WHERE NOT EXISTS(SELECT 1 FROM tbl WHERE [user]=T.[user] AND (yw>T.yw or (yw=T.yw and id>t.id))) 
order by yw desc,ss desc,id desc   

[天下如山]

create table table1(id int identity(1,1),usera varchar(20),yw int,ss int,datea smalldatetime)
insert into table1
select 'a',87,90,'2011-2-5'  union all
select 'a',45,89, '2011-2-6' union all
select 'b',91,45, '2011-2-7' union all
select 'c',89,69, '2011-2-8' union all
select 'c',89,74, '2011-2-9' union all
select 'b',91,45, '2011-2-10' union all
select 'a',34,12, '2011-2-11' union all
select 'b',24,53, '2011-2-12' union all
select 'c',55,34,'2011-2-13'

select * from table1 a where not exists(select 1 from table1 where  a.usera=table1.usera and (table1.yw>a.yw or table1.yw=a.yw and table1.ss>a.ss or table1.yw=a.yw and table1.ss=a.ss and table1.datea>a.datea )) order by usera 

/*
id          usera                yw          ss          datea                                                  
----------- -------------------- ----------- ----------- ----------------------------1           a                    87          90          2011-02-05 00:00:00
6           b                    91          45          2011-02-10 00:00:00
5           c                    89          74          2011-02-09 00:00:00

（所影响的行数为 3 行）

*/

[打一壶酱油]

SELECT * FROM tbl T  
WHERE NOT EXISTS(SELECT 1 FROM tbl WHERE [user]=T.[user] AND (yw>T.yw or (yw=T.yw and id>t.id))) 
order by yw desc,ss desc,id desc   

[longai123]

exists not exists 搞得我烦死了