create table SC(S# varchar(10),C# varchar(10),score decimal(18,1))
insert into SC values('01' , '01' , 80)
insert into SC values('01' , '02' , 90)
insert into SC values('01' , '03' , 99)
insert into SC values('02' , '01' , 70)
insert into SC values('02' , '02' , 60)
insert into SC values('02' , '03' , 80)
insert into SC values('03' , '01' , 80)
insert into SC values('03' , '02' , 80)
insert into SC values('03' , '03' , 80)
insert into SC values('04' , '01' , 50)
insert into SC values('04' , '02' , 30)
insert into SC values('04' , '03' , 20)
insert into SC values('05' , '01' , 76)
insert into SC values('05' , '02' , 87)
insert into SC values('06' , '01' , 31)
insert into SC values('06' , '03' , 34)
insert into SC values('07' , '02' , 89)
insert into SC values('07' , '03' , 98)
go
/*问题:如何将下列的两条语句修改为关联查询,不用子查询*/
--Score重复时保留名次空缺
select t.* , px = (select count(1) from SC where C# = t.C# and score > t.score) + 1 from sc t order by t.c# , px 
--Score重复时合并名次
select t.* , px = (select count(distinct score) from SC where C# = t.C# and score >= t.score) from sc t order by t.c# , px 

--Score重复时保留名次空缺
select * ,rank() over(partition by c# order by score desc)  as px
from sc
order by c#
--Score重复时合并名次
select * ,dense_rank() over(partition by c# order by score desc)  as px
from sc
order by c#

------解决方案--------------------
没必要用关联

------解决方案--------------------
SQL code
--示例数据
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50
GO

--1. 名次生成方式1,Score重复时合并名次
SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score)
FROM tb a
ORDER BY Place
/*--结果
Name       Score        Place 
---------------- ----------------- ----------- 
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        3
ff         76.00        4
bb         56.00        5
cc         56.00        5
ff         50.00        6
--*/



--2. 名次生成方式2,Score重复时保留名次空缺
SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1
FROM tb a
ORDER BY Place
/*--结果
Name       Score        Place 
--------------- ----------------- ----------- 
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        4
ff         76.00        5
bb         56.00        6
cc         56.00        6
ff         50.00        8
--*/

------解决方案--------------------
SQL code
--非要用连接的话 可以这样 

select 
   a.*,b.px
from
   sc a,
   (select C#,count(1) as px from sc group by c#)b
where
   a.c#=b.c#
and
   b.score > a.score

------解决方案--------------------
探讨


SQL code
--非要用连接的话 可以这样 

select 
   a.*,b.px
from
   sc a,
   (select C#,count(1) as px from sc group by c#)b
where
   a.c#=b.c#
and
   b.score > a.score


------解决方案--------------------
SQL code
select 
   a.s#,a.c#,c.score,b.px
from
   sc a,
   (select C#,count(1) as px from sc group by c#)b,
   sc c
where
   a.c#=b.c#
and
   b.c#=c.c#
and
   c.score>a.score

------解决方案--------------------
探讨

用關聯查詢干嘛??這樣就可能嘛~