mysql - 如何在子查询内使用外部选择的列

Question

我尝试在子查询中使用名为 children_ids 的列的值:

SELECT hotels.id,
       hotels.hotel,
       hotels.country,
       hotels.corrected,
       hotels.children_type,
       hotels.children_ids as children_ids,
       (SELECT SUM(trx_input.count) FROM hotels LEFT JOIN trx_input ON hotels.trx_id = trx_input.id WHERE hotels.id IN (children_ids)) as count_children,
       trx_input.count
FROM hotels
LEFT JOIN trx_input ON hotels.trx_id = trx_input.id
WHERE hotels.country = 'DE' AND children_type != 2
ORDER BY hotels.hotel
LIMIT 1000

但是count_children始终为NULL。如果我用一些实际值替换 children_ids ，它会起作用:

SELECT hotels.id,
       hotels.hotel,
       hotels.country,
       hotels.corrected,
       hotels.children_type,
       hotels.children_ids as children_ids,
       (SELECT SUM(trx_input.count) FROM hotels LEFT JOIN trx_input ON hotels.trx_id = trx_input.id WHERE hotels.id IN (338666,338665,338456,338691)) as count_children,
       trx_input.count
FROM hotels
LEFT JOIN trx_input ON hotels.trx_id = trx_input.id
WHERE hotels.country = 'DE' AND children_type != 2
ORDER BY hotels.hotel
LIMIT 1000

有什么方法可以在子查询中使用外部列的值吗？

Answer 1

您可以使用find_in_set()做您想做的事情:

   (SELECT SUM(i.count)
    FROM hotels h2 JOIN
         trx_input i
         ON h2.trx_id = i.id
    WHERE find_in_set(h2.id, h.children_ids) > 0
   ) as count_children,

但是，您应该修复数据结构以使用正确的连接表。在逗号分隔列中存储 id 列表并不是存储数据的正确方法。