修改sql语句

Question

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 

Answer 1

用關聯查詢干嘛??這樣就可能嘛~--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#

Answer 2

--示例数据
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
--*/排名的就这样比较简单了

Answer 3

--非要用连接的话 可以这样 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

Answer 4

表b里面沒有score字段.運行出錯了~
我覺得,如果非要用關聯,必須各加一個順序號字段前,用順序號去對應.

Answer 5

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

Answer 6

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个函数的具体用法。

Answer 7

因为我个人觉得，该子查询能查询出来的结果，也可以用关联查询（无括号）写得出来，再说该子查询本身用的也是inner join 关联查询。

Answer 8

/*
按子查询得出来的结果如下：
S#      C#     score    px
01 01 80.0 1
03 01 80.0 1
05 01 76.0 3
02 01 70.0 4
04 01 50.0 5
06 01 31.0 6
01 02 90.0 1
07 02 89.0 2
05 02 87.0 3
03 02 80.0 4
02 02 60.0 5
04 02 30.0 6
01 03 99.0 1
07 03 98.0 2
02 03 80.0 3
03 03 80.0 3
06 03 34.0 5
04 03 20.0 6
*/

Answer 9

--To:小F 你语句的结果，##。S#   C#     score    px
02 01 80.0 6
04 01 80.0 6
05 01 80.0 6
06 01 80.0 6
05 02 90.0 6
07 02 90.0 6
04 02 90.0 6
03 02 90.0 6
02 02 90.0 6
02 03 99.0 6
03 03 99.0 6
04 03 99.0 6
07 03 99.0 6
06 03 99.0 6
04 01 70.0 6
06 01 70.0 6
04 02 60.0 6
04 03 80.0 6
06 03 80.0 6
02 01 80.0 6
04 01 80.0 6
05 01 80.0 6
06 01 80.0 6
04 02 80.0 6
02 02 80.0 6
04 03 80.0 6
06 03 80.0 6
06 01 50.0 6
02 01 76.0 6
04 01 76.0 6
06 01 76.0 6
04 02 87.0 6
03 02 87.0 6
02 02 87.0 6
04 03 34.0 6
05 02 89.0 6
04 02 89.0 6
03 02 89.0 6
02 02 89.0 6
02 03 98.0 6
03 03 98.0 6
04 03 98.0 6
06 03 98.0 6

Answer 10

太扯淡了,下面算不算----Score重复时保留名次空缺
select a.s#,b.c#,a.score,b.px from 
(select * ,row_number() over(partition by c# order by score desc) as rownum from sc)  as a  ,(
select c#,row_number() over(partition by c# order by score desc) as rownum,rank() over(partition by c# order by score desc)  as px
from sc ) as b
where a.c#=b.c# and a.rownum=b.rownum
--Score重复时合并名次
select a.s#,b.c#,a.score,b.px from 
(select * ,row_number() over(partition by c# order by score desc) as rownum from sc)  as a  ,(
select c#,row_number() over(partition by c# order by score desc) as rownum,dense_rank() over(partition by c# order by score desc)  as px
from sc ) as b
where a.c#=b.c# and a.rownum=b.rownum

Answer 11

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

Answer 12

你的结果是:a.s#   a.c#   c.score   b.px
02 01 80.0 6
04 01 80.0 6
05 01 80.0 6
06 01 80.0 6
05 02 90.0 6
07 02 90.0 6
04 02 90.0 6
03 02 90.0 6
02 02 90.0 6
02 03 99.0 6
03 03 99.0 6
04 03 99.0 6
07 03 99.0 6
06 03 99.0 6
04 01 70.0 6
06 01 70.0 6
04 02 60.0 6
04 03 80.0 6
06 03 80.0 6
02 01 80.0 6
04 01 80.0 6
05 01 80.0 6
06 01 80.0 6
04 02 80.0 6
02 02 80.0 6
04 03 80.0 6
06 03 80.0 6
06 01 50.0 6
02 01 76.0 6
04 01 76.0 6
06 01 76.0 6
04 02 87.0 6
03 02 87.0 6
02 02 87.0 6
04 03 34.0 6
05 02 89.0 6
04 02 89.0 6
03 02 89.0 6
02 02 89.0 6
02 03 98.0 6
03 03 98.0 6
04 03 98.0 6
06 03 98.0 6

Answer 13

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