新银，求条SQL语句解决思路

新银，求条SQL语句
表结构是这样的
table1
id date data
1 2011-11-22 3  
2 2011-01-21 12  
3 2011-05-30 3  
4 2011-03-24 12
5 2011-10-22 9



SELECT MAX(date) from table1 WHERE convert(varchar(4),date,120)='2011'

需求是这样的 ：得到data列中的最大值 和对应的时间

[解决办法]

SQL code

SELECT top 1 * from table1 WHERE convert(varchar(4),date,120)='2011' order by date desc
[解决办法]
SQL code
SELECT id,date,dataFROM (select *,row=ROW_NUMBER()OVER(PARTITION BY YEAR([date]) ORDER BY [date] desc) from table1)tWHERE row=1 and YEAR([date])=2011
[解决办法]
SQL code
use stuif object_id('tb')is not nulldrop table tbgocreate table tb(id int,date datetime,data int)goinsert into tbselect 1,'2011-11-22',3 union allselect 2,'2011-01-21',12 union allselect 3,'2011-05-30',3 union allselect 4,'2011-03-24',12 union allselect 4,'2011-10-22',9需求是这样的 ：得到data列中的最大值 和对应的时间 select data,convert(varchar(10),date,120 )date from tb where data=(select max(data) from tb)/*data        date----------- ----------12          2011-01-2112          2011-03-24(2 行受影响)*/drop table tb
[解决办法]
或
SQL code
SELECT * FROM table1 AS a WHERE NOT EXISTS(SELECT 1 FROM table1 WHERE YEAR([date])=YEAR(a.[date]) AND [date]>a.[date]) AND YEAR([date])=2011SELECT * FROM table1 AS a WHERE [date]=(SELECT MAX([date]) FROM table1 WHERE YEAR([date])=YEAR(a.[date]))AND YEAR([date])=2011
[解决办法]
SQL code
select * from table1 where date=(select max(date) from tb) and convert(varchar(4),date,120)='2011'
[解决办法]
SQL code
if object_id('tb','U') is not null   drop table tbgocreate table tb( id int identity(1,1), date varchar(10), data int)goinsert into tb (date,data)select '2011-11-12',3 union allselect '2011-01-21',12 union allselect '2011-05-30',3 union allselect '2011-03-24',12 union allselect '2011-10-12',9goselect * from tb where data=(select max(data) from tb)go/*id          date       data----------- ---------- -----------2           2011-01-21 124           2011-03-24 12(2 行受影响)*/