---分组、排序求更好的方法---

[luoyoumou]

现A表有以下数据结构： 
    rq      ddsj 
-------------------- 
  200805    30 
  200805    40 
  200805    50 
  200805    20 
  200806    250 
  200806    200 
  200806    310 
  200806    100 
  200806    300 
  200806    130 
  200806    430 
--------------------
按rp分组、ddsj排序，选择出每组摆在正中间的记录行（若为偶数行的话，选择摆在正中间的两条记录行）
drop table a;

create table a(rq varchar(8), ddsj int)
insert into a
select
'200805',30 union all select
'200805',40 union all select 
'200805',50 union all select 
'200805',20 union all select 
'200806',250 union all select
'200806',200 union all select
'200806',310 union all select
'200806',100 union all select
--'200806',300 union all select
'200806',130;

------我的方法如下：
select t1.orders, t1.rq, t1.ddsj 
    from ( select ROW_NUMBER() OVER(PARTITION BY rq ORDER BY ddsj ) orders, rq, ddsj from a ) t1
inner join   ( select rq, max(orders)+2 orders from 
            (select rq, ROW_NUMBER() OVER(PARTITION BY rq ORDER BY ddsj ) orders from a ) t3 group by rq )
t2
on t1.rq=t2.rq and  ((t1.orders=convert(int,t2.orders/2) or t1.orders=convert(int,(t2.orders-0.5)/2)));

---求更好的方法？？？？

[lang071234]

都没看懂。

[gsk09]

ddsj 重复的要怎么处理？

[gsk09]

哦，中间的行不是中间的值啊。看错了

[SQL77]

来学习

[gsk09]

select a.rq,a.ddsj
from 
	(select 
		 abs(row_number()over(partition by rq order by ddsj)
				-(count(*)over(partition by rq)+1)/2.0) c
		,*
	from a)a
where c<1

[gsk09]

跟feixianxxx的方法2重了

[Shawn_1978]

;with cte_final
as (
select rq,ddsj,COUNT(*) over(partition by rq) as cnt
,ROW_NUMBER() over(partition by rq order by ddsj ) as rn
from a
)
select rn,rq,ddsj
from cte_final
where rn = (case
when cnt%2=0 and rn=(cnt/2+1) then (cnt/2+1) --Mid+1
when cnt%2=0 and rn=(cnt/2) then (cnt/2) --Mid
else (cnt/2+1)
end)

[luoyoumou]

[Quote=引用 17 楼 gsk09 的回复:]
ddsj 重复的要怎么处理？
[/Quote]

-----可以先看成：ddsj不重复!

[Shawn_1978]

[Quote=引用 15 楼 feixianxxx 的回复:]
SQL code--方法2select rq,ddsjfrom
(select ROW_NUMBER()over(partitionby rqorderby ddsj)as rn1,COUNT(*)over(partitionby rq)as rn
,*from a ) kwhereabs(2*rn1-rn-1)<=1/*
rq ddsj
-------- -----------
2¡­
[/Quote]

这个牛！！！
致敬！

[SQL77]

declare @a table(rq varchar(8), ddsj int)
insert into @a
select
'200805',30 union all select
'200805',40 union all select 
'200805',50 union all select 
'200805',20 union all select 
'200806',250 union all select
'200806',200 union all select
'200806',310 union all select
'200806',100 union all select
--'200806',300 union all select
'200806',130;

select (select count(*) from @a where rq=t.rq and ddsj<=t.ddsj)as num ,* 
into #t from @a t order by ddsj

select
	 t.* 
from
	 #t t ,
( 
select 
	(min(num)+max(num))/2 num,
	ceiling((min(num)+max(num))*1.0/2)num1,rq 
from 
	#t 
	group by rq)as t1 

where (t.num=t1.num or t.num=t1.num1) and t.rq=t1.rq

num         rq       ddsj        
----------- -------- ----------- 
2           200805   30
3           200805   40
3           200806   200

（所影响的行数为 3 行）

[dawugui]

create table a(rq varchar(8), ddsj int)
insert into a
select
'200805',30 union all select
'200805',40 union all select 
'200805',50 union all select 
'200805',20 union all select 
'200806',250 union all select
'200806',200 union all select
'200806',310 union all select
'200806',100 union all select
--'200806',300 union all select
'200806',130;

select m.rq , n .ddsj from
(select rq , count(1) cnt from a group by rq) m ,
(select * ,px = (select count(1) from a where rq = t.rq and ddsj < t.ddsj) + 1 from a t) n
where m.rq = n.rq and m.cnt%2 = 1 and n.px = m.cnt/2 + 1
union all
select m.rq , n .ddsj from
(select rq , count(1) cnt from a group by rq) m ,
(select * ,px = (select count(1) from a where rq = t.rq and ddsj < t.ddsj) + 1 from a t) n
where m.rq = n.rq and m.cnt%2 = 0 and (n.px = m.cnt/2 or n.px = m.cnt/2 + 1)
order by m.rq , ddsj

drop table a

/*
rq       ddsj        
-------- ----------- 
200805   30
200805   40
200806   200

（所影响的行数为 3 行）

*/

[dawugui]

--sql 2000的写法.

create table a(rq varchar(8), ddsj int)
insert into a
select
'200805',30 union all select
'200805',40 union all select 
'200805',50 union all select 
'200805',20 union all select 
'200806',250 union all select
'200806',200 union all select
'200806',310 union all select
'200806',100 union all select
--'200806',300 union all select
'200806',130;

select m.rq , n .ddsj from
(select rq , count(1) cnt from a group by rq) m ,
(select * ,px = (select count(1) from a where rq = t.rq and ddsj < t.ddsj) + 1 from a t) n
where m.rq = n.rq and ((m.cnt%2 = 1 and n.px = m.cnt/2 + 1) or (m.cnt%2 = 0 and (n.px = m.cnt/2 or n.px = m.cnt/2 + 1)))
order by m.rq , ddsj

drop table a

/*
rq       ddsj        
-------- ----------- 
200805   30
200805   40
200806   200

（所影响的行数为 3 行）

*/

[小宏]

小麦帅了一米

[devilidea]

日

[luoyoumou]

[Quote=引用 29 楼 devilidea 的回复:]
日
[/Quote]

???

[幸运的意外]

我公司用的是2000,估计实现这个问题老麻烦了吧.

[duanxugang]

学习

[taoistong]

人齐结帖

[James__Zhan]

学习...

[chnerhu]

强悍！