一个SQL排序难题，求指教

[zhuyangpeng]

CREATE TABLE [dbo].[zf_ffr](
[id] [int] NULL,
[xingming] [varchar](10) ,
[fenshu] [int] NULL,
[lianxi] [varchar](5)
)
insert into zf_ffr values(1,'a',50,'1')
insert into zf_ffr values(2,'aa',80,'1')
insert into zf_ffr values(3,'b',60,'2')
insert into zf_ffr values(4,'bb',90,'2')
insert into zf_ffr values(5,'c',85,'3')
insert into zf_ffr values(6,'cc',30,'3')

上表实现分房排名，字段分别为编号、姓名，分数、标志。要求按分数从高到低实现双职工分房排名。其中标志位相同代表这两个分房人为一家，按照家庭分（家庭取夫妻两人高分者）从高到低排序。达到目标结果为：
4,bb,90,2
3,b,60,2
5,c,85,3
6,cc,30,3
2,aa,80,1
1,a,50,1
谢谢

[zhuyangpeng]

问题已经解决了，非常感谢qianjin036a的帮助

[-晴天]

这是什么年代的东西,还分房哪!

[-晴天]

你们老师太OUT了!

[zhuyangpeng]

有没有高手呀？

[-晴天]

你的结果还是错的,80,50 那一家,比85,30 那家要高!

CREATE TABLE [dbo].[zf_ffr](
[id] [int] NULL,
[xingming] [varchar](10) ,
[fenshu] [int] NULL,
[lianxi] [varchar](5)  
)
insert into zf_ffr values(1,'a',50,'1')
insert into zf_ffr values(2,'aa',80,'1')
insert into zf_ffr values(3,'b',60,'2')
insert into zf_ffr values(4,'bb',90,'2')
insert into zf_ffr values(5,'c',85,'3')
insert into zf_ffr values(6,'cc',30,'3')

select * from zf_ffr a order by (select sum(fenshu) from zf_ffr where lianxi=a.lianxi) desc,fenshu desc
/*
id          xingming   fenshu      lianxi
----------- ---------- ----------- ------
4           bb         90          2
3           b          60          2
2           aa         80          1
1           a          50          1
5           c          85          3
6           cc         30          3

(6 行受影响)

*/
go
drop table zf_ffr

[zhuyangpeng]

抱歉，没有表达清楚
家庭分是取夫妻两人高分的人
排序时按家庭分从高到低排序
给出低分是为了让分房人更清楚
排名 编号 姓名 分数 标志
1 4,bb,90,2
3,b,60,2
2 5,c,85,3
6,cc,30,3
3 2,aa,80,1
1,a,50,1

[-晴天]

那就这样呗:
select * from zf_ffr a order by (select max(fenshu) from zf_ffr where lianxi=a.lianxi) desc,fenshu desc

[zhuyangpeng]

非常感谢，我在后面又加了两条数据，结果就不对了，麻烦看一下

insert into zf_ffr values(1,'a',50,'1')
insert into zf_ffr values(2,'aa',80,'1')
insert into zf_ffr values(3,'b',60,'2')
insert into zf_ffr values(4,'bb',90,'2')
insert into zf_ffr values(5,'c',80,'3')
insert into zf_ffr values(6,'cc',30,'3')
insert into zf_ffr values(7,'d',80,'4')
insert into zf_ffr values(8,'dd',30,'4')

[zhuyangpeng]

结果成这样了
5 c 90 3
6 cc 30 3
7 d 80 4
2 aa 80 1
1 a 60 1
8 dd 30 4
4 bb 70 2
3 b 50 2
标志位，4和4应该在一起

[-晴天]

CREATE TABLE [dbo].[zf_ffr](
[id] [int] NULL,
[xingming] [varchar](10) ,
[fenshu] [int] NULL,
[lianxi] [varchar](5)  
)
insert into zf_ffr values(1,'a',50,'1')
insert into zf_ffr values(2,'aa',80,'1')
insert into zf_ffr values(3,'b',60,'2')
insert into zf_ffr values(4,'bb',90,'2')
insert into zf_ffr values(5,'c',85,'3')
insert into zf_ffr values(6,'cc',30,'3')

select * from zf_ffr a order by (select max(fenshu) from zf_ffr where lianxi=a.lianxi) desc,fenshu desc
/*
id          xingming   fenshu      lianxi
----------- ---------- ----------- ------
4           bb         90          2
3           b          60          2
5           c          85          3
6           cc         30          3
2           aa         80          1
1           a          50          1

(6 行受影响)

*/
go
drop table zf_ffr

[-晴天]

CREATE TABLE [dbo].[zf_ffr](
[id] [int] NULL,
[xingming] [varchar](10) ,
[fenshu] [int] NULL,
[lianxi] [varchar](5)  
)
insert into zf_ffr values(1,'a',50,'1')
insert into zf_ffr values(2,'aa',80,'1')
insert into zf_ffr values(3,'b',60,'2')
insert into zf_ffr values(4,'bb',90,'2')
insert into zf_ffr values(5,'c',85,'3')
insert into zf_ffr values(6,'cc',30,'3')
insert into zf_ffr values(7,'d',80,'4')
insert into zf_ffr values(8,'dd',30,'4')

select * from zf_ffr a order by (select max(fenshu) from zf_ffr where lianxi=a.lianxi) desc,lianxi,fenshu desc
/*
id          xingming   fenshu      lianxi
----------- ---------- ----------- ------
4           bb         90          2
3           b          60          2
5           c          85          3
6           cc         30          3
2           aa         80          1
1           a          50          1
7           d          80          4
8           dd         30          4

(8 行受影响)

*/
go
drop table zf_ffr