一个通过年月取最新数据的问题

[shenqdm]

A表如下
resourceId ,country, year ,month ,value
1, china , 2009, 1, 1
1, china, 2010, 5, 3
1, china, 2009, 10, 2
2, china, 2010, 1, 6

现在给你的 条件是 resourceId =1 ，country=china,
怎么样找到最新的那条数据 ，即1, china, 2010, 5, 3

[永生天地]

select top 1 * from A
where resourceId =1 and country='china'
order by [year] desc,[month] desc

[csuxp2008]

select top 1*
from a
order by year desc,month desc,value desc

可以不？

[csuxp2008]

[Quote=引用 2 楼 csuxp2008 的回复:]
SQL code
select top 1*
from a
order by year desc,month desc,value desc

可以不？
[/Quote]

忘记where条件了 - -

[华夏小卒]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (resourceId int,country varchar(5),year int,month int,value int)
insert into #tb
select 1,'china',2009,1,1 union all
select 1,'china',2010,5,3 union all
select 1,'china',2009,10,2 union all
select 2,'china',2010,1,6

select top 1 * from #tb
where resourceId =1 and country='china'
order by ltrim(year)+'-'+ltrim(month)+'-01' desc


resourceId  country year        month       value
----------- ------- ----------- ----------- -----------
1           china   2010        5           3

(1 行受影响)

[华夏小卒]

[Quote=引用 1 楼 xys_777 的回复:]
select top 1 * from A
where resourceId =1 and country='china'
order by [year] desc,[month] desc
[/Quote]我的思维还是比较局限哦

[liuchuan_beijing]

[Quote=引用 1 楼 xys_777 的回复:]
select top 1 * from A
where resourceId =1 and country='china'
order by [year] desc,[month] desc
[/Quote]
up~~~

[Johnson]

[Quote=引用 1 楼 xys_777 的回复:]
select top 1 * from A
where resourceId =1 and country='china'
order by [year] desc,[month] desc
[/Quote]

[春风十里耶耶耶]

支持1楼