sql server 写法
select sno from sc where c# in(select c# from sc from sc where sno='s001')
group by sno having count(*)=(select count(*) from sc where sno='s001')
select sno from sc where c# in(select c# from sc from sc where sno='s001')
group by sno having count(*)=(select count(*) from sc where sno='s001')
begin
drop table sc
end
else
create table sc
(
sno char(20),
cno char(20),
score float
)
insert into sc values('s001', 'c001', 78.9)
insert into sc values('s001','c002', 82.9)
insert into sc values('s001','c003', 59
)
insert into sc values('s002', 'c001', 80.9)
insert into sc values('s002', 'c002', 72.9
)
insert into sc values('s002', 'c003', 80
)
insert into sc values('s003', 'c002', 81.9
)
insert into sc values('s004', 'c001', 60.9
)
insert into sc values('s002','c004',99.0)
insert into sc values('s005','c001',88.9)
insert into sc values('s005','c002',88.9)
insert into sc values('s005','c003',88.9)select * from sc
select sno from sc where cno in(select cno from sc)
group by sno having count(*)=
(select count(*) from sc where sno='s001')
去掉 where sno='s001'
2. 根据“与s001选修课数数量”相同来寻找,这也不对。还要每科名称一样才叫“完全相同”啊求正解
select * from SCORE where SNO in(
select sno from SCORE where sno<>1
group by SNO
having COUNT(*)=(select COUNT(*) from SCORE where SNO=1))) s on t.CNO=s.CNO) temp group by ss having COUNT(tc)=3
完全菜鸟 但是功能实现了,希望帮助能帮助你
from (select xh,wm_concat(kc) kc from a group by xh) a
where a.kc = (select wm_concat(kc) from a where xh = 's001' group by xh);
SELECT a.sno
FROM sc a,sc b
WHERE a.sno <> 's001'
AND b.sno = 's001'
AND a.cno = b.cno
GROUP BY a.sno
HAVING COUNT(*) = (SELECT COUNT(*)
FROM sc c
WHERE c.sno = 's001')
and b.n = (select count(1) from cs where sno='10000' )
and b.n = (select count(1) from cs where sno='10000' )
and b.n< (select count(1) from cs c where b.sno=c.sno)还要加一个条件..
select * from (select sno sno,count(1) n from cs a group by a.sno) b where b.sno !='10000'
and b.n = (select count(1) from cs where sno='10000' )
and b.n>= (select count(1) from cs c where b.sno=c.sno)
后面是>=
(select sno from sc where cno in (select cno from sc where sno = 's001')
group by sno having count(*) = (select count(*) from sc where sno = 's001')) sc1
where sc1.sno <>
(select sno from sc where cno not in (select cno from sc where sno = 's001'))
and sc1.sno <> 's001';
FROM student x,
(SELECT t.sno, wm_concat(t.cno) tt
FROM (select * from score order by cno)t
group by t.sno
) h1,
(SELECT sno,wm_concat(cno) tt FROM score where sno = 's001' group by sno) h2
where h1.tt = h2.tt
and x.sno = h1.sno
and x.sno <> 's001'
s001 c001 78.9
s001 c002 82.9
s001 c003 59
s002 c001 80.9
s002 c002 72.9
s002 c003 80
s003 c002 81.9
s002 c004 99
s005 c001 88.9
s005 c002 88.9
s005 c003 88.9
-----------------------------------------------------------------------
select * from (
select t.cno, WMSYS.WM_CONCAT(t.sno) TIME From sc t
GROUP BY t.CNO) ab
where substr(ab.TIME,1,4)='s001'CNO, TIME
c001 s001 ,s005,s002
c002 s001 ,s005 ,s003,s002
c003 s001,s005,s002
WITH sno_cno(sno, cnos) AS (
SELECT SNO, LISTAGG(CNO) WITHIN GROUP(ORDER BY CNO) cnos
FROM sc
GROUP BY sno
)select sno,cnos from sno_cno where cnos=
(
select cnos FROM sno_cno where sno='s001'
);
where wm =(select wm_concat(course) from student where sno='s01') ,考虑了cno为空的情况
select distinct sno from sc where cno in (
select cno from sc where sno='s001'
) and sno<>'s001' 亲 ,试一下,好使的话,给个回复就行
minus
(
select* from sc_test
minus
select * from sc_test where sno='s001'
)select sc.sno, count(sc.cno)
from sc_test sc
where sc.cno in (select cno from sc_test where sno = 's001')
group by sc.sno
having count(sc.cno) >
(select count(cno) from sc_test where sno = 's001')