修改sql语句

SQL code

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 

SQL code

--Score重复时保留名次空缺select * ,rank() over(partition by c# order by score desc)  as pxfrom scorder by c#--Score重复时合并名次select * ,dense_rank() over(partition by c# order by score desc)  as pxfrom scorder by c#
[解决办法]
没必要用关联
[解决办法]
SQL code
--示例数据CREATE TABLE tb(Name varchar(10),Score decimal(10,2))INSERT tb SELECT 'aa',99UNION ALL SELECT 'bb',56UNION ALL SELECT 'cc',56UNION ALL SELECT 'dd',77UNION ALL SELECT 'ee',78UNION ALL SELECT 'ff',76UNION ALL SELECT 'gg',78UNION ALL SELECT 'ff',50GO--1. 名次生成方式1,Score重复时合并名次SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score)FROM tb aORDER BY Place/*--结果Name       Score        Place ---------------- ----------------- ----------- aa         99.00        1ee         78.00        2gg         78.00        2dd         77.00        3ff         76.00        4bb         56.00        5cc         56.00        5ff         50.00        6--*/--2. 名次生成方式2,Score重复时保留名次空缺SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1FROM tb aORDER BY Place/*--结果Name       Score        Place --------------- ----------------- ----------- aa         99.00        1ee         78.00        2gg         78.00        2dd         77.00        4ff         76.00        5bb         56.00        6cc         56.00        6ff         50.00        8--*/
[解决办法]
SQL code
--非要用连接的话 可以这样 select    a.*,b.pxfrom   sc a,   (select C#,count(1) as px from sc group by c#)bwhere   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.pxfrom   sc a,   (select C#,count(1) as px from sc group by c#)b,   sc cwhere   a.c#=b.c#and   b.c#=c.c#and   c.score>a.score
[解决办法]
探讨

用查干嘛??就可能嘛~ 
 

SQL code

--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# o……

[解决办法]
1．RANK() OVER()——重复，不连续，1，1，3
2．DENSE_RANK() OVER()——重复，连续，1，1，2
3．ROW_NUMBER() OVER()——不重复，连续，1，2，3
4．NTILE() OVER()——根据NTILE后面括号里的数值，按照OVER()后面排序的字段分区并排序。例如：select ntile(4) over(order by cBH) as rowindex,* from huopin意思就是按照cBH排序huopin数据表里的数据，并每4条分一个区。
楼主可以看下这4个函数的具体用法。