求一SQL

[wangxiaofeiwuqiao]

有如下数据：

ITEM_CODE        INDATE         UPRICE
00001           2011-08-01       0.5
00001           2011-09-01       0.8
00001           2011-07-01       0.7
00002           2011-05-12       0.2
00002           2011-07-11       0.4
00002           2011-08-18       0.9

我要的结果：相同ITEM_CODE的要最大的INDATE,UPRICE要取对应的最大INDATE的UPRICE

00001           2011-09-01       0.8
00002           2011-08-18       0.9

数据库环境：sql 2000

[koumingjie]

[Quote=引用 7 楼 xaresx 的回复:]
select ITEM_CODE,INDATE,UPRICE
from (
select ITEM_CODE,INDATE,UPRICE,row_number() over(partition by ITEM_CODE order by INDATE desc) as rownum from 表名) as a
where a.rownum=1
[/Quote]

人家数据库是2000的

[快溜]

select * from tb a
 where not exists(select 1 from tb where ITEM_CODE=a.ITEM_CODE and INDATE>a.INDATE)

[--小F--]

select
  *
from
   tb t
where
  indate=(select max(indate) from tb where item=t.item)

[NBDBA]

这语句不是每天都在CSDN出现的吗，搂主好像不是新来的

select * from tab a
where not exists (
  select 1
  from tab
  where ITEM_CODE= a.ITEM_CODE
  and INDATE>a.INDATE
  )

[chuanzhang5687]

select * from tb t where not exists(select 1 from tb where ITEM_CODE=t.ITEM_CODE and INDATE>t.INDATE)

[jyh070207]

create table #t(item_code varchar(10), indate datetime ,uprice numeric(12,3))
insert into #t(item_code,indate,uprice)
select '00001','2011-08-01',0.5
union all
select '00001','2011-09-01',0.8
union all
select '00001','2011-07-01',0.7
union all
select '00002','2011-05-12',0.2
union all
select '00002','2011-07-11',0.4
union all
select '00002','2011-08-18',0.9

select * from #t 
where not exists(select 1 from #t t where t.item_code = #t.item_code and t.indate >  #t.indate)

/*
00001	2011-09-01 00:00:00.000	.800
00002	2011-08-18 00:00:00.000	.900
*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-09-05 09:44:03
-- Verstion:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) 
--	Apr  2 2010 15:53:02 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ITEM_CODE] varchar(5),[INDATE] datetime,[UPRICE] numeric(2,1))
insert [tb]
select '00001','2011-08-01',0.5 union all
select '00001','2011-09-01',0.8 union all
select '00001','2011-07-01',0.7 union all
select '00002','2011-05-12',0.2 union all
select '00002','2011-07-11',0.4 union all
select '00002','2011-08-18',0.9
--------------开始查询--------------------------
select
  *
from
   tb t
where
  indate=(select max(indate) from tb where ITEM_CODE=t.ITEM_CODE)
order by 1
----------------结果----------------------------
/* ITEM_CODE INDATE                  UPRICE
--------- ----------------------- ---------------------------------------
00001     2011-09-01 00:00:00.000 0.8
00002     2011-08-18 00:00:00.000 0.9

(2 行受影响)
*/

[XaresX]

select ITEM_CODE,INDATE,UPRICE
from (
select ITEM_CODE,INDATE,UPRICE,row_number() over(partition by ITEM_CODE order by INDATE desc) as rownum from 表名) as a
where a.rownum=1

[wangxiaofeiwuqiao]

1楼的结果不对啊

[木小舟]

楼上大部分都正确，相同ITEM_CODE的要最大的INDATE,UPRICE要取对应的最大INDATE的UPRICE，
可最大INDATE也有可能相同，所以取出结果UPRICE有可能是多条记录

[NBDBA]

[Quote=引用 8 楼 wangxiaofeiwuqiao 的回复:]
1楼的结果不对啊
[/Quote]
1楼的结果怎么不对？不是一样的语句吗？？