找学习相同课程的人

oyzhjr · 发表于 2013-12-2 18:32

本帖最后由 oyzhjr 于 2013-12-3 12:38 编辑

如下表为一个学生学习表,bh列为课程代号,xm列为姓名缩写.现要求通过一个查询语句查出所学课程与ztt一致的信息.即返回结果为zhh\ztt的行.
请高人指点, 用=ALL没法得出正确结果.

bh xm
10    zhh
20       yyy
20       zhh
30       gmm
10       ztt
20       ztt

zxb7679 · 发表于 2013-12-3 10:00

先捋一捋你写的话了再，表述的。。。。。。

oyzhjr · 发表于 2013-12-3 12:38

zxb7679 发表于 2013-12-3 10:00
先捋一捋你写的话了再，表述的。。。。。。

谢谢，已经修改。

amazeyeli · 发表于 2013-12-3 13:38

USE tempdb
GO
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST;
GO
CREATE TABLE TEST
(
bh CHAR(2),
xm VARCHAR(8)
);
GO
INSERT INTO TEST VALUES('10','zhh');
INSERT INTO TEST VALUES('20','yyy');
INSERT INTO TEST VALUES('20','zhh');
INSERT INTO TEST VALUES('30','gmm');
INSERT INTO TEST VALUES('10','ztt');
INSERT INTO TEST VALUES('20','ztt');
GO
----------------------------------------
;with a as
(select bh from TEST where xm='ztt'),
b as
(select COUNT(*) as counts from TEST where xm='ztt'),
c as
(select xm from TEST group by xm having COUNT(*)=(select counts from b))
select * from TEST
where bh in (select bh from a)
and xm in (select xm from c);
----------------------------------------
/*
bh xm
---- --------
10 zhh
20 zhh
10 ztt
20 ztt
(4 行受影响)
*/
----------------------------------------

复制代码

oyzhjr · 发表于 2013-12-3 16:44

亲，第20行代码处是不是少了点东西？

amazeyeli · 发表于 2013-12-3 17:59

修改为:

查询a为查询ztt所选课程的代码:
select bh from TEST where xm='ztt'
查询b为查询ztt所选课程的数量:
select COUNT(*) as counts from TEST where xm='ztt'
查询c为查询与ztt所选课程的代码相同并且所选课程的数量相同的同学:
select xm from TEST as d inner join a on a.bh=d.bh
group by xm having count(*)=(select counts from b)
查询所有信息:
select * from TEST where xm in (select xm from c)

复制代码

select * from TEST
where xm in
(
select xm from TEST as d
inner join
(
select bh from TEST where xm='ztt'
) as a
a.bh=d.bh
group by xm
having count(*)=
(
select COUNT(*) as counts from TEST where xm='ztt'
)
)

复制代码

oyzhjr · 发表于 2013-12-3 21:28

楼上代码不错，经测试正常。
但第8行的代码少了个 on 应为 ) as a on
我也想过用连接自身的方法进行查询，select  a.xm, a.bh from test a inner join test b on a.bh=b.bh and a.xm!=b.xm and b.xm='ztt'，但返回的结果包并不正确，yyy只选了其中一门课，仍返回了结果。

楼上的代码美中不足的是无若zhh同时选了10,20,30三门课，但ztt 只选了10,20两门课，执行上述代码时，仍会把zhh选出来，但可以通过增加一个having来筛选出来，如下代码。
select * from test where xm in (
select xm  from test where xm in
(
  select xm from TEST as d
  inner join
  (    select bh from TEST where xm='ztt' ) as a on
a.bh=d.bh
group by xm
having count(*)=
  (    select COUNT(*) as counts from TEST where xm='ztt'  )
)
group by xm
having count(xm)=(select count (xm)  from test e where e.xm='ztt')
)

还是非常感放谢楼上。

amazeyeli · 发表于 2013-12-4 09:13

USE tempdb
GO
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST;
GO
CREATE TABLE TEST
(
bh CHAR(2),
xm VARCHAR(8)
);
GO
INSERT INTO TEST VALUES('10','zhh');
INSERT INTO TEST VALUES('20','zhh');
INSERT INTO TEST VALUES('10','gmm');
INSERT INTO TEST VALUES('20','gmm');
INSERT INTO TEST VALUES('30','gmm');
INSERT INTO TEST VALUES('10','ztt');
INSERT INTO TEST VALUES('20','ztt');
INSERT INTO TEST VALUES('20','yyy');
GO
----------------------------------------
;with
a as
(select bh from TEST where xm='ztt'),
b as
(select COUNT(*) as counts from TEST where xm='ztt'),
c as
(select xm from TEST group by xm having COUNT(*)=(select counts from b)),
d as
(select t.xm from TEST as t join a on t.bh = a.bh join c on t.xm = c.xm group by t.xm having COUNT(*)=(select counts from b))
select * from d
----------------------------------------
/*
xm
--------
zhh
ztt
(2 行受影响)
*/
----------------------------------------

复制代码

修改测试数据,选3门的gmm和1门的yyy都不会出现在结果中了
原理和楼上你的应该差不多

oyzhjr · 发表于 2013-12-4 11:53

楼上的代码是SQL2005以上的吧？

wuxiang_123 · 发表于 2013-12-4 15:17

SELECT B.* FROM
(SELECT BH FROM 表 WHERE XM='ZTT')A,
(SELECT BH,XM FROM 表 WHERE XM<>'ZTT')B,
(SELECT XM,COUNT(BH) AS 课程 FROM 表 GROUP BY XM)C,
(SELECT COUNT(BH) AS 课程 FROM 表 WHERE XM='ZTT')D
WHERE A.BH=B.BH AND B.XM=C.XM AND C.课程=D.课程

复制代码

假设表名称是表.

		自动登录	找回密码
密码			免费注册

[求助] 找学习相同课程的人