Table1StudNo zhuanye nianji kecheng zhuanyeke
1 CG 一年级 设计 CG专业课
1 CG 一年级 创艺 CG专业课
2 BA 二年级 营销 BA专业课
3 CG 三年级 英语 CG基础课
4 CG 一年级 素描 CG专业课
4 CG 一年级 摄影 CG专业课如何查出不同的学生,符合下面条件:必须nianji为一年级,zhuanye为CG,所选专业课zhuanyeke(为CG专业课)数>=2的不同学生,列表后如下:StudNo zhuanye zhuanyeke
1 CG CG专业课
4 CG CG专业课SQL语句应如何写?
in(select StudNo from tablename group by StudNo having count(*)>=2) and
nianji='一年级' and zhuanye='CG'
in(select StudNo from tablename group by StudNo having count(*)>=2) and
nianji='一年级' and zhuanye='CG'
from table1
where nianji = '一年级' and zhuanye = 'CG' and zhuanyeke = 'CG专业课'
group by StudNo ,zhuanye, zhuanyeke
having count(*) >= 2
insert into tb values(1, 'CG', '一年级', '设计', 'CG专业课')
insert into tb values(1, 'CG', '一年级', '创艺', 'CG专业课')
insert into tb values(2, 'BA', '二年级', '营销', 'BA专业课')
insert into tb values(3, 'CG', '三年级', '英语', 'CG基础课')
insert into tb values(4, 'CG', '一年级', '素描', 'CG专业课')
insert into tb values(4, 'CG', '一年级', '摄影', 'CG专业课')
select StudNo ,zhuanye, zhuanyeke , count(*)
from tb
where nianji = '一年级' and zhuanye = 'CG' and zhuanyeke = 'CG专业课'
group by StudNo ,zhuanye, zhuanyeke
having count(*) >= 2drop table tb/*
StudNo zhuanye zhuanyeke
----------- ---------- ---------- -----------
1 CG CG专业课 2
4 CG CG专业课 2(所影响的行数为 2 行)*/
(
studno int,
zhuanye varchar(50),
nianji varchar(50),
kecheng varchar(50),
zhuanyeke varchar(50)
)insert into @t select 1,'CG','一年级','设计','CG专业课'
union all select 1,'CG','一年级','设计','CG专业课'
union all select 2,'CG','一年级','设计','CG专业课'
union all select 2,'CC','一年级','设计','CC专业课'
union all select 3,'CG','一年级','设计','CG专业课'
union all select 3,'CG','一年级','设计','CG专业课'select StudNo,zhuanye,zhuanyeke from @t t
where t.zhuanye='CG' and t.zhuanyeke='CG专业课' and t.nianji='一年级'
group by StudNo,zhuanye,zhuanyeke
having count(StudNo)>=2
3 CG CG专业课