关于同列分数比较问题

kz55766880 2011-09-21 10:40:30



-- 要查询出列C# 01课程比02课程高信息

-- 表

/***

S#       C#       SCORE

01	01	80.0

01	02	90.0

01	03	99.0

02	01	70.0

02	02	60.0

02	03	80.0

03	01	80.0

03	02	80.0

03	03	80.0

04	01	50.0

04	02	30.0

04	03	20.0

05	01	76.0

05	02	87.0

06	01	31.0

06	03	34.0

07	02	89.0

07	03	98.0



***/



--想要输出的结果

/**

S#       C#       SCORE

02	01	70.0

02	02	60.0

02	03	80.0

04	01	50.0

04	02	30.0

04	03	20.0

........

**/

-- 表

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

...全文

107 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

mycodeis0000 2011-09-22

打赏
举报



(中國風)大大

你的SQL是不是有点问题呢



SELECT * FROM SC AS a

WHERE NOT EXISTS(SELECT 1 FROM SC AS b WHERE  S#='01' AND NOT EXISTS(SELECT 1 FROM SC WHERE S#=a.S# AND C#=b.C# AND score<b.score)

)



--用这个测试数据，结果好像不对

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' , 60)

    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

mycodeis0000 2011-09-22

打赏
举报





select * from SC where S# in(select b.S# from (select * from SC where C#='01') a ,

(select * from SC where C#='02') b where a.score>b.score)

gw6328 2011-09-21

打赏
举报



    select * from SC where S# in(

		select S# from SC a where C#='01' and exists(select 1 from Sc b where a.s#=b.s# and a.score >b.score and b.C#='02') 

    )

    

    /*

    S#         C#         score

---------- ---------- ---------------------------------------

02         01         70.0

02         02         60.0

02         03         80.0

04         01         50.0

04         02         30.0

04         03         20.0



    */

中国风 2011-09-21

打赏
举报

這樣寫比較高效

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



SELECT *

FROM SC AS a 

WHERE  

NOT EXISTS(SELECT 1 FROM SC AS b WHERE S#='01' AND  NOT EXISTS(SELECT 1 FROM SC WHERE S#=a.S# AND C#=b.C# AND score<b.score)

)

/*

S#	C#	score

02	01	70.0

02	02	60.0

02	03	80.0

04	01	50.0

04	02	30.0

04	03	20.0

*/

kz55766880 2011-09-21

打赏
举报



/**

问题补充

查询出查询"01"课程分数比"02"课程分数成绩高的信息



S#为学生列     C#为课程列     SCORE为分数列



**/

--小F-- 2011-09-21

打赏
举报

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



select

   a.*

from

   (select * from SC where c#='01')a,

   (select * from SC where c#='02')b

 where

   a.score>b.score



drop table SC



/*(1 行受影响)

S#         C#         score

---------- ---------- ---------------------------------------

01         01         80.0

02         01         70.0

03         01         80.0

05         01         76.0

01         01         80.0

03         01         80.0

01         01         80.0

02         01         70.0

03         01         80.0

04         01         50.0

05         01         76.0

06         01         31.0



(12 行受影响)

*/