Question

我需要通过一套价值观(不到10项),看看它们是否被列入表。如果是的话,我需要印刷所有记录价值,但如果该项目没有存在,我仍然希望将其列入印刷结果,尽管有联合国扫盲十年或零值。因此,例如,以下询问:

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 |
+----+-----------------------------+-------------+----------+------+

But I want it to return

+----+-----------------------------+-------------+----------+------+
| ID | NAME                        | DESCRIPTION | ORDER_ID | TYPE |
+----+-----------------------------+-------------+----------+------+
|  4 | [TEST-1]                    |             |        3 | NULL |
|  5 | [TEST-2]                    |             |        4 | NULL |
|  15| NULL                        |             |        0 | NULL |
+----+-----------------------------+-------------+----------+------+

这是可能的吗?

Answer 1

为了取得你想要的产出,你首先必须建造一个含有你所希望的<代码>ACTOR.id的衍生表格。小型数据组的所有工程:

SELECT *
  FROM (SELECT 4 AS actor_id
          FROM DUAL
        UNION ALL
        SELECT 5
          FROM DUAL
        UNION ALL
        SELECT 15
          FROM DUAL) x

为此,您可向实际桌旁索取您想要的结果:

   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

如在<代码>x和a之间没有对应关系,则<代码>a栏将无效。因此,如果在15岁前没有相应时间,你会命令为零:

   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

Answer 2

诚然,就这种微薄的价值观而言,你可以做这样的事情。

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, 我想;对于Oracle You d need to use DUAL, e.g. http://www.un.org/Depts/DGACM/index_french.htm

Answer 3

这只能使用临时表格。

Answer 4

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;

Answer 5

如果你知道对身份证的上限和下限,那不会太坏。找到所有可能女方的意见——通过trick子连接是最简单的方式——并且是外人与你的真正表态。在此,我把它限于价值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);

Answer 6

您能否提出一个包含预期行为者的表格?

如果是的话,你可以离开。

SELECT * FROM expected_actors LEFT JOIN actors USING (ID)

友情链接