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
谢谢
[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
谢谢
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
家庭分是取夫妻两人高分的人
排序时按家庭分从高到低排序
给出低分是为了让分房人更清楚
排名 编号 姓名 分数 标志
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
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')
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应该在一起
[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
[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