今天去面试,得一题目,本人愚钝,百思不得其解.望达人赐教表ASid CId
001 001
001 002
001 003
002 001
002 002
003 003Sid 表示学生编号
Cid 表示课程编号求与SID=002 选了同样课程的学生ID(包含002选课的集合)
再求与SID=002 不同课程的学生ID (不包含002选课的集合)
001 001
001 002
001 003
002 001
002 002
003 003Sid 表示学生编号
Cid 表示课程编号求与SID=002 选了同样课程的学生ID(包含002选课的集合)
再求与SID=002 不同课程的学生ID (不包含002选课的集合)
SELECT *
FROM A AA
WHERE AA.SID <>'002'
AND Cid IN(
SELECT Cid FROM A WHERE SID = '002')
AND (SELECT COUNT(*) FROM A WHERE AA.SID = SID)
>=(SELECT COUNT(*) FROM A WHERE SID = '002')-- 反向选择
SELECT * FROM A
WHERE SID <>'002'
AND SID NOT IN(
SELECT *
FROM A AA
WHERE AA.SID <>'002'
AND Cid IN(
SELECT Cid FROM A WHERE SID = '002')
AND (SELECT COUNT(*) FROM A WHERE AA.SID = SID)
>=(SELECT COUNT(*) FROM A WHERE SID = '002'))
2、select sid from a where cid not in(select cid from a where sid=002) group by sid
INSERT @t SELECT '001', '001'
UNION ALL SELECT '001', '002'
UNION ALL SELECT '001', '003'
UNION ALL SELECT '002', '001'
UNION ALL SELECT '002', '002'
UNION ALL SELECT '003', '003'
UNION ALL SELECT '003', '001'
UNION ALL SELECT '004', '005'
/*
两题题意都比较晦涩,容易让人产生岐义
题一
*/
--只要有一门Cid与002的Cid相同
SELECT * FROM @t WHERE Cid IN(SELECT Cid FROM @t WHERE Sid='002') --当然写法有很多。--002所选的Cid全都要被选取
SELECT * FROM @t a
WHERE NOT EXISTS(
SELECT 1 FROM @t b
WHERE b.Sid='002'
AND b.Cid NOT IN (SELECT Cid FROM @t c WHERE c.Sid=a.Sid)
)/*题二*/
--002选的Cid没有任何一个被选取
SELECT * FROM @t a WHERE NOT EXISTS(SELECT 1 FROM @t b WHERE a.Sid=b.Sid AND b.Cid IN (SELECT Cid FROM @t WHERE Sid='002'))
--与002选取的Cid不完全相同
SELECT * FROM @t a
WHERE EXISTS(
SELECT 1 FROM @t b
WHERE b.Sid='002'
AND b.Cid NOT IN (SELECT Cid FROM @t c WHERE c.Sid=a.Sid)
)
OR
EXISTS
(
SELECT 1 FROM @t b
WHERE b.Sid=a.Sid
AND b.Cid NOT IN (SELECT Cid FROM @t WHERE Sid='002')
)
--与002选取的Cid不完全相同 Cid集合不把002的Cid作为子集的除外SELECT * FROM @t a
WHERE EXISTS(
SELECT 1 FROM @t b
WHERE b.Sid='002'
AND b.Cid NOT IN (SELECT Cid FROM @t c WHERE c.Sid=a.Sid)
)--写法都有很多,只是随手打的,没看语句效率
insert into t select '001','001' union all
select '001','002' union all
select '001','003' union all
select '002','002' union all
select '002','001' union all
select '003','003' union all
select '004','001' union all
select '005','002' union all
select '005','003' union all
select '005','001'
go
create function f_str(@sid varchar(03))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cid from t where sid=@sid order by cid
return stuff(@sql,1,1,'')
end
go
declare @str varchar(100), @sid varchar(04)
set @sid='002'
set @str=''
select @str=@str+'%'+cid from t where sid=@sid order by cidprint '與'+@sid+'選相同的課'
exec('select distinct sid from t where patindex('''+@str+'%'',dbo.f_str(sid))>0 ')
print '與'+@sid+'選不同的課'
exec('select distinct sid from t where patindex('''+@str+'%'',dbo.f_str(sid))=0 ')
go
drop table t
drop function f_str/*result:*/
與002選相同的課
sid
----
001
002
005與002選不同的課
sid
----
003
004
纵像比较是不好比较
横像比较会好点,这样可能有违题意.毕竟在表设计的时候改一下设计就OK
那么SID 为 001 的学生选了 001 002 003 三门课程
那么{001,002,003}的集合包含了{001,002},那么SID,001的就符合要求zjcxc(邹建)的好像也不合题意如果有一个学生004,他的记录
004 001
004 003
004 005那么
004 001 这条记录是满足查询的,但是却不满足题意
FROM A AA
WHERE AA.SID <>'002'
AND Cid IN(SELECT Cid FROM A WHERE SID = '002')
WHERE NOT EXISTS(
SELECT 1 FROM @t b
WHERE b.Sid='002'
AND b.Cid NOT IN (SELECT Cid FROM @t c WHERE c.Sid=a.Sid)
)这条就可以了.
SId=002的学生的Cid中如果有一个Cid不出现在其它某个学生的Cid里,那么就不选取,反过来就是说,
只取
Sid=002的学生的Cid中不存在 某个Cid不出现在其它某个学生的Cid集合 里的情况.
就这个意思.
select a.sid from #tt a ,
(select cid from #tt where sid='002') b
where a.cid=b.cid
group by a.sid
having count(a.cid)=(select count(cid) from #tt where sid='002')
2.
select sid from #tt
where sid not in
(select a.sid from #tt a ,
(select cid from #tt where sid='002') b
where a.cid=b.cid
group by a.sid
having count(a.cid)=(select count(cid) from #tt where sid='002')
)
group by sid
Declare @A table(Sid char(3), CId char(3))
Insert into @a
Select '001','001' union
Select '001','002' union
Select '001','003' union
Select '002','001' union
Select '002','002' union
Select '003','003'
Select distinct Sid from @A a where not exists(
Select * from @A b where b.sid='002' and not exists(
Select * from @A c where c.sid<>'002' and a.sid=c.sid and b.cid=c.cid )
) Select distinct Sid from @A a where not exists(
Select * from @A b where b.sid='002' and exists(
Select * from @A c where a.sid=c.sid and b.cid=c.cid )
)
2、select Sid from A where Cid not in(select Cid from a where sid=002)
应该是:
Select distinct Sid from @A a where exists(
Select * from @A b where b.sid='002' and not exists(
Select * from @A c where a.sid=c.sid and b.cid=c.cid )
)
SELECT sid FROM A WHERE cid IN (SELECT cid FROM A WHERE sid=002)
SELECT sid FROM A WHERE sid<>002 AND cid NOT IN (SELECT cid FROM A WHERE sid=002)
这样不行吗?
SELECT a1.sid FROM A a1,A a2 WHERE a1.cid = a2.cid AND a2.sid=002
SELECT sid FROM A a1,A a2 WHERE a1.sid<>a2.sid AND a2.sid=002 AND a1.cid=a2.cid
drop table cs
go
create table cs(sid varchar(20) not null,cid varchar(20) not null,primary key(sid,cid))
goinsert cs
select '001','001' union all
select '001','002' union all
select '001','003' union all
select '002','001' union all
select '002','002' union all
select '003','003' union all
select '004','001' union all
select '004','002' union all
select '005','001' union all
select '005','003' union all
select '006','001'
goselect * from cs
/*
sid cid
001 001
001 002
001 003
002 001
002 002
003 003
004 001
004 002
005 001
005 003
006 001
*/
select * from (select distinct sid from cs where sid<>'002') a where not exists
(select * from (select cid from cs where sid='002') b where not exists(select * from cs c where a.sid=c.sid and b.cid=cid))
/*
sid
001
004
*/
select * from (select distinct sid from cs where sid<>'002') a where exists
(select * from (select cid from cs where sid='002') b where not exists(select * from cs c where a.sid=c.sid and b.cid=cid))
/*
sid
003
005
006
*/
select Sid from A where Cid in (select Cid from A where Sid='002')
select Sid from A where Cid not in (select Cid from A where Sid='002')
sid cid
005 001
005 001
005 002
005 002SQL的写法和不重复的又不一样了。
INSERT INTO #T
SELECT '001','001' UNION ALL
SELECT '001','003' UNION ALL
SELECT '002','001' UNION ALL
SELECT '002','002' UNION ALL
SELECT '002','003' UNION ALL
SELECT '003','001' UNION ALL
SELECT '003','002' UNION ALL
SELECT '003','003' UNION ALL
SELECT '003','004' UNION ALL
SELECT '004','001' UNION ALL
SELECT '005','002' UNION ALL
SELECT '005','003' UNION ALL
SELECT '006','001' UNION ALL
SELECT '006','002' UNION ALL
SELECT '006','002' UNION ALL
SELECT '006','001' SELECT * FROM #T WHERE Sid IN
(SELECT Sid FROM (
SELECT Sid,COUNT(DISTINCT CID)CO FROM #T WHERE Cid IN
(SELECT Cid FROM #T WHERE Sid = '002') GROUP BY Sid
HAVING COUNT(DISTINCT CID)>(SELECT COUNT(*)-1FROM #T WHERE Sid='002')
) A
)SELECT * FROM #T WHERE NOT Sid IN
(SELECT Sid FROM (
SELECT Sid,COUNT(DISTINCT CID)CO FROM #T WHERE Cid IN
(SELECT Cid FROM #T WHERE Sid = '002') GROUP BY Sid
HAVING COUNT(DISTINCT CID)>(SELECT COUNT(*)-1FROM #T WHERE Sid='002')
) A
)
<一>
SELECT *
FROM @T A
WHERE (SELECT COUNT(1)
FROM @T
WHERE SID = A.SID
AND CID IN (SELECT CID
FROM @T
WHERE SID = '002')) > 1
AND A.SID <> '002'
<二>
SELECT *
FROM @T
WHERE SID NOT IN (SELECT A.SID
FROM @T A
WHERE (SELECT COUNT(1)
FROM @T
WHERE SID = A.SID
AND CID IN (SELECT CID
FROM @T
WHERE SID = '002')) > 1)
INSERT @a SELECT '001', '001'
UNION ALL SELECT '001', '002'
UNION ALL SELECT '001', '003'UNION ALL SELECT '002', '001'
UNION ALL SELECT '002', '002' UNION ALL SELECT '003', '003'
UNION ALL SELECT '003', '001'UNION ALL SELECT '004', '003'
UNION ALL SELECT '005', '002'
-- 包含或者包含并且多于002所选的课程
SELECT distinct sid
FROM @A AA
WHERE AA.SID <>'002'
AND Cid IN(
SELECT Cid FROM @A WHERE SID = '002')-- 反向选择
SELECT * FROM @A
WHERE SID <>'002'
AND SID NOT IN(SELECT distinct sid
FROM @A AA
WHERE AA.SID <>'002'
AND Cid IN(
SELECT Cid FROM @A WHERE SID = '002'))
group by ...
having count(sid)>=2
第二题好像是反向思维。。
。。
INSERT @t SELECT '001', '001'
UNION ALL SELECT '001', '002'
UNION ALL SELECT '001', '003'UNION ALL SELECT '002', '001'
UNION ALL SELECT '002', '002' UNION ALL SELECT '003', '003'
UNION ALL SELECT '003', '001'UNION ALL SELECT '004', '003'
UNION ALL SELECT '005', '002'
-- 包含或者包含并且多于002所选的课程
SELECT distinct sid FROM @t a
WHERE a.sid<>'002' and NOT EXISTS(
SELECT 1 FROM @t b
WHERE b.Sid='002'
AND b.Cid NOT IN (SELECT Cid FROM @t c WHERE c.Sid=a.Sid)
)-- 反向选择
SELECT distinct sid FROM @t where sid not in(
SELECT distinct sid FROM @t a
WHERE NOT EXISTS(
SELECT 1 FROM @t b
WHERE b.Sid='002'
AND b.Cid NOT IN (SELECT Cid FROM @t c WHERE c.Sid=a.Sid)
)
)
INSERT @t SELECT '001', '001'
UNION ALL SELECT '001', '002'
UNION ALL SELECT '001', '003'UNION ALL SELECT '002', '001'
UNION ALL SELECT '002', '002' UNION ALL SELECT '003', '003'
UNION ALL SELECT '003', '001'UNION ALL SELECT '004', '003'
UNION ALL SELECT '005', '002'
-- 包含或者包含并且多于002所选的课程
SELECT distinct sid FROM @t a
WHERE a.sid<>'002' and NOT EXISTS(
SELECT 1 FROM @t b
WHERE b.Sid='002'
AND b.Cid NOT IN (SELECT Cid FROM @t c WHERE c.Sid=a.Sid)
)
-- 反向选择
----1偷懒行为
SELECT distinct sid FROM @t where sid not in(
SELECT distinct sid FROM @t a
WHERE NOT EXISTS(
SELECT 1 FROM @t b
WHERE b.Sid='002'
AND b.Cid NOT IN (SELECT Cid FROM @t c WHERE c.Sid=a.Sid))
)
----2仿照行为(推荐)
SELECT distinct sid FROM @t a
WHERE EXISTS(
SELECT 1 FROM @t b
WHERE b.Sid='002'
AND b.Cid NOT IN (SELECT Cid FROM @t c WHERE c.Sid=a.Sid)
)
INSERT @t SELECT '001', '001'
UNION ALL SELECT '001', '002'
UNION ALL SELECT '001', '003'
UNION ALL SELECT '002', '001'
UNION ALL SELECT '002', '002'
UNION ALL SELECT '003', '003'
UNION ALL SELECT '003', '001'
UNION ALL SELECT '004', '005'select distinct Sid
from @t a , (select Cid as D from @t where Sid='002') b
where b.D=a.Cid
这是相同的,一会儿写不相同的
select sid from a where cid<>(select cid from a where sid='002')
这是可以Select Sysobjects from t where ('a','b') in ('a','b','c')
这句是错误的做题一般是按这个思路来的.也就是先读该记录的集合再把这个集合与'002'的进行比较
当然集合是无法在SQL里面进行比较咯看来我还是太笨了
这是可以Select * from Sysobjects where ('a','b') in ('a','b','c')
这是不行的
更改一下