oracle 填充 SELECT 语句中的缺失值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2738033/
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
Fill in missing values in a SELECT statement
提问by Vijay
I have a table with two columns, customer id and order. Let's say I have in total order IDs 1,2,3,4
我有一个包含两列、客户 ID 和订单的表。假设我的订单 ID 总数为 1、2、3、4
All the customer can have all the four orders, like below:
所有客户都可以拥有所有四个订单，如下所示：
1234 1
1234 2
1234 3
1234 4
3245 3
3245 4
5436 2
5436 4
You can see above that 3245 customer doesn't have order id 1 or 2. How could I print in the query output like:
您可以在上面看到 3245 客户没有订单 ID 1 或 2。我如何在查询输出中打印，例如：
3245 1
3245 2
5436 1
5436 3
EDIT: I don't have an order table, but I have a list of order's like we can hard code it in the query(1,2,3,4).
编辑：我没有订单表，但我有一个订单列表，就像我们可以在查询（1,2,3,4）中对其进行硬编码一样。
回答by Quassnoi
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
)
If you have customerstable, it becomes more simple:
如果你有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
)
回答by APC
Okay, there are two issues here. The first problem is turning a list of numbers into a rowset. There are a number of different ways of doing this, depending on how you get the numbers into the query. In the following example I use a function which turns a comma-separated string into a nested table, which can be treated as a regular table with the TABLE() function. This is not strictly relevant to the question you pose. If you're interested in this bit of the implementation see my post in this other thread.
好的，这里有两个问题。第一个问题是将数字列表转换为行集。有许多不同的方法可以做到这一点，这取决于您如何将数字输入到查询中。在下面的示例中，我使用了一个函数，它将逗号分隔的字符串转换为嵌套表，可以将其视为具有 TABLE() 函数的常规表。这与您提出的问题并不严格相关。如果您对实现的这一点感兴趣，请参阅我在其他线程中的帖子。
The second part of the problem is identifying the missing Orders for each Customer. The obvious approaches - such as using NOT IN with a sub-query - won't work, because the Orders for Customer 1234 match all the Order IDs. We need to do is fill in the missing orders for each Customer. This can be done by using a LEFT OUTER JOIN combined with the PARTITION BY clause. It is then a simple matter to filter out the hits by embedding the LOJ query in an outer SELECT, like this:
问题的第二部分是确定每个客户丢失的订单。显而易见的方法 - 例如使用 NOT IN 和子查询 - 将不起作用，因为客户 1234 的订单匹配所有订单 ID。我们需要做的是为每个客户填写缺失的订单。这可以通过将 LEFT OUTER JOIN 与 PARTITION BY 子句结合使用来完成。然后通过将 LOJ 查询嵌入到外部 SELECT 中来过滤掉命中是一件简单的事情，如下所示：
SQL> select customer_id
2 , missing_order_id
3 from (
4 select t42.customer_id
5 , t42.order_id
6 , nos.column_value as missing_order_id
7 from ( select * from table (str_to_number_tokens('1,2,3,4'))) nos
8 left outer join t42 partition by ( t42.customer_id )
9 on nos.column_value = t42.order_id
10 )
11 where order_id is null
12 /
CUSTOMER_ID MISSING_ORDER_ID
----------- ----------------
3245 1
3245 2
5436 1
5436 3
SQL>
回答by DRapp
aside from my comment, and your existing table, I would approach something like this...
除了我的评论和你现有的桌子，我会处理这样的事情......
select distinct
a.Customer,
b.OrderNumber
from
YourOrderTable a,
( select distinct OrderNumber from YourOrderTable ) b
where
b.OrderNumber NOT IN
( select OrderNumber from
YourOrderTable c
where a.Customer = c.Customer
and b.OrderNumber = c.OrderNumber )
By doing a select distinct as the second table in the FROM clause and no specific join to it, you will get a Cartesian join... ie: for each customer, it will join to every possible order number.
通过在 FROM 子句中执行 select distinct 作为第二个表并且没有特定的连接，您将获得笛卡尔连接...即：对于每个客户，它将连接到每个可能的订单号。
Then, in your WHERE clause, the NOT IN SQL test will only allow the "b." order numbers where none exist in the SQL-subselect (c.)
然后，在您的 WHERE 子句中，NOT IN SQL 测试将只允许“b”。SQL-subselect 中不存在的订单号 (c.)
This could be a very costly query, especially if you have many unique orders..
这可能是一个非常昂贵的查询，特别是如果您有许多独特的订单。
