oracle 找不到元素时，如何打印列值的“NULL”或“0”值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4693407/
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
How do I print out 'NULL' or '0' values for column values when an element isn't found?
提问by Vladimir
I need to loop through a set of values (less than 10) and see if they are in a table. If so, I need to print out all of the record values, but if the item doesn't exist, I still want it to be included in the printed result, although with NULL or 0 values. So, for example, the following query returns:
我需要遍历一组值（小于 10）并查看它们是否在表中。如果是这样，我需要打印出所有记录值，但如果该项目不存在，我仍然希望它包含在打印结果中，尽管值为 NULL 或 0。因此，例如，以下查询返回：
select *
from ACTOR
where ID in (4, 5, 15);
+----+-----------------------------+-------------+----------+------+ | ID | NAME | DESCRIPTION | ORDER_ID | TYPE | +----+-----------------------------+-------------+----------+------+ | 4 | [TEST-1] | | 3 | NULL | | 5 | [TEST-2] | | 4 | NULL | +----+-----------------------------+-------------+----------+------+但我想让它回来
+----+-----------------------------+-------------+----------+------+ | ID | NAME | DESCRIPTION | ORDER_ID | TYPE | +----+-----------------------------+-------------+----------+------+ | 4 | [TEST-1] | | 3 | NULL | | 5 | [TEST-2] | | 4 | NULL | | 15| NULL | | 0 | NULL | +----+-----------------------------+-------------+----------+------+
Is this possible?
这可能吗？
回答by OMG Ponies
To get the output you want, you first have to construct a derived table containing the ACTOR.idvalues you desire. UNION ALL works for small data sets:
要获得您想要的输出，您首先必须构建一个包含ACTOR.id您想要的值的派生表。UNION ALL 适用于小数据集：
SELECT *
FROM (SELECT 4 AS actor_id
FROM DUAL
UNION ALL
SELECT 5
FROM DUAL
UNION ALL
SELECT 15
FROM DUAL) x
With that, you can OUTER JOIN to the actual table to get the results you want:
有了这个，您可以 OUTER JOIN 到实际表以获得您想要的结果：
SELECT x.actor_id,
a.name,
a.description,
a.orderid,
a.type
FROM (SELECT 4 AS actor_id
FROM DUAL
UNION ALL
SELECT 5
FROM DUAL
UNION ALL
SELECT 15
FROM DUAL) x
LEFT JOIN ACTOR a ON a.id = x.actor_id
If there's no match between xand a, the acolumns will be null. So if you want orderid to be zero when there's no match for id 15:
如果x和之间不匹配a，则a列将为空。因此，如果您希望在 id 15 没有匹配项时 orderid 为零：
SELECT x.actor_id,
a.name,
a.description,
COALESCE(a.orderid, 0) AS orderid,
a.type
FROM (SELECT 4 AS actor_id
FROM DUAL
UNION ALL
SELECT 5
FROM DUAL
UNION ALL
SELECT 15
FROM DUAL) x
LEFT JOIN ACTOR a ON a.id = x.actor_id
回答by Matt Gibson
Well, for that few values, you could do something ugly like this, I suppose:
好吧，对于这几个值，我想你可以做一些丑陋的事情：
SELECT
*
FROM
(
SELECT 4 AS id UNION
SELECT 5 UNION
SELECT 15
) ids
LEFT JOIN ACTOR ON ids.id = ACTOR.ID
(That should work in MySQL, I think; for Oracle you'd need to use DUAL, e.g. SELECT 4 as id FROM DUAL...)
（我认为这应该适用于 MySQL；对于 Oracle，您需要使用 DUAL，例如SELECT 4 as id FROM DUAL...）
回答by AndreKR
That is only possible using a temporary table.
这只能使用临时表。
回答by Matt
CREATE TABLE actor_temp (id INTEGER);
INSERT INTO actor_temp VALUES(4);
INSERT INTO actor_temp VALUES(5);
INSERT INTO actor_temp VALUES(15);
select actor_temp.id, ACTOR.* from ACTOR RIGHT JOIN actor_temp on ACTOR.id = actor_temp.id;
DROP TABLE actor_temp;
回答by Jim Hudson
If you know the upper and lower limits on the ID, it's not too bad. Set up a view with all possible ids - the connect by trick is the simplest way - and do an outer join with your real table. Here, I've limited it to values from 1-1000.
如果你知道ID的上下限，那还不错。使用所有可能的 id 设置一个视图 - 通过技巧连接是最简单的方法 - 并与您的真实表进行外部连接。在这里，我将其限制为 1-1000 的值。
select * from (
select ids.id, a.name, a.description, nvl(a.order_id,0), a.type
from Actor a,
(SELECT level as id from dual CONNECT BY LEVEL <= 1000) ids
where ids.id = a.id (+)
)
where id in (4,5,15);
回答by Parris Varney
Can you make a table that contains expected actor ids?
你能制作一个包含预期演员 ID 的表格吗？
If so you can left join from it.
如果是这样，您可以离开它加入。
SELECT * FROM expected_actors LEFT JOIN actors USING (ID)
