SQL语句,求解答

[TBNoO]

--1.学生表
Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表 
Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号
--3.教师表 
Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名
--4.成绩表 
SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数
*/
--创建测试数据
create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')
insert into Student values('04' , N'李云' , '1990-08-06' , N'男')
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')
create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
insert into Course values('01' , N'语文' , '02')
insert into Course values('02' , N'数学' , '01')
insert into Course values('03' , N'英语' , '03')
create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values('01' , N'张三')
insert into Teacher values('02' , N'李四')
insert into Teacher values('03' , N'王五')
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
--求:查询和"01"号的同学学习的课程完全相同的其他同学的信息 的正解

[--小F--]

http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html?89116

[TBNoO]

你给的网址我看过了，而这题的解答是错误的。

[Quote=引用 2 楼 fredrickhu 的回复:]
http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html?89116
[/Quote]

[TBNoO]

正解的结果和2楼参考的结果是一样的，但语句不对。
[Quote=引用 1 楼 qianjin036a 的回复:]
正解结果是什么?
[/Quote]

[-晴天]

create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')
insert into Student values('04' , N'李云' , '1990-08-06' , N'男')
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')
create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
insert into Course values('01' , N'语文' , '02')
insert into Course values('02' , N'数学' , '01')
insert into Course values('03' , N'英语' , '03')
create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values('01' , N'张三')
insert into Teacher values('02' , N'李四')
insert into Teacher values('03' , N'王五')
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)
insert into SC values('07' , '04' , 98)

go
--求:查询和"01"号的同学学习的课程完全相同的其他同学的信息 的正解
select a.* 
from student a inner join(
select s# 
from sc a 
where s#<>'01' 
and not exists(select 1 from sc b where s#=a.s# and c# not in(select c# from sc where s#='01'))
group by s#
having count(*)=(select count(*) from sc where s#='01')
)b on a.s#=b.s#
/*
S#         Sname      Sage                    Ssex
---------- ---------- ----------------------- ----------
02         钱电         1990-12-21 00:00:00.000 男
03         孙风         1990-05-20 00:00:00.000 男
04         李云         1990-08-06 00:00:00.000 男

(3 行受影响)

*/
go
drop table sc,teacher,Student,course

[-晴天]

上面在SC表里添加了一个01没有的课程以验证.
貌似还应该有其他的解法.

[-晴天]

create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')
insert into Student values('04' , N'李云' , '1990-08-06' , N'男')
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')
create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
insert into Course values('01' , N'语文' , '02')
insert into Course values('02' , N'数学' , '01')
insert into Course values('03' , N'英语' , '03')
create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values('01' , N'张三')
insert into Teacher values('02' , N'李四')
insert into Teacher values('03' , N'王五')
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)
insert into SC values('07' , '04' , 98)

go
--求:查询和"01"号的同学学习的课程完全相同的其他同学的信息 的正解
select a.* 
from student a inner join(
select a.s# 
from sc a inner join sc b on a.c#=b.c# and a.s#<>'01' and b.s#='01'
group by a.s#
having count(*)=(select count(*) from sc where s#='01')
)b on a.s#=b.s#
/*
S#         Sname      Sage                    Ssex
---------- ---------- ----------------------- ----------
02         钱电         1990-12-21 00:00:00.000 男
03         孙风         1990-05-20 00:00:00.000 男
04         李云         1990-08-06 00:00:00.000 男

(3 行受影响)

*/
go
drop table sc,teacher,Student,course

[pengxuan]

if object_id('student','U') is not null
   drop table student
go
--创建测试数据
create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')
insert into Student values('04' , N'李云' , '1990-08-06' , N'男')
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')
go
if object_id('course','u') is not null
   drop table course
go
create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
insert into Course values('01' , N'语文' , '02')
insert into Course values('02' , N'数学' , '01')
insert into Course values('03' , N'英语' , '03')
go
if object_id('teacher','u') is not null
   drop table teacher
go
create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values('01' , N'张三')
insert into Teacher values('02' , N'李四')
insert into Teacher values('03' , N'王五')
go
if object_id('sc','U') is not null
   drop table sc
go
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 student where S# in(
select S# from sc where C# in (select C# from SC where S#='01') and S#<>'01' group by S# having count(*)=(select count(*) from SC where S#='01'))
/*
S#         Sname      Sage                    Ssex
---------- ---------- ----------------------- ----------
02         钱电         1990-12-21 00:00:00.000 男
03         孙风         1990-05-20 00:00:00.000 男
04         李云         1990-08-06 00:00:00.000 男

(3 行受影响)
*/

[开着拖拉机泡妞]

select * from Student where S# in
(select distinct SC.S# from SC where S# <> '01' and SC.C#
in (select distinct C# from SC where S# = '01')
group by
SC.S#
having
count(1) = (select count(1) from SC where S#='01'))

[TBNoO]

里面的解法不能用in进行过滤,因为先过滤再按个数分组是不正确的.

[Quote=引用 5 楼 qianjin036a 的回复:]
select a.*
from student a inner join(
select s#
from sc a
where s#<>'01'
and not exists(select 1 from sc b where s#=a.s# and c# not in(select c# from sc where s#='01'))
group by s#
having count(*)=(select count(*) from sc where s#='01')
)b on a.s#=b.s#
[/Quote]

[TBNoO]

不对哈.[Quote=引用 8 楼 pengxuan 的回复:]
SQL code

if object_id('student','U') is not null
drop table student
go
--创建测试数据
create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student v……
[/Quote]

[TBNoO]

谢谢回复,可惜不是正解.

[Quote=引用 9 楼 travylee 的回复:]
select * from Student where S# in
(select distinct SC.S# from SC where S# <> '01' and SC.C#
in (select distinct C# from SC where S# = '01')
group by
SC.S#
having
count(1) = (select count(1)……
[/Quote]

[-晴天]

[Quote=引用 10 楼 badyflf 的回复:]

里面的解法不能用in进行过滤,因为先过滤再按个数分组是不正确的.

引用 5 楼 qianjin036a 的回复:
select a.*
from student a inner join(
select s#
from sc a
where s#<>'01'
and not exists(select 1 from sc b where s#=a.s# and c# not i……
[/Quote]

你没理解我的查询逻辑:
用 select 语句查询 01 所学过的 c#,
用子查询查找不存在 a.s# 学的课程不在 01 学的课程中的情况,即 a.s# 学的课程,都在 01 所学的课程号中.
再找出课程数相同的,就是了.

因此用 in.
你可以用各种数据去检验,包正确.

[TBNoO]

我是验证过后才发贴的，呵呵。
不应该说是有其他解法，而本来的解法就是错误的了，现在在求正解~~
[Quote=引用 6 楼 qianjin036a 的回复:]
上面在SC表里添加了一个01没有的课程以验证.
貌似还应该有其他的解法.
[/Quote]

[-晴天]

[Quote=引用 14 楼 badyflf 的回复:]

我是验证过后才发贴的，呵呵。
不应该说是有其他解法，而本来的解法就是错误的了，现在在求正解~~
引用 6 楼 qianjin036a 的回复:
上面在SC表里添加了一个01没有的课程以验证.
貌似还应该有其他的解法.
[/Quote]

做人不要过于自信,仔细检查一下您发的数据吧.

[TBNoO]

你能否提供下你逻辑的代码哈，我看着有些犯晕了，呵呵。

[Quote=引用 13 楼 qianjin036a 的回复:]
引用 10 楼 badyflf 的回复:

里面的解法不能用in进行过滤,因为先过滤再按个数分组是不正确的.

引用 5 楼 qianjin036a 的回复:
select a.*
from student a inner join(
select s#
from sc a
where s#<>'01'
and not exists(select 1 from sc b whe……
[/Quote]

[TBNoO]

数据是原先贴子的数据，也就是2楼提供的网址。与本人过于自信无关，你也可以验证下。

[Quote=引用 15 楼 qianjin036a 的回复:]

做人不要过于自信,仔细检查一下您发的数据吧.
[/Quote]

[TBNoO]

。。。我原以为你的回复是原先那个贴子的答案，没仔细解读，你自己写的解答是正解了。我14楼说的验证，不是指你的代码，是指原先贴子的解答，希望你不要误解。

[Quote=引用 13 楼 qianjin036a 的回复:]
引用 10 楼 badyflf 的回复:

里面的解法不能用in进行过滤,因为先过滤再按个数分组是不正确的.

[/Quote]

[-晴天]

create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')
insert into Student values('04' , N'李云' , '1990-08-06' , N'男')
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')
create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
insert into Course values('01' , N'语文' , '02')
insert into Course values('02' , N'数学' , '01')
insert into Course values('03' , N'英语' , '03')
insert into Course values('04' , N'历史' , '01')
create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values('01' , N'张三')
insert into Teacher values('02' , N'李四')
insert into Teacher values('03' , N'王五')
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)  --'01'三门课,01,02,03

insert into SC values('02' , '01' , 70)
insert into SC values('02' , '02' , 60)
insert into SC values('02' , '03' , 80)  --'02'三门课,课程号01,02,03 仅他与 01 相同

insert into SC values('03' , '01' , 80)
insert into SC values('03' , '02' , 80)  --'03'只有两门课

insert into SC values('04' , '01' , 50)
insert into SC values('04' , '02' , 30)
insert into SC values('04' , '04' , 20)  --'04'三门课,但课程号与01不同

insert into SC values('05' , '01' , 76)
insert into SC values('05' , '02' , 87)
insert into SC values('05' , '03' , 31)
insert into SC values('05' , '04' , 34)  --'05'四门课
go
--求:查询和"01"号的同学学习的课程完全相同的其他同学的信息 的正解

select a.* 
from student a inner join(
select s# 
from sc a 
where s#<>'01' 
and not exists(select 1 from sc b where s#=a.s# and c# not in(select c# from sc where s#='01'))
group by s#
having count(*)=(select count(*) from sc where s#='01')
)b on a.s#=b.s#
/*
S#         Sname      Sage                    Ssex
---------- ---------- ----------------------- ----------
02         钱电         1990-12-21 00:00:00.000 男

(1 行受影响)
*/
go

select a.* 
from student a inner join(
select a.s#
from sc a inner join sc b on a.c#=b.c# and a.s#<>'01' and b.s#='01'
group by a.s#
having count(*)=(select count(*) from sc where s#='01') and count(*)=(select count(*) from sc where s#=a.s#)
)b on a.s#=b.s#
/*
S#         Sname      Sage                    Ssex
---------- ---------- ----------------------- ----------
02         钱电         1990-12-21 00:00:00.000 男

(1 行受影响)

*/
go

[-晴天]

[Quote=引用 18 楼 badyflf 的回复:]

。。。我原以为你的回复是原先那个贴子的答案，没仔细解读，你自己写的解答是正解了。我14楼说的验证，不是指你的代码，是指原先贴子的解答，希望你不要误解。

引用 13 楼 qianjin036a 的回复:
引用 10 楼 badyflf 的回复:

里面的解法不能用in进行过滤,因为先过滤再按个数分组是不正确的.
[/Quote]


我只回答你的帖子来着,二楼那个网址是什么啊?

刚才开个玩笑,莫计较.

[TBNoO]

二楼是个“带颜色”的网址,你点开不就知道了。

[Quote=引用 20 楼 qianjin036a 的回复:]
我只回答你的帖……
[/Quote]