sql - 在 SELECT 语句中填写缺失值

Question

我有一个包含两列的表，客户 ID 和订单。假设我总共有订单 ID 1、2、3、4

所有客户都可以拥有所有四个订单，如下所示:

    1234 1
    1234 2
    1234 3
    1234 4
    3245 3
    3245 4
    5436 2
    5436 4

您可以在上面看到 3245 客户没有订单 ID 1 或 2。我如何在查询输出中打印:

3245 1
3245 2
5436 1
5436 3

编辑:我没有订单表，但我有一个订单列表，我们可以在查询中对其进行硬编码 (1,2,3,4)。

Answer 1

SELECT  c.id, o.order
FROM    (
        SELECT  1 AS order
        UNION ALL
        SELECT  2 AS order
        UNION ALL
        SELECT  3 AS order
        UNION ALL
        SELECT  4 AS order
        ) o
CROSS JOIN
        (
        SELECT  DISTINCT id
        FROM    customer_orders
        ) c
WHERE   NOT EXISTS
        ( 
        SELECT  NULL
        FROM    customer_orders ci
        WHERE   ci.id = c.id
                AND ci.order = o.order
        )

如果你有customers表，它就变得更简单了:

SELECT  c.id, o.order
FROM    (
        SELECT  1 AS order
        UNION ALL
        SELECT  2 AS order
        UNION ALL
        SELECT  3 AS order
        UNION ALL
        SELECT  4 AS order
        ) o
CROSS JOIN
        customers c
WHERE   NOT EXISTS
        ( 
        SELECT  NULL
        FROM    customer_orders ci
        WHERE   ci.id = c.id
                AND ci.order = o.order
        )