要求使用自表关联和
exists两种方法实现。题目:
假设表tb的结构以及数据如下:
ID Mid Mname
1 Um001-005 aa
2 UM005-427 bb
3 UM001-005 cc
4 UM007-001 dd
.....查询出所有Mould_id相同,但是Mould_name不同的数据?
结果应该如下类似以下:
ID Mould_id
1 Um001-005 (注:该型号有不同Mould_name)
....
exists两种方法实现。题目:
假设表tb的结构以及数据如下:
ID Mid Mname
1 Um001-005 aa
2 UM005-427 bb
3 UM001-005 cc
4 UM007-001 dd
.....查询出所有Mould_id相同,但是Mould_name不同的数据?
结果应该如下类似以下:
ID Mould_id
1 Um001-005 (注:该型号有不同Mould_name)
....
from tb t1
where exists (select 'x' from tb t2 where t2.mid=t1.mid and t2.mName<>t1.mName)
select a.mid from tb a,tb b
where a.mid=b.mid
and a.mname<>b.mname
group by a.mid
having count(*)>1
t1.id,
t1.Mid
from
tblTest t1
inner join tblTest t2
on
t2.Mid = t1.Mid
and t2.Mname <> t1.Mnamegroup by t1.Mid ,t1.id