表结构是这样的
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 行受影响)*/