查找每个tc_no的结果的非重复计数，然后选择count大于1的记录

• • 查询**

select * from your_tbl_name a
where exists(
  select 1 from (
    select tc_no, count(distinct result) as cnt
    from your_tbl_name 
    where result in ('PASS','FAIL')
    group by c_no
  ) b
  where a.tc_no = b.tc_no
  and b.cnt > 1
)

您可以简单地将表INNER JOIN到其自身上，并在WHERE子句中添加一个 predicate ，以便只返回不匹配的结果。

• • SQL语句：**

SELECT 
  a.ID, 
  a.TC_No, 
  a.Result 
FROM 
  Excel a 
  INNER JOIN Excel b ON a.TC_No = b.TC_No 
WHERE 
  a.Result <> b.Result;

• • 结果：**

| ID | TC_No | Result |
|----|-------|--------|
|  1 |  tc_1 |   PASS |
|  1 |  tc_4 |   PASS |
|  2 |  tc_1 |   FAIL |
|  2 |  tc_4 |   FAIL |

• • SQL Fiddle演示：**此处

使用窗口函数检查过滤数据中的最大结果与最小结果何时不同。

WITH cte AS (
    SELECT tab.*, 
           MAX(Result_) OVER(PARTITION BY TC_No) AS max_result,
           MIN(Result_) OVER(PARTITION BY TC_No) AS min_result
    FROM tab
    WHERE ID IN (1,2)
)
SELECT Id, Tc_No, Result_
FROM cte
WHERE min_result < max_result

检查here演示。

您可以使用分析函数COUNT（）OVER（）来查找具有不同内容的行，然后在Where子句中过滤结果：

SELECT  ID, TC_NO, RESULT
FROM    ( Select  ID, TC_NO, RESULT, 
                  CASE  WHEN Count(DISTINCT RESULT) OVER(Partition By TC_NO) = 2 THEN 'Y' END "IS_DIFF" 
          From    tbl
        )
WHERE IS_DIFF = 'Y'
ORDER BY ID

使用示例数据：

WITH
    tbl (ID, TC_NO, RESULT) AS
        (
            Select 1, 'tc_1', 'PASS' From Dual Union All
            Select 1, 'tc_2', 'PASS' From Dual Union All
            Select 1, 'tc_3', 'FAIL' From Dual Union All
            Select 1, 'tc_4', 'PASS' From Dual Union All
            Select 1, 'tc_5', 'FAIL' From Dual Union All
            Select 2, 'tc_1', 'FAIL' From Dual Union All
            Select 2, 'tc_2', 'PASS' From Dual Union All
            Select 2, 'tc_3', 'FAIL' From Dual Union All
            Select 2, 'tc_4', 'FAIL' From Dual Union All
            Select 2, 'tc_5', 'FAIL' From Dual 
        )

......结果是：
| 识别号|TC_编号|结果|
| - ------|- ------|- ------|
| 1个|tc_1|通过|
| 1个|四氯化钛|通过|
| 第二章|tc_1|未通过|
| 第二章|四氯化钛|未通过|