DENSE_RANK() sql2000排序
这个排序在SQL2000下面怎么改？
select DENSE_RANK() over(order by ddate,cinvcode,ph,hw) as 序号,ddate,cinvcode,ph,hw,sum(qty1) as qty1 from xianCun
where cinvcode IS NOT NULL AND hw IS NOT NULL
group by ddate,cinvcode,ph,hw
------解决方案--------------------
select 序号=identity(int,1,1),ddate,cinvcode,ph,hw,sum(qty1) as qty1
into #tmp
from xianCun
where cinvcode IS NOT NULL AND hw IS NOT NULL
group by ddate,cinvcode,ph,hw
order by ddate,cinvcode,ph,hw
select * from #tmp
------解决方案--------------------
select 序号=identity(int,1,1),id=id*1,ddate,cinvcode,ph,hw,sum(qty1) as qty1
into #tmp from xianCun where cinvcode IS NOT NULL AND hw IS NOT NULL group by ddate,cinvcode,ph,hw order by ddate,cinvcode,ph,hw
------解决方案--------------------
如果只是查询，可这样用
SELECT 序号= (SELECT COUNT(DISTINCT BINARY_CHECKSUM(ddate,cinvcode,ph,hw))
FROM xianCun
WHERE cinvcode IS NOT NULL AND hw IS NOT NULL
AND (ddate<a.ddate OR (ddate=a.ddate AND (cinvcode<a.cinvcode OR (cinvcode=a.cinvcode AND (ph<a.ph OR (ph=a.ph AND hw<a.hw)))))))+1,
ddate ,
cinvcode ,
ph ,
hw ,
SUM(qty1) AS qty1
FROM xianCun AS a
WHERE cinvcode IS NOT NULL
AND hw IS NOT NULL
GROUP BY ddate ,
cinvcode ,
ph ,
hw