这道SQL题能有别的方法解么

蛋疼的淡定哥 发布于 2012/07/13 14:31
阅读 277
收藏 0

表temptable,2个字段tid,tname

tid    tname

-------------

100    001

100    002

101    003

101    003

102    004

102    005

103    006

104    007

要求输出:

tid    tname

-------------

100    001

100    002

102    004

102    005

 

select t.* 
from temptable t 
where t.tid in (select a.tid from temptable a group by a.tid having count(*)>1) 
and t.tid not in (select b.tid from temptable b group by (b.tid, b.tname) having count(*)>1)

加载中
0
hulubo
hulubo
select tid,tname from 
(select b.tid,b.tname,count(distinct tname) over ( partition by tid ) ct from temptable b )where ct>1

oracle sql
0
xiaoyang0878
xiaoyang0878
select b.* from 
(select  a.tid from mypro.`temptable` a group by a.tid having count(a.tid)>1) a,
(select b.tid,b.tname from mypro.`temptable` b group by b.tid, b.tname having count(*)=1) b
where a.tid=b.tid
-- or 
select b.tid,b.tname from mypro.`temptable` b group by b.tid, b.tname having count(*)=1
and b.tid in (select  a.tid from mypro.`temptable` a group by a.tid having count(a.tid)>1)

0
BatM3
BatM3
select * from template where tid in (100,102); 
蛋疼的淡定哥
蛋疼的淡定哥
的确这是最那啥的方法,你猜面试官会怎么想- -!
返回顶部
顶部