没明白你得需求
对于SNO=1 AND CLASSNO=1,name有张帅,对的,两个值,你想怎么显示?
Sno+ClassNo不重复
@天空10047:
select a.* from tb a join
(select sno,classno,count(1) from tb
group by sno,classno having count(1)>1) b
on a.sno=b.sno and a.classno=b.classno
@我是大菠萝: 实践检验
@天空10047:
select a.* from tb a join
(select sno,classno,count(1) cnt from tb
group by sno,classno having count(1)>1) b
on a.sno=b.sno and a.classno=b.classno
count(1)这里少个别名
二楼正解,2005以上版本还可以用开窗函数的方法实现:
select sno, name, classno from
(
select * , count(name) over(partition by sno,classno) as counter from tb
) t
where counter = 1
实践
@天空10047: 你的意思是有重复的sno+classno的记录要取第一条?
select sno, name, classno from
(
select * ,row_number() over(partition by sno,classno order by sno,classno) as num from tb
) t
where num = 1