studentID classID
1 1
1 2
2 1
3 2
4 1
4 2
4 3
studentID(学号,int) classID(选课号,int)
我现在想查出classID为1和2都选了的学生的学号,答案应该是:
studentID
1
4
要求输入参数是字符窜,多个用逗号隔开,次列子的输入为‘1,2’
求大家帮忙看看怎么写,多谢了!
1 1
1 2
2 1
3 2
4 1
4 2
4 3
studentID(学号,int) classID(选课号,int)
我现在想查出classID为1和2都选了的学生的学号,答案应该是:
studentID
1
4
要求输入参数是字符窜,多个用逗号隔开,次列子的输入为‘1,2’
求大家帮忙看看怎么写,多谢了!
set @para='1,2'
declare @sql varchar(8000)select @sql='
select distinct studentID
from tablename
where classID in ('+@para+')
group by studentID
having count(*)='+cast(len(@para)-len(replace(@para,',',''))+1 as varchar(20))
exec (@sql)
@s varchar(1000)
as
begin
select studentid from tb
where classid in(1,2)
group by studentid
having count(classid)=len(replace(''+@s+'',',',''))
endexec sp_tb '1,2'
@s varchar(1000)
as
begin
select studentid from tb
where charindex(','+rtrim(classid)+',',','+@s+',')>0
group by studentid
having count(classid)=len(replace(''+@s+'',',',''))
endexec sp_tb '1,2'
写成where classid in(@s)呢