求一条SQL语句

有表A:
a b c
1 33 kk
2 33 kk
3 33 mm
4 55 mm
5 66 kk
6 66 ff

要得到以下结果：
a b c
3 33 mm
5 66 kk
6 66 ff

即查出b列相同而c列不同的记录

SQL code

WITH a AS
     (SELECT 1 a, 33 b, 'kk' c
        FROM DUAL
      UNION ALL
      SELECT 2, 33, 'kk'
        FROM DUAL
      UNION ALL
      SELECT 3, 33, 'mm'
        FROM DUAL
      UNION ALL
      SELECT 4, 55, 'mm'
        FROM DUAL
      UNION ALL
      SELECT 5, 66, 'kk'
        FROM DUAL
      UNION ALL
      SELECT 6, 66, 'ff'
        FROM DUAL)
SELECT a, b, c
  FROM (SELECT a, b, c, COUNT (1) OVER (PARTITION BY b) b_count,
               COUNT (1) OVER (PARTITION BY c, b) c_count
          FROM a)
 WHERE c_count = 1 AND b_count > 1

SQL code

这个需求比较奇怪,按理说,应该B和C如果一样,为什么33 kk
不显示呢?

select distinct a1.a, a1.b, a1.c
from A a1 inner join A a2 on a1.b = a2.b
where a1.c <> a2.c

SQL code

Select distinct 
a1.a, 
a1.b, 
a1.c
from A a1 inner join A a2 on a1.b = a2.b

where a1.c <> a2.c

正解

经检测一楼 tx2730 得出的结果符合楼主要求。