分组查询，难，高手请教

[ojuju10]

create table #(id int identity,name varchar(10))
insert into # select 'a'
insert into # select 'a'
insert into # select 'a'
insert into # select 'a'
insert into # select 'b'
insert into # select 'b'
insert into # select 'b'
insert into # select 'b'
insert into # select 'd'
insert into # select 'd'

查询每个name组的50%，最后得到的结果是：
1 a
2 a
5 b
6 b
9 d

[xiequanqin]

select id,name from
(

select top 50 percent id,name,rank() over(partition by name order by id) cur_rank from #
order by cur_rank,name,id
) t
order by id,cur_rank

-----------------------------------
不好意思,没看到50%

[xiequanqin]

我的浏览器不支持源代码..

[xiequanqin]

select m.* from
(select id,name,rank() over(partition by name order by id) cur_rank from #) m,
(select name,round(count(id)*0.5+0.5,0) need_rank from # group by name) n
where m.name=n.name and m.cur_rank<=n.need_rank
order by m.name,id

---------------------------------------------------------------
讨论后,觉得这个比较好...
前面的虽然在这个问题能解决,但是如果各个name 的个数\要求输出的比例换一下,可能不对

这个算法遍历 表2次,
5楼的算法没问题,不过遍历N+1次

[smilyvm]

谁能告诉我rank()什么意思

[rainvictor]

5楼的比较好

[wzy_love_sly]

鸟儿厉害

[ojuju10]

5楼的比较好，但是很慢

[fa_ge]

create   table   #(id   int   identity,name   varchar(10)) 
insert   into   #   select   'a' 
insert   into   #   select   'a' 
insert   into   #   select   'a' 
insert   into   #   select   'a' 
insert   into   #   select   'b' 
insert   into   #   select   'b' 
insert   into   #   select   'b' 
insert   into   #   select   'b' 
insert   into   #   select   'd' 
insert   into   #   select   'd' 

select * from # a
where (select count(1) from # where name=a.name and id <a.id)<((select count(1) from # where name=a.name )/2)

/*
id          name       
----------- ---------- 
1           a
2           a
5           b
6           b
9           d

（所影响的行数为 5 行）
*/

[sunhonglei2004]

select top 50 percent * from # where name='a' union all
select top 50 percent * from # where name='b' union all
select top 50 percent * from # where name='d'

[chuifengde]

declare @a table (id   int   identity,name   varchar(10)) 
insert   into   @a   select   'a' 
insert   into   @a   select   'a' 
insert   into   @a   select   'a' 
insert   into   @a  select   'a' 
insert   into   @a   select   'b' 
insert   into   @a   select   'b' 
insert   into   @a   select   'b' 
insert   into   @a   select   'b' 
insert   into   @a   select   'd' 
insert   into   @a   select   'd' 

select aa.id,aa.name from
(select *,(select count(1) from @a where name=a.name and id<=a.id) idd from @a a)aa
inner join
(select name,count(1)/2 num from @a group by name)bb
on aa.name=bb.name
where idd<=num
--result
/*
id          name       
----------- ---------- 
1           a
2           a
5           b
6           b
9           d
*/

[fa_ge]

楼上的方法不能通用,如果name有100个的话，不是要写100个union all

[dobear_0922]

综合１楼和２楼：

create   table   #(id   int   identity,name   varchar(10)) 
insert   into   #   select   'a' 
insert   into   #   select   'a' 
insert   into   #   select   'a' 
insert   into   #   select   'a' 
insert   into   #   select   'b' 
insert   into   #   select   'b' 
insert   into   #   select   'b' 
insert   into   #   select   'b' 
insert   into   #   select   'd' 
insert   into   #   select   'd' 

select * from # a
where id in (select top 50 percent id from #  where name=a.name order by id)

drop table #

/*
id          name
----------- ----------
1           a
2           a
5           b
6           b
9           d

(5 row(s) affected)
*/

[dobear_0922]

貌似综合之后比鹤兄的要简洁，不知效率如何，呵呵　

[xiequanqin]

select id,name from
(
select id,name,rank() over(partition by name order by id) cur_rank from #
) t
where cur_rank<=2

SQL2005 下

[xiequanqin]

create table #(id int identity,name varchar(10))
insert into # select 'a'
insert into # select 'a'
insert into # select 'a'
insert into # select 'a'
insert into # select 'b'
insert into # select 'b'
insert into # select 'b'
insert into # select 'b'
insert into # select 'd'
insert into # select 'd'

select * from #

select id,name from
(
select id,name,rank() over(partition by name order by id) cur_rank from #
) t
where cur_rank<=2

/*
id name
----------- ----------
1 a
2 a
5 b
6 b
9 d
10 d

(6 行受影响)
*/

[ShenLiang2025]

1）先做下分析 Floor 5的～～
如果我给你他的另外的语句 你看看结果
select * from #
where id in (select top 40 percent id from # where name=a.name order by id)
结果怎样呢？为什么30 percent 还会和50 percent 一样呢？谁先解释下原因先～～


2）谢大哥（XQQ）的Rank() over(partition by Column order by Column)对于分区统计查询 比较常见


3）我的表数据如下：我追加了一个记录到'a'里
1 a
2 a
3 a
4 a
5 b
6 b
7 b
8 b
9 d
10 d
11 a

如果对XQQ的查询不熟悉，下面的例子可能对你有帮助～～（--① 我将#替换为表名test了②以下查询是基于上面提供的数据）

select id,name,rank() over(partition by name order by id)cur_rank from test;

Result：
1 a 1
2 a 2
3 a 3
4 a 4
11 a 5
5 b 1
6 b 2
7 b 3
8 b 4
9 d 1
10 d 2

select id,name,count(*)over(partition by name ) sum_rank from test;

Result：
1 a 5
2 a 5
3 a 5
4 a 5
11 a 5
5 b 4
6 b 4
7 b 4
8 b 4
9 d 2
10 d 2

4）所以这样 你再看上面的SQL代码 是不是很轻松了呢？～

5）不过还有个问题，谢大哥的查询结果是
1 a 1
2 a 2
3 a 3
5 b 1
6 b 2
9 d 1

问题是如果我们想显示（前一半2个，后一半3个（针对'a'而言 'a'现在是奇数5个）），那么Round（）恐怕要做修改了。

select m.id,m.name from
(select id,name,rank() over(partition by name order by id) cur_rank from test) m,
(select name,round(count(id)*0.5,1) need_rank from test group by name) n
where m.name=n.name and m.cur_rank<=n.need_rank
order by m.name,id

Result：
1 a
2 a
5 b
6 b
9 d

注意不再是以下查询的结果了：

select m.id,m.name from
(select id,name,rank() over(partition by name order by id) cur_rank from test) m,
(select name,round(count(id)*0.5,0) need_rank from test group by name) n
where m.name=n.name and m.cur_rank<=n.need_rank
order by m.name,id

1 a
2 a
3 a
5 b
6 b
9 d

6）Oracle里用TRUNC()函数直接可以解决上面的问题

7)希望可以继续讨论 完善 批评 指导～～

[xiequanqin]

select m.id,name,cur_rank from
(select id,name,
rank() over(partition by name order by id) cur_rank,
count(id) over(partition by name) sum_rank
from #
) m
where cur_rank<=((sum_rank)*0.5+0.4999999 /*50%向上取整*/)

呵呵...楼上的朋友给了我灵感,进一步优化~

[ShenLiang2025]

如果rank()不熟悉 用row_number() 好了 结果一样的～～

[zhiguo2008]

...