sql - 使用子查询（公用表表达式）作为过滤器

Question

tl;dr：根据今天和昨天的交货日期，我的过滤条件有很大不同。如何利用子查询来简化我的代码？

---

我一直在自学 SQL，并且在大多数概念上都非常扎实，除了子查询。一般来说，我认为我可以在查询之前使用WITH ___ AS来返回满足子查询中指定条件的结果。我以为我可以让它与多个子查询一起工作，但我遇到了麻烦。它适用于一个子查询，但不适用于多个子查询。我知道这可以在 WHERE 语句中完成，但这会非常复杂。这是我想做的一个例子：

WITH todays_results AS(
SELECT
order_id,
status,
message
FROM delivery_statuses
WHERE delivery date = STRLEFT(CAST(now() AS string,10)
AND (status = 'delivered'
OR (status = 'out for delivery' AND message = 'On vehicle for delivery')
OR (status = 'in transit' AND message <>'At sort center')
),

yesterdays_results AS (
SELECT
order_id,
status,
message
FROM delivery_statuses
WHERE delivery date = STRLEFT(CAST(now() - INTERVAL 1 days AS string,10)
AND (status = 'delivered'
OR (status = 'out for delivery' AND message = 'Shipment will be delivered within 1 hour')
OR (status = 'pre transit' AND message <>  'Order processing')
)

SELECT
*

FROM customer_details cd
INNER JOIN
(SELECT * FROM todays_results) tr
ON cd.order_id = tr.order_id
INNER JOIN
(SELECT * FROM yesterdays_results) yr
ON cd.order_id = yr.order_id

我怎样才能使它返回与第一个子查询匹配的结果，并在第二个子查询的结果下方？我什至想添加第三个子查询。

标签： sqlsubquerycommon-table-expression

Answer 1

给每个with 子句一个数字来标识它。然后您可以合并所有连接，并按公共id列对结果进行排序。

-- this code has not been tested.
WITH 
A AS
(
    SELECT 1 ID, OTHER_STUFF FROM SOME_WHERE
),
B AS
(
    SELECT 2 ID, OTHER_STUFF FROM SOME_WHERE_ELSE
)
SELECT *
FROM
(
    SELECT A.ID, C.*
    FROM TABLEX C
    JOIN A
      ON A.KEY = C.KEY

    UNION ALL

    SELECT B.ID, D.*
    FROM TABLEX D
    JOIN B
      ON B.KEY = D.KEY
) 
ORDER BY ID