SQL2000求日前日期数据行

SQL2000求最近日期数据行
mssql2000 最近数据行
视图内容:VIEW_GOODSSTORE
goodsid,billdate,STOREID,STORESTATEID
74 ,2011-2-19, 11, ,104
74 ,2011-6-14, 11, ,104

78, 2011-11-11, 12, , 33

91 ,2011-6-19, 11, ,104
91 ,2011-6-19, 11, ,105
91 ,2011-6-19, 11, ,106

结果：
当goodsid相面时，取最大日期的行 (取出来后不要重复的)

goodsid,billdate,STOREID,STORESTATEID
74 ,2011-6-14, 11, ,104
78, 2011-11-11, 12, , 33
91 ,2011-6-19, 11, ,106

[最优解释]
早知道我答了算了。

SELECT  *
FROM    VIEW_GOODSSTORE a
WHERE   EXISTS ( SELECT 1
                 FROM   ( SELECT    MAX(billdate) billdate ,
                                    goodsid
                          FROM      VIEW_GOODSSTORE
                          GROUP BY  goodsid
                        ) b
                 WHERE  a.billdate = b.billdate
                        AND a.goodsid = b.goodsid )

[其他解释]
select * from VIEW_GOODSSTORE a
where not exsits(select 1 from VIEW_GOODSSTORE where goodsid=a.goodsid and billdate>a.billdate)
[其他解释]
SELECT goodsid,max(billdate),max(STOREID),max(STORESTATEID)
from VIEW_GOODSSTORE
group by goodsid

[其他解释]

引用:

select * from VIEW_GOODSSTORE a
where not exsits(select 1 from VIEW_GOODSSTORE where goodsid=a.goodsid and billdate>a.billdate)

运行有如下提示:
服务器: 消息 156，级别 15，状态 1，行 2
在关键字 'select' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 2
第 2 行: ')' 附近有语法错误。

[其他解释]

SELECT  *
FROM    VIEW_GOODSSTORE a
WHERE   NOT EXISTS ( SELECT 1
                     FROM   VIEW_GOODSSTORE 
 
                     WHERE  goodsid = a.goodsid
                            AND billdate &gt; a.billdate )

[其他解释]


if object_id('dbo.t1') is not null
drop table t1;
go

create table t1(
goodsid int,
billdate datetime,
STOREID int,
STORESTATEID int
)

insert into t1
select 74,'2011-2-19',11,104
union all
select 74,'2011-6-14',11,104
union all
select 78,'2011-11-11',12,33
union all
select 91,'2011-6-19',11,104
union all
select 91,'2011-6-19',11,105
union all
select 91,'2011-6-19',11,106

select * from t1

select * from (
select goodsid,billdate,storeid,storestateid,row_number() over(partition by goodsid order by billdate,storestateid desc) as rn from t1) tbl
where rn=1

[其他解释]

引用:

SQL code?

123456

SELECT * FROM VIEW_GOODSSTORE a WHERE NOT EXISTS ( SELECT 1 FROM VIEW_GOODSSTORE WHERE goodsid = a.goodsid ……

经测试，结果不对
[其他解释]
我只是把ssp2009那个报错去掉而已。
[其他解释]

引用:

SQL code?

123456789101112131415161718192021222324252627282930

if object_id('dbo.t1') is not nulldrop table t1; go create table t1( goodsid int, billdate datetime, STOREID int, STORESTATEID……

谢谢回复,不知道是不是我的SQL是2000版的问题,运行提示:
服务器: 消息 195，级别 15，状态 10，行 2
'row_number' 不是可以识别的函数名。

SQL2000求日前日期数据行

热点推荐