mysql - 无法深入访问字段多个子查询

Question

我在尝试访问子查询的子查询中的选定字段时遇到错误。它说“where 子句中的未知列”。我还有其他子查询也使用相同的字段，它们工作得很好。问题似乎只在我尝试访问子查询中的子查询中的字段时出现。

我的查询的简化版本

SELECT r.id, 
 (SELECT min(begindatum) 
  FROM ((SELECT begindatum FROM reservatiekamer 
         WHERE reservatieId = r.id) UNION 
        (SELECT begindatum FROM reservatiefaciliteit 
         WHERE reservatieId = r.id)) as bg)
as begindatum
FROM reservatie r

错误

#1054 - Unknown column 'r.id' in 'where clause'

背景故事，我正在尝试选择存在多个房间和设施预订(2 个不同的表)的预订的开始日期。

Answer 1

如果子查询总是只返回一个值，您可以使用least():

SELECT r.id, 
       least((SELECT begindatum FROM reservatiekamer WHERE reservatieId = r.id),
             (SELECT begindatum FROM reservatiefaciliteit WHERE reservatieId = r.id)
            ) as begindatum
FROM reservatie r;

实际上，这种变体会更好:

SELECT r.id, 
       least(COALESCE((SELECT MIN(begindatum) FROM reservatiekamer WHERE reservatieId = r.id), 0),
             COALESCE((SELECT MIN(begindatum) FROM reservatiefaciliteit WHERE reservatieId = r.id), 0)
            ) as begindatum
FROM reservatie r;

这假设值始终为正，这就是 0 存在的原因。另一个值可能更合适。

最后，您可以将逻辑移至 from 子句:

SELECT r.id,
       LEAST(COALESCE(rk.minbd, rf.minbd), COALESCE(rf.minbd, rk.minbd)) as begindatum
FROM reservatie r LEFT JOIN
     (SELECT reservatieId, MIN(begindatum) as minbd
      FROM reservatiekamer
      GROUP BY reservatieId
     ) rk
     ON r.id = rk.reservatieId LEFT JOIN
     (SELECT reservatieId, MIN(begindatum) as minbd
      FROM reservatiefaciliteit
      GROUP BY reservatieId
     ) rf
     ON r.id = rf.reservatieId;

coalesce() 是 least()，如果另一个为 NULL，则返回一个值。