时间戳

首页 > 解决方案 > 如何在父查询案例语句中使用子查询的结果

mysql - 如何在父查询案例语句中使用子查询的结果

问题描述

我有以下查询返回预期结果：

SELECT
    locations.*,
    (
        SELECT
            id
        FROM
            hauls
        WHERE
            haul_type_id = 1
            AND location_id = locations.id
        ORDER BY
            created_at DESC
        LIMIT 1) AS last_delivery_id,
    (
        SELECT
            id
        FROM
            hauls
        WHERE
            haul_type_id = 2
            AND location_id = locations.id
        ORDER BY
            created_at DESC
        LIMIT 1) AS last_pickup_id
FROM
    locations

我想在父查询的 case 语句中使用子查询 ( last_delivery_id, ) 的结果，但出现错误：last_pickup_id

错误：未知列“last_delivery_id”

SELECT
    locations.*, 
    case when last_delivery_id = 1 then 'pending' when last_delivery_id = 2 then 'active' end as status,
    (
        SELECT
            id
        FROM
            hauls
        WHERE
            haul_type_id = 1
            AND location_id = locations.id
        ORDER BY
            created_at DESC
        LIMIT 1) AS last_delivery_id,
    (
        SELECT
            id
        FROM
            hauls
        WHERE
            haul_type_id = 2
            AND location_id = locations.id
        ORDER BY
            created_at DESC
        LIMIT 1) AS last_pickup_id
FROM
    locations

标签： mysqlsqldatetimesubquerylateral-join

解决方案

您可以使用子查询或 CTE：

SELECT lh.*, . . .   -- any expressions you want
       (case when last_delivery_id = 1 then 'pending'
             when last_delivery_id = 2 then 'active'
        end) as status
FROM (SELECT l.*,
             (SELECT id
              FROM hauls h
              WHERE h.haul_type_id = 1 AND
                    h.location_id = l.id
              ORDER BY h.h.created_at DESC
              LIMIT 1
             ) AS last_delivery_id,
             (SELECT id
              FROM hauls h
              WHERE h.haul_type_id = 2 AND
                    h.location_id = l.id
              ORDER BY h.created_at DESC
              LIMIT 1
             ) AS last_pickup_id
      FROM locations l
     ) lh

请注意表别名的使用，这使查询更易于编写和阅读。delivery_id也就是说，如果您只想要字符串版本，我不确定您是否真的需要：

      SELECT l.*,
             (SELECT (case when h.id = 1 then 'pending'
                           when h.id = 2 then 'active'
                      end)
              FROM hauls h
              WHERE h.haul_type_id = 1 AND
                    h.location_id = l.id
              ORDER BY h.created_at DESC
              LIMIT 1
             ) AS status
      FROM locations l

推荐阅读