在同一個 id 下同時顯示 null 和 not null 但不像 XOR 邏輯那樣同時顯示 null 或 not null

考慮下Employee表：

在此表中，我只需要選擇同時具有 null 和 not null 標記的 id，並且它不應該是 not null 或兩者都是 null in col1。

count(column)``count(*)如果 NULL 存在，則會有所不同：

SELECT ID, Col1 
FROM #TestData
WHERE id IN 
(
  SELECT id
  FROM #TestData
  GROUP BY id
  HAVING COUNT(*) > COUNT(Col1) -- at least one NULL 
     AND COUNT(Col1) > 0        -- at least one NOT NULL
)

大多數 DBMS 支持分析函式，如果數據是更複雜查詢的結果，這可能會更有效，因為它避免了兩次訪問同一個表：

SELECT ID, Col1
FROM 
(
  SELECT ID, Col1,
     COUNT(Col1) OVER (PARTITION BY ID) AS cntNotNull,
     COUNT(*) OVER (PARTITION BY ID) AS cntNull
  FROM #TestData AS t
) AS dt
WHERE cntNull > cntNotNull
 AND cntNotNull > 0

select * from table 
where id in ( select id from table where col1 is     null 
             intersect 
             select id from table where col1 is not null 
           )

select t1.* from table t1
where exists ( select 1 from table where id = t1.id and col1 is     null )    
 and exists ( select 1 from table where id = t1.id and col1 is not null ) 

引用自：https://dba.stackexchange.com/questions/137642