问个去优先的有关问题

问个去优先的问题
2007-9-302aaa
2007-9-301bbb
2007-12-311ccc
2007-12-312ddd
2008-3-312eee
2008-3-311fff
2008-6-301ggg
2008-6-302hhh
2008-9-301iii
2008-12-312kkk


2007-9-302aaa
2007-12-312ddd
2008-3-312eee
2008-6-302hhh
2008-9-301iii
2008-12-312kkk



[解决办法]

SQL code

--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([date] datetime,[state] int,[name] varchar(3))insert [test]select '2007-9-30',2,'aaa' union allselect '2007-9-30',1,'bbb' union allselect '2007-12-31',1,'ccc' union allselect '2007-12-31',2,'ddd' union allselect '2008-3-31',2,'eee' union allselect '2008-3-31',1,'fff' union allselect '2008-6-30',1,'ggg' union allselect '2008-6-30',2,'hhh' union allselect '2008-9-30',1,'iii' union allselect '2008-12-31',2,'kkk'select * from [test] awhere a.state=(select MAX(state) from test b where a.date=b.date)/*date    state    name2008-12-31 00:00:00.000    2    kkk2008-09-30 00:00:00.000    1    iii2008-06-30 00:00:00.000    2    hhh2008-03-31 00:00:00.000    2    eee2007-12-31 00:00:00.000    2    ddd2007-09-30 00:00:00.000    2    aaa*/
[解决办法]
SQL code
create table za(col1 date, col2 int, col3 varchar(5))insert into zaselect '2007-9-30', 2, 'aaa' union allselect '2007-9-30', 1, 'bbb' union allselect '2007-12-31', 1, 'ccc' union allselect '2007-12-31', 2, 'ddd' union allselect '2008-3-31', 2, 'eee' union allselect '2008-3-31', 1, 'fff' union allselect '2008-6-30', 1, 'ggg' union allselect '2008-6-30', 2, 'hhh' union allselect '2008-9-30', 1, 'iii' union allselect '2008-12-31', 2, 'kkk'with t as(select row_number() over(partition by col1 order by case col2 when 2 then 1 else 0 end desc) rn, col1,col2,col3 from za)select col1,col2,col3 from twhere rn=1/*col1       col2        col3---------- ----------- -----2007-09-30  2           aaa2007-12-31  2           ddd2008-03-31  2           eee2008-06-30  2           hhh2008-09-30  1           iii2008-12-31  2           kkk(6 row(s) affected)*/
[解决办法]
SQL code
select     CONVERT(varchar(10),[date],120) as [date],    [state],    [name]from     test awhere     not exists(        select             1         from             test b         where             a.[date]=b.[date]             and a.[state]<b.[state]        )--恩恩