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
谢谢
------解决方案--------------------
- SQL code
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 23 b 60 25 c 85 36 cc 30 32 aa 80 11 a 50 17 d 80 48 dd 30 4(8 行受影响)*/godrop table zf_ffr