这个试试看~~~select * from [tablename] where Sno not in ( select Sno from [tablename] a left join tablename b on a.Cno=b.Cno where a.Sno='06002' and b.Cno is null )
select distinct Sno from tablename where Cno in ( (select Cno from tablename where Cno='06002') ?
刚才少了个a.select * from [tablename] where Sno not in ( select a.Sno from [tablename] a left join [tablename] b on a.Cno=b.Cno where a.Sno='06002' and b.Cno is null )
select sno from Tb a join ( select cno from tb where sno ='06002' ) b on a.cno = b.cno
declare @a table (XS varchar(10),KM int) insert @a select '学生A',1 union all select '学生A',2 union all select '学生B',2 union all select '学生C',1 union all select '学生C',2 union all select '学生C',3 union all select '学生D',1 union all select '学生D',3 union all select '学生E',1 union all select '学生E',2 union all select '学生E',4select * from @a where xs in (select xs from @a a where km in (select km from @a where xs='学生A') and xs!='学生A'group by xs having count(km)>1 )(所影响的行数为 11 行)XS KM ---------- ----------- 学生C 1 学生C 2 学生C 3 学生E 1 学生E 2 学生E 4(所影响的行数为 6 行)
xiao ku 你的不对,数据多抽了
XS 为学生列,KM为科目列。加多个条件就行了select * from @a where xs in (select xs from @a a where km in (select km from @a where xs='学生A') and xs!='学生A'group by xs having count(km)!<(select count(*)from @a where xs='学生A' ))(所影响的行数为 11 行)XS KM ---------- ----------- 学生C 1 学生C 2 学生C 3 学生E 1 学生E 2 学生E 4(所影响的行数为 6 行)
and xs!='学生A'不要这个条件,就是选择全部(包含"学生A")
select sno from Tb a join ( select cno from tb where sno ='06002' ) b on a.cno = b.cno having count(a.cno)>=( select count(cno) from tb where sno ='06002') group by sno
select distinct sno from A aa inner join ( select distict cno from A) on B aa.cno=b.cno
select distinct sno from A aa inner join ( select distict cno from A where sno='06002') on B aa.cno=b.cno
select distinct sno from A aa inner join ( select cno from A where sno='06002) B on aa.cno=b.cno最后的对了
select distinct sno from A aa inner join ( select cno from A where sno='06002) B on aa.cno=b.cno最后的对了------ 根我这句海不是一样的错 select sno from Tb a join ( select cno from tb where sno ='06002' ) b on a.cno = b.cno
SELECT c.Sno FROM (SELECT a.cno, b.Sno FROM (SELECT cno tblname WHERE Sno = '06002') AS a, (SELECT DISTINCT sno FROM tblname )AS b) AS c LEFT JOIN tbalname AS d ON c.Cno = d .Cno WHERE NOT (d .Cno IS NULL)
select a.sno from ( select a.sno, 1 from tb a where exists(select 1 from tb b where sno='06002' and b.cno=a.cno ) and a.sno<>'06002' ) a where group by a.sno having(count(1))= (select count(1) from tb where sno='06002')
少加了一个条件SELECT c.Sno FROM (SELECT a.cno, b.Sno FROM (SELECT cno tblname WHERE Sno = '06002') AS a, (SELECT DISTINCT sno FROM tblname )AS b) AS c LEFT JOIN tbalname AS d ON c.Sno=d.Sno and c.Cno = d .Cno WHERE NOT (d.Cno IS NULL)
select sno,count(*) from tbl a inner join (select cno from tbl where sno='06002') b on a.cno=b.cno group by sno having count(*)=(select count(*) from tbl where sno='06002')不知道可不可以 跟着感觉写的
hehe ...人都没有了 谁叫这边光棍多
用SQL能弄出来吗?? 怀疑。。
表名:COURSE 学号:SID 课程号:COURSEID
SELECT T1.SID FROM COURSE T1 RIGHT JOIN (SELECT * FROM COURSE WHERE SID='002') T2 ON T1.COURSEID=T2.COURSEID GROUP BY T1.SID HAVING COUNT(T1.SID)=(SELECT COUNT(COURSEID) FROM COURSE WHETE SID='002') AND T1.SID<>'002'sql 能实现的,终于写了一个能实现功能。
select d.sno from ( select c.sno,count(*) as cnum from( select a.cno as scno, b.* from (select d.cno from student d where d.sno = '06002')a, student b where a.cno = b.cno and b.sno !='06002')c group by c.sno )d, (select count(*) as cnum from student d where d.sno = '06002')e where d.cnum >= e.cnum 测试通过 不容易啊
第一步,获取06002所有的课程(设表为a) , select cno from a where sno = '06002'(设这个为b) 第二步,组织一张表(设人员为表c),这张表是所有人对这些课程的列表 select c.sno, b.cno from c full outer join b (设为d) 第三步 d left join a,这样如果其中一个课程某人没有,将获得一个null字段 select d.sno, d.cno, a.cno as no2 from d left join a on d.sno = a.sno and d.cno = a.cno (设为e) 第四步取到没有课程的sno select d.sno from e where no2 = null(设为f) 第五步,淘汰掉这些sno select * from c where sno not in f然后把这些sql拼起来......
caipi267202(皮儿) ( ) 信誉:100 Blog 2006-10-20 16:09:05 得分: 0
表名:COURSE 学号:SID 课程号:COURSEID
SELECT T1.SID FROM COURSE T1 RIGHT JOIN (SELECT * FROM COURSE WHERE SID='002') T2 ON T1.COURSEID=T2.COURSEID GROUP BY T1.SID HAVING COUNT(T1.SID)=(SELECT COUNT(COURSEID) FROM COURSE WHETE SID='002') AND T1.SID<>'002'sql 能实现的,终于写了一个能实现功能。
Top caipi267202(皮儿) ( ) 信誉:100 Blog 2006-10-20 16:10:17 得分: 0
还是要谢谢 大家的帮忙,集众人的智慧完成任务!
Top -------------------------------------------------------------------------- 上面所有使用count()取和目标个数相同都存在一个问题,假设目标有1,2,3科目 那像有1,4,5; 2,3,6; ... 等学号也会统计进去,这样就多了。
select distinct(sno) from course where sno not in (select distinct(sno) from (select d.sno, d.cno, course.cno as no2 from (select a.sno, b.cno from (select distinct(sno) from course) a ,(select sno, cno from course where sno = '06002') b) d left join course on d.sno = course.sno and d.cno = course.cno) e where no2 is null)and sno <> '06002'
where Sno in (
(select Sno from tablename
where Cno='06002')?
where Sno in (
(select Sno from tablename group by Cno
where Cno='06002')
where exists(select 1 from tb b where b.cno=a.cno and b.sno ='06002')
Sno Cno Crade
06001 1 92
06001 2 85
06001 3 88
06002 2 90
06002 3 80
06003 1 74
06003 3 85
(
select Sno from [tablename] a left join tablename b on a.Cno=b.Cno where a.Sno='06002' and b.Cno is null
)
where Cno in (
(select Cno from tablename
where Cno='06002')
?
(
select a.Sno from [tablename] a left join [tablename] b on a.Cno=b.Cno
where a.Sno='06002' and b.Cno is null
)
from Tb a join
(
select cno
from tb
where sno ='06002'
) b on a.cno = b.cno
insert @a
select '学生A',1 union all
select '学生A',2 union all
select '学生B',2 union all
select '学生C',1 union all
select '学生C',2 union all
select '学生C',3 union all
select '学生D',1 union all
select '学生D',3 union all
select '学生E',1 union all
select '学生E',2 union all
select '学生E',4select * from @a where xs in
(select xs
from @a a where
km in (select km from @a where xs='学生A')
and xs!='学生A'group by xs having count(km)>1 )(所影响的行数为 11 行)XS KM
---------- -----------
学生C 1
学生C 2
学生C 3
学生E 1
学生E 2
学生E 4(所影响的行数为 6 行)
你的不对,数据多抽了
(select xs
from @a a where
km in (select km from @a where xs='学生A')
and xs!='学生A'group by xs
having count(km)!<(select count(*)from @a where xs='学生A' ))(所影响的行数为 11 行)XS KM
---------- -----------
学生C 1
学生C 2
学生C 3
学生E 1
学生E 2
学生E 4(所影响的行数为 6 行)
from Tb a join
(
select cno
from tb
where sno ='06002'
) b on a.cno = b.cno
having count(a.cno)>=( select count(cno) from tb where sno ='06002')
group by sno
根我这句海不是一样的错
select sno
from Tb a join
(
select cno
from tb
where sno ='06002'
) b on a.cno = b.cno
FROM (SELECT a.cno, b.Sno
FROM (SELECT cno tblname
WHERE Sno = '06002') AS a,
(SELECT DISTINCT sno
FROM tblname )AS b) AS c LEFT JOIN
tbalname AS d ON c.Cno = d .Cno
WHERE NOT (d .Cno IS NULL)
select a.sno, 1 from tb a where exists(select 1 from tb b where sno='06002' and b.cno=a.cno ) and a.sno<>'06002'
) a where group by a.sno having(count(1))= (select count(1) from tb where sno='06002')
FROM (SELECT a.cno, b.Sno
FROM (SELECT cno tblname
WHERE Sno = '06002') AS a,
(SELECT DISTINCT sno
FROM tblname )AS b) AS c LEFT JOIN
tbalname AS d ON c.Sno=d.Sno and c.Cno = d .Cno
WHERE NOT (d.Cno IS NULL)
group by sno
having count(*)=(select count(*) from tbl where sno='06002')不知道可不可以 跟着感觉写的
谁叫这边光棍多
怀疑。。
学号:SID
课程号:COURSEID
SELECT T1.SID FROM COURSE T1
RIGHT JOIN
(SELECT * FROM COURSE WHERE SID='002') T2
ON T1.COURSEID=T2.COURSEID
GROUP BY T1.SID
HAVING COUNT(T1.SID)=(SELECT COUNT(COURSEID) FROM COURSE WHETE SID='002')
AND T1.SID<>'002'sql 能实现的,终于写了一个能实现功能。
from
(
select c.sno,count(*) as cnum
from(
select a.cno as scno, b.*
from
(select d.cno
from student d
where d.sno = '06002')a,
student b
where a.cno = b.cno
and b.sno !='06002')c
group by c.sno
)d,
(select count(*) as cnum
from student d
where d.sno = '06002')e
where d.cnum >= e.cnum
测试通过
不容易啊
select cno from a where sno = '06002'(设这个为b)
第二步,组织一张表(设人员为表c),这张表是所有人对这些课程的列表
select c.sno, b.cno from c full outer join b (设为d)
第三步 d left join a,这样如果其中一个课程某人没有,将获得一个null字段
select d.sno, d.cno, a.cno as no2 from d left join a on d.sno = a.sno and d.cno = a.cno (设为e)
第四步取到没有课程的sno
select d.sno from e where no2 = null(设为f)
第五步,淘汰掉这些sno
select * from c where sno not in f然后把这些sql拼起来......
表名:COURSE
学号:SID
课程号:COURSEID
SELECT T1.SID FROM COURSE T1
RIGHT JOIN
(SELECT * FROM COURSE WHERE SID='002') T2
ON T1.COURSEID=T2.COURSEID
GROUP BY T1.SID
HAVING COUNT(T1.SID)=(SELECT COUNT(COURSEID) FROM COURSE WHETE SID='002')
AND T1.SID<>'002'sql 能实现的,终于写了一个能实现功能。
Top
caipi267202(皮儿) ( ) 信誉:100 Blog 2006-10-20 16:10:17 得分: 0
还是要谢谢 大家的帮忙,集众人的智慧完成任务!
Top
--------------------------------------------------------------------------
上面所有使用count()取和目标个数相同都存在一个问题,假设目标有1,2,3科目
那像有1,4,5;
2,3,6;
...
等学号也会统计进去,这样就多了。
Sno Cno Crade
06001 1 92
06001 2 85
06001 3 88
06002 2 90
06002 3 80
06003 1 74
06003 3 85
------------------------------
像上面的例子,学号06003也在其中了,但06003只有一个3,没有2.
(select a.sno, b.cno from (select distinct(sno) from course) a
,(select sno, cno from course where sno = '06002') b) d left join course on d.sno = course.sno
and d.cno = course.cno) e where no2 is null)and sno <> '06002'