表结构是这样的
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 9SELECT MAX(date) from table1 WHERE convert(varchar(4),date,120)='2011'需求是这样的 :得到data列中的最大值 和对应的时间
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 9SELECT MAX(date) from table1 WHERE convert(varchar(4),date,120)='2011'需求是这样的 :得到data列中的最大值 和对应的时间
FROM (select *,row=ROW_NUMBER()OVER(PARTITION BY YEAR([date]) ORDER BY [date] desc) from table1)t
WHERE row=1 and YEAR([date])=2011
if object_id('tb')is not null
drop table tb
go
create table tb
(
id int,
date datetime,
data int
)
go
insert into tb
select 1,'2011-11-22',3 union all
select 2,'2011-01-21',12 union all
select 3,'2011-05-30',3 union all
select 4,'2011-03-24',12 union all
select 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-21
12 2011-03-24(2 行受影响)*/
drop table tb
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
id int identity(1,1),
date varchar(10),
data int
)
go
insert into tb (date,data)
select '2011-11-12',3 union all
select '2011-01-21',12 union all
select '2011-05-30',3 union all
select '2011-03-24',12 union all
select '2011-10-12',9
go
select * from tb where data=(select max(data) from tb)
go
/*
id date data
----------- ---------- -----------
2 2011-01-21 12
4 2011-03-24 12(2 行受影响)*/
drop table tb
go
create table tb
(
id int identity(1,1),
date varchar(10),
data int
)
go
insert into tb (date,data)
select '2011-11-12',3 union all
select '2011-01-21',12 union all
select '2011-05-30',3 union all
select '2011-03-24',12 union all
select '2011-10-12',9
go
select * from tb where data=(select max(data) from tb where left(date,4)='2011')
go
/*
id date data
----------- ---------- -----------
2 2011-01-21 12
4 2011-03-24 12(2 行受影响)
*/
from tb inner join
(select max(data) as maxi from tb ) m on
tb.data=m.maxi