根据多个分组项目排序得出总的项目排序的问题

[duset]

Q: 有8个人,赵钱孙李,周吴郑王.每次都有几个人凑在一起比身高,
每次参加的人数也不同.已有5组数据如下
要求得出全部人的总的身高排序

CREATE TABLE GroupSeq
(
	GroupItem		VARCHAR(10),
	Seq				INT,
	NAME			VARCHAR(20),
)

INSERT INTO GroupSeq (GroupItem,Seq,NAME)
		  SELECT 'A',1,'zhao'
UNION ALL SELECT 'A',2,'qian'
UNION ALL SELECT 'A',3,'li'

UNION ALL SELECT 'B',1,'sun'
UNION ALL SELECT 'B',2,'zhou'
UNION ALL SELECT 'B',3,'wu'

UNION ALL SELECT 'C',1,'wu'
UNION ALL SELECT 'C',2,'wang'

UNION ALL SELECT 'D',1,'sun'
UNION ALL SELECT 'D',2,'zhou'
UNION ALL SELECT 'D',3,'wu'
UNION ALL SELECT 'D',4,'zheng'

UNION ALL SELECT 'E',1,'qian'
UNION ALL SELECT 'E',2,'sun'
UNION ALL SELECT 'E',3,'zheng'


--result
--1	'zhao'
--2	'qian'
--3	'sun'
--3	'li'
--4	'zhou'
--5	'wu'
--6	'zheng'
--6	'wang'

--OR

--result
--1	'zhao'
--2	'qian'
--3	'sun'
--4	'li'
--5	'zhou'
--6	'wu'
--7	'zheng'
--8	'wang'

(注:无法确定的顺序的人可以用相同序号或者随意排序,即如果sun和li彼此无法确定谁高,则可以使用同一序号3,或一个3,一个4,前后无所谓)

[dawugui]

你根据什么排?哪个是身高?

[duset]

回1楼:

具体身高是不知道的,
GroupItem是指次,即A次有3个人参加...

Seq 是指身高的排序,即A次的身高比较是 zhao > qian > li

这样解释是否清楚?

[Herb2]

set nocount on
CREATE TABLE px
(
    G        VARCHAR(10),
    S                INT,
    NAME            VARCHAR(20),
)

INSERT INTO px (G,S,NAME)
          SELECT 'A',1,'zhao'
UNION ALL SELECT 'A',2,'qian'
UNION ALL SELECT 'A',3,'li'

UNION ALL SELECT 'B',1,'sun'
UNION ALL SELECT 'B',2,'zhou'
UNION ALL SELECT 'B',3,'wu'

UNION ALL SELECT 'C',1,'wu'
UNION ALL SELECT 'C',2,'wang'

UNION ALL SELECT 'D',1,'sun'
UNION ALL SELECT 'D',2,'zhou'
UNION ALL SELECT 'D',3,'wu'
UNION ALL SELECT 'D',4,'zheng'

UNION ALL SELECT 'E',1,'qian'
UNION ALL SELECT 'E',2,'sun'
UNION ALL SELECT 'E',3,'zheng'

go
create table #(id int identity,name varchar(10))
while exists(select 1  from px)
 begin
   insert #(name) select distinct name from px a where s=1 and not exists (select 1 from px where name=a.name and s<>1)
   update px set s= s - 1 where g in (select g from px a where s = 1 and not exists(select 1 from px where name=a.name and s<>1))
   delete px where name in(select name from #)
 end 
select * from # 
drop table #
go
drop table px
/*
id          name       
----------- ---------- 
          1 zhao
          2 qian
          3 li
          4 sun
          5 zhou
          6 wu
          7 wang
          8 zheng
*/

[dawugui]

[Quote=引用 2 楼 duset 的回复:]
回1楼:

具体身高是不知道的,
GroupItem是指次,即A次有3个人参加...

Seq 是指身高的排序,即A次的身高比较是 zhao > qian > li

这样解释是否清楚?
[/Quote]
如果是这样的需求,需要用循环来处理,麻烦,友情帮顶.

[duset]

理解一下Herb2的思路:

做一个循环插入
每次向临时表中插入排序最高(=1),并且没有出现任何一次排序>1的情况,这种数据一定是总序里面最高的.
然后将这个排序最高的人从源表中除去,并将该人所参与的次中其他人的排序-1

想了一下,应该没有问题,很清晰,谢谢..

[sdxiong]

呵呵，这题目有点意思，虽然已经贴，但这是试了一下

declare @t TABLE (    Gp        VARCHAR(10),    Seq                INT,    NAME            VARCHAR(20))

INSERT INTO @t (Gp,Seq,NAME)
SELECT 'A',1,'zhao'UNION ALL SELECT 'A',2,'qian'UNION ALL SELECT 'A',3,'li'
UNION ALL SELECT 'B',1,'sun'UNION ALL SELECT 'B',2,'zhou'UNION ALL SELECT 'B',3,'wu'
UNION ALL SELECT 'C',1,'wu'UNION ALL SELECT 'C',2,'wang'
UNION ALL SELECT 'D',1,'sun'UNION ALL SELECT 'D',2,'zhou'UNION ALL SELECT 'D',3,'wu'UNION ALL SELECT 'D',4,'zheng'
UNION ALL SELECT 'E',1,'qian'UNION ALL SELECT 'E',2,'sun'UNION ALL SELECT 'E',3,'zheng'

;with s as (
select name as mname,gp,seq,name,cast(gp+name as varchar(100)) as ns,1 as c from @t
union all
select mname,b.gp,b.seq,b.name,cast(ns+b.gp+b.name as varchar(100)) as ns,a.c+1 as c
from s a
inner join @t b on a.gp=b.gp and a.seq>b.seq and charindex(b.gp+b.name,a.ns)=0
union all
select mname,b.gp,b.seq,b.name,cast(ns+b.gp+b.name as varchar(100)) as ns,a.c
from s a
inner join @t b on a.gp<>b.gp and a.name=b.name and charindex(b.gp+b.name,a.ns)=0
)
select mname,max(c) as c from s group by mname order by c

/*
mname                c
-------------------- -----------
zhao                 1
qian                 2
sun                  3
li                   3
zhou                 4
wu                   5
wang                 6
zheng                6

(8 行受影响)

*/