Oracle Sql NOT NULL xor NULL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5832473/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Sql NOT NULL xor NULL?
提问by John Strickler
I'm selecting a group of records and I want to filter a column in the logic of XOR - IS NOT NULL xor IS NULL.
我正在选择一组记录，我想在 XOR 的逻辑中过滤一列 - IS NOT NULL xor IS NULL。
--basic
SELECT make, model
FROM cars
results
--------
ford taurus
ford (null)
toyota camry
toyota (null)
honda (null)
--Obviously XOR is not a real Oracle operator
--This is what I'm trying to do..
SELECT make, model
FROM cars
WHERE model IS NOT NULL
XOR model IS NULL
results (pulls records where model IS NOT NULL, falling back to NULL if necessary)
--------
ford taurus
toyota camry
honda (null)
Can anyone give me insight on how to achieve the desired result I'm looking for? I'm struggling on this one!
任何人都可以让我了解如何实现我正在寻找的预期结果？我正在为这个而挣扎！
Many thanks!
非常感谢！
回答by Quassnoi
SELECT make, model
FROM (
SELECT c.*,
ROW_NUMBER() OVER (PARTITION BY make ORDER BY model NULLS LAST) AS rn
FROM cars c
)
WHERE NOT (rn > 1 AND model IS NULL)
回答by Klas Lindb?ck
SELECT make, model
FROM cars
WHERE model IS NOT NULL
UNION -- Add makes that don't have any specific model
SELECT make, model
FROM cars
WHERE make NOT IN
(SELECT make
FROM cars
WHERE model IS NOT NULL)
回答by onedaywhen
I initially upvoted Klas Lindb?ck's answer but now I'm wondering whether this instead gives the desired results:
我最初赞成 Klas Lindb?ck 的回答，但现在我想知道这是否会给出预期的结果：
SELECT make, model
FROM Cars
WHERE model IS NOT NULL
UNION
SELECT make, NULL
FROM Cars
MINUS
SELECT make, NULL
FROM cars
WHERE model IS NOT NULL;
