我有以下一张表
ID DateValue
1 1.0000
1 5.0000
1 6.0000
2 9.0000
2 20.0000
2 35.0000
2 60.0000
3 72.0000
3 99.0000
3 100.0000
3 20.0000
3 6.0000
4 6.0000
4 12.0000
5 10.0000按ID和DateValue排序,希望能取到每一个ID的中间值,若ID总数为奇数,取第(n+1)/2个的值;如果是偶数,取第n/2个的值结果最后应该是
ID DateValue
1 5.0000
2 20.0000
3 72.0000
4 6.0000
5 10.0000这个sql应该如何写呢?
ID DateValue
1 1.0000
1 5.0000
1 6.0000
2 9.0000
2 20.0000
2 35.0000
2 60.0000
3 72.0000
3 99.0000
3 100.0000
3 20.0000
3 6.0000
4 6.0000
4 12.0000
5 10.0000按ID和DateValue排序,希望能取到每一个ID的中间值,若ID总数为奇数,取第(n+1)/2个的值;如果是偶数,取第n/2个的值结果最后应该是
ID DateValue
1 5.0000
2 20.0000
3 72.0000
4 6.0000
5 10.0000这个sql应该如何写呢?
--> Author : jinjazzli
--> Target : ---->1000
--> Date : 2009-12-08 14:55:19
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
declare @tb table (ID int,DateValue numeric(7,4))
insert into @tb
select 1,1.0000 union all
select 1,5.0000 union all
select 1,6.0000 union all
select 2,9.0000 union all
select 2,20.0000 union all
select 2,35.0000 union all
select 2,60.0000 union all
select 3,72.0000 union all
select 3,99.0000 union all
select 3,100.0000 union all
select 3,20.0000 union all
select 3,6.0000 union all
select 4,6.0000 union all
select 4,12.0000 union all
select 5,10.0000select * ,px=(select count(*) from @tb where id=t.id and DateValue<=t.DateValue)
into #t
from @tb tselect distinct id,datevalue=(select datevalue from #t where id=t.id and px=((select max(px) from #t where id=t.id)+1)/2) from #t tid datevalue
----------- ---------------------------------------
1 5.0000
2 20.0000
3 72.0000
4 6.0000
5 10.0000(5 行受影响)
drop table #t
--> 测试时间:2009-12-08 15:02:59
--> 测试菜鸟:l8r
--> 我的淘宝:《戒色坊》http://shop36766744.taobao.com/if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[DateValue] numeric(7,4))
insert [TB]
select 1,1.0000 union all
select 1,5.0000 union all
select 1,6.0000 union all
select 2,9.0000 union all
select 2,20.0000 union all
select 2,35.0000 union all
select 2,60.0000 union all
select 3,72.0000 union all
select 3,99.0000 union all
select 3,100.0000 union all
select 3,20.0000 union all
select 3,6.0000 union all
select 4,6.0000 union all
select 4,12.0000 union all
select 5,10.0000
select * from [TB] t
where (select count(1) from TB where ID=T.ID and DateValue<=T.DateValue)
=((select count(*) from TB where ID=T.ID)+1)/2
order by ID,DateValue/*ID DateValue
----------- ---------
1 5.0000
2 20.0000
3 72.0000
4 6.0000
5 10.0000(所影响的行数为 5 行)*/drop table [TB]
(
select n=row_number() over(partition by id order by DateValue)
ID,DateValue
From tb
)select cte.*
from cte,
(
select (case when max(n)%2=1 then max(n)/2+1 else max(n)/2 end) as n,
id
from
cte
group by id
) v
where cte.id=v.id and cte.n=v.n
declare @table table([ID] int,[DateValue] numeric(7,4))
insert @table
select 1,1.0000 union all
select 1,5.0000 union all
select 1,6.0000 union all
select 2,9.0000 union all
select 2,20.0000 union all
select 2,35.0000 union all
select 2,60.0000 union all
select 3,72.0000 union all
select 3,99.0000 union all
select 3,100.0000 union all
select 3,20.0000 union all
select 3,6.0000 union all
select 4,6.0000 union all
select 4,12.0000 union all
select 5,10.0000select [id],[DateValue]
from(select (select count(1) from @table where id = t.id and DateValue <= t.DateValue) as order_code,
(select ceiling(count(1)*1.0/2) from @table where id = t.id) as [row_count],
* from @table t ) h
where order_code = [row_count]
------------------------------------
1 5.0000
2 20.0000
3 72.0000
4 6.0000
5 10.0000
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[DateValue] numeric(7,4))
insert [tb]
select 1,1.0000 union all
select 1,5.0000 union all
select 1,6.0000 union all
select 2,9.0000 union all
select 2,20.0000 union all
select 2,35.0000 union all
select 2,60.0000 union all
select 3,72.0000 union all
select 3,99.0000 union all
select 3,100.0000 union all
select 3,20.0000 union all
select 3,6.0000 union all
select 4,6.0000 union all
select 4,12.0000 union all
select 5,10.0000
---查询---
SELECT
a3.id,
a3.DateValue
FROM
(
SELECT a1.id,a1.DateValue, COUNT(a1.DateValue) Rank
FROM tb a1, tb a2
WHERE a1.id=a2.id and a1.DateValue >= a2.DateValue
GROUP BY a1.id, a1.DateValue
) a3 ,
(SELECT id,(COUNT(*)+1)/2 as Rank FROM tb group by id) a4
WHERE a3.id=a4.id and a3.Rank = a4.Rank
order by a3.id
---结果---
id DateValue
----------- ---------
1 5.0000
2 20.0000
3 72.0000
4 6.0000
5 10.0000(所影响的行数为 5 行)
ID DateValue
1 1.0000
1 5.0000
1 6.0000
2 9.0000
2 20.0000
2 35.0000
2 60.0000
3 72.0000
3 72.0000
3 100.0000
3 20.0000
3 6.0000
4 6.0000
4 6.0000
5 10.0000 出来的结果还要求是
ID DateValue
1 5.0000
2 20.0000
3 72.0000
4 6.0000
5 10.0000 这个sql应该如何写呢?如果用以上的方法,只能得到以下数据
ID DateValue
1 5.0000
2 20.0000
5 10.0000
然而这个结果不是我最终要得到的。
--那就用临时表吧
--> 测试数据:@table
declare @table table([ID] int,[DateValue] numeric(7,4))
insert @table
select 1,1.0000 union all
select 1,5.0000 union all
select 1,6.0000 union all
select 2,9.0000 union all
select 2,20.0000 union all
select 2,35.0000 union all
select 2,60.0000 union all
select 3,72.0000 union all
select 3,72.0000 union all
select 3,100.0000 union all
select 3,20.0000 union all
select 3,6.0000 union all
select 4,6.0000 union all
select 4,6.0000 union all
select 5,10.0000select identity(int,1,1) as code,* into #temp from @table order by [ID],[DateValue]select [ID],[DateValue]
from #temp t
where code = (select min(code) from #temp where id = t.id)+
(select ceiling(count(1)*1.0/2) from #temp where id = t.id) - 1
drop table #temp
----------------------------------1 5.0000
2 20.0000
3 72.0000
4 6.0000
5 10.0000
SELECT DISTINCT ID, (SELECT MAX(DATEVALUE)
FROM (SELECT TOP 50 PERCENT *
FROM @TB B
WHERE A.ID = B.ID
ORDER BY DATEVALUE) C
) AS DATEVALUE
FROM @TB A
select id, datavalue from
(select *, rid=row_number() over (partition by id order by datavalue),
cnt=count(1) over (partition by id) from tb) t
where rid=(case when cnt%2=1 then (cnt+1)/2 else cnt/2 end)
create table #tb([ID] int,[DateValue] numeric(7,4))
insert #tb
select 1,1.0000 union all
select 1,5.0000 union all
select 1,6.0000 union all
select 2,9.0000 union all
select 2,20.0000 union all
select 2,35.0000 union all
select 2,60.0000 union all
select 3,72.0000 union all
select 3,72.0000 union all
select 3,99.0000 union all
select 3,100.0000 union all
select 3,20.0000 union all
select 3,6.0000 union all
select 4,6.0000 union all
select 4,6.0000 union all
select 4,12.0000 union all
select 5,10.0000select id, DateValue from
(select *, rid=row_number() over (partition by id order by datevalue),
cnt=count(1) over (partition by id) from #tb) t
where rid=(case when cnt%2=1 then (cnt+1)/2 else cnt/2 end)
/*
1 5.0000
2 20.0000
3 72.0000
4 6.0000
5 10.0000
*/