sql - 如何将来自同一数据库的两个查询与不同的 where 子句结合起来？

Question

SELECT
    DATE_PART(week, pickup_date) AS Week,
    CAST(pickup_date AS date),
    Sum(pick_up) AS pickup,
    location_id
FROM
    really_large.db
WHERE
    location_id IN ('Location_1', 'Location_2', 'Location_3')
    AND DATE_PART(week, event_pickup_date) IS NOT NULL
    AND pick_up = 1
    AND in_transit = 0
    AND scan_event = 0
    AND delivery = 0
    AND return_datetime_min IS NULL
GROUP BY
    Date_part(week, pickup_date), CAST(pickup_date AS date), location_id
ORDER BY
    Date_part(week, pickup_date) DESC, CAST(pickup_date AS date) DESC, location_id ASC

UNION

SELECT
    DATE_PART(week, pickup_date) AS Week,
    CAST(pickup_date AS date),
    SUM(in_transit) AS In_Transit,
FROM
    really_large.db
WHERE
    location_id IN ('Location_1', 'Location_2', 'Location_3')
    AND DATE_PART(week, event_pickup_date) IS NOT NULL
    AND pick_up = 1
    AND in_transit = 1
    AND scan_event = 0
    AND delivery = 0
    AND return_datetime_min IS NULL
GROUP BY 
    Date_part(week, pickup_date), CAST(pickup_date AS date), location_id
ORDER BY
    Date_part(week, pickup_date) DESC, CAST(pickup_date AS date) DESC, location_id ASC;

联合导致语法错误。

理想情况下，这将显示

星期	日期	总和（取件）	总和（in_transit）	Location_id
28	21 年 7 月 18 日	72	88	位置_1
28	21 年 7 月 18 日	75	68	位置_2
28	21 年 7 月 18 日	96	72	位置_3
27	21 年 7 月 14 日	76	92	位置_1
27	21 年 7 月 14 日	71	72	位置_2
27	21 年 7 月 14 日	69	81	位置_3

标签： sqlwhere-clause

Answer 1

两个查询都需要返回相同数量的列和数据类型来进行联合。您的第二个查询缺少 location_id 列。