declare @t table (id int identity,nameid int,price money) //nameid为物料id号 insert into @t (nameid,price) select distinct 物料id,价格 from 订单表 order by 物料id,价格 --如果重复价格不忽略时,把distinct去掉--取一个价格 select a.nameid,a.price from @t a, (select nameid,min(id) minid,max(id) maxid from @t group by nameid) b where a.nameid=b.nameid and a.id=(b.maxid+b.minid)/2 --取二个价格 select a.nameid,a.price from @t a, (select nameid,min(id) minid,max(id) maxid from @t group by nameid) b where a.nameid=b.nameid and (a.id in ((b.maxid+b.minid)/2,b.maxid+b.minid)/2+1)
2005用ROW_NUMBER(),2000用临时表加标识列
是要取 declare @rowX int,@listX intset @rowX =select count(*) from tb set @listX=(@rowX /2)+1如果是2005可以写成 select top 1 @listX from (select top @listX id,price,name from tb order by id) A order by id desc
我觉得先按 价格 来个排序,rownumber 并且加一个排名列(rankID)。(select row_number() over(order by price) from XX) 获取 总共的记录数(select count(1) from XX ) 判断记录数为 偶数还是奇数, 然后取出 中间的一行 ,或两行。
insert into @t (nameid,price) select distinct 物料id,价格 from 订单表 order by 物料id,价格 --如果重复价格不忽略时,把distinct去掉--取一个价格
select a.nameid,a.price
from @t a, (select nameid,min(id) minid,max(id) maxid from @t group by nameid) b
where a.nameid=b.nameid and a.id=(b.maxid+b.minid)/2
--取二个价格
select a.nameid,a.price
from @t a, (select nameid,min(id) minid,max(id) maxid from @t group by nameid) b
where a.nameid=b.nameid and (a.id in ((b.maxid+b.minid)/2,b.maxid+b.minid)/2+1)
declare @rowX int,@listX intset @rowX =select count(*) from tb
set @listX=(@rowX /2)+1如果是2005可以写成
select top 1 @listX
from
(select top @listX id,price,name from tb order by id) A
order by id desc
获取 总共的记录数(select count(1) from XX )
判断记录数为 偶数还是奇数,
然后取出 中间的一行 ,或两行。