我正在尝试使用子查询来使用 JOIN 查询几个表来缩小返回的结果。当我在子查询中使用 WHERE 子句引用外部查询中的列值时，我在 where 子句中收到错误 #1054 unknown column。
我想使用 mode=2 查询主表并从事务表中获取相关记录。如果 Master.mode=2 和 Master.type=1，则获取所有 id 匹配但 type=2 的交易，反之亦然。简而言之，从事务中获取 ID 匹配但类型值相反的记录。如果超过 1 条记录，则从 Transaction 表中获取最高 'Amount' 的记录。
Master table
+----+-----+-------+-------+
|id |mode |type |other |
+----+-----+-------+-------+
|1 |1 |1 |11111 |
|2 |2 |2 |22222 |
|3 |2 |1 |33333 |
|4 |1 |2 |44444 |
+----+-----+-------+-------+
Transaction table
+----+-------+-------+
|id |type |amount |
+----+-------+-------+
|1 |1 |1000 |
|1 |2 |1000 |
|2 |1 |2000 |
|2 |2 |3000 |
|3 |1 |500 |
|3 |1 |5000 |
|3 |1 |3000 |
|3 |2 |4000 |
|3 |2 |2000 |
|4 |1 |1000 |
|4 |2 |2000 |
|1 |1 |3000 |
+----+-------+-------+
Expected Result
+----+-----+-------+-------+-------+
|id |mode |type |other |amount |
+----+-----+-------+-------+-------+
|2 |2 |2 |22222 |2000 |
|3 |2 |1 |33333 |4000 |
+----+-----+-------+-------+-------+
My query
SELECT t1.*, t2.Amount
FROM master AS t1
INNER JOIN (
SELECT t3.*
FROM transactions AS t3
WHERE t3.id=t1.id AND t3.Type=(CASE WHEN t1.Type=1 THEN 2 ELSE 1 END)
ORDER BY t3.Amount DESC
LIMIT 1
) AS t2 ON t1.id=t2.id
WHERE t1.Mode=2 AND t2.Type=(CASE WHEN t1.Type=1 THEN 2 ELSE 1 END)
我收到错误#1054 - 'where 子句' 中的未知列 't1.id'
以达到预期的效果。我们可以从这样的查询开始：
SELECT m.id
, m.mode
, m.type
, m.other
FROM master m
WHERE m.mode = 2
ORDER BY ...
对于该查询返回的每一行，看起来我们想要获得一个值。我们可以使用相关子查询来获取值，例如：
SELECT t.amount
FROM transactions t
WHERE t.id = m.id
AND t.type = IF(m.type=2,1,2)
ORDER BY t.amount DESC
LIMIT 1
相关子查询返回单列，并且返回不超过一行，因此我们可以将其包含在原始查询的 SELECT 列表中。
像这样的东西：
SELECT m.id
, m.mode
, m.type
, m.other
, ( SELECT t.amount
FROM transactions t
WHERE t.id = m.id
AND t.type = IF(m.type=2,1,2)
ORDER BY t.amount DESC
LIMIT 1
) AS `Amount`
FROM master m
WHERE m.mode = 2
ORDER BY ...
其工作方式是执行外部查询（来自 master 的查询）。对于返回的每一行，都会评估 SELECT 列表中的表达式。评估般的表情m.id，m.mode，m.type是非常简单的，并且每个返回其被放入结果集标量值。
评估相关子查询的工作方式相同。它有点复杂... MySQL 执行子查询，使用当前m行的值，并返回单个值，该值被放入结果集中。
笔记：
如果相关子查询不返回行，则将 NULL 放入结果集中。
如果规范是只返回金额列中具有非 NULL 值的行，我们可以添加一个HAVING子句
WHERE m.mode = 2
HAVING `Amount` IS NOT NULL
ORDER BY ...
另请注意：
因为对于外部查询返回的每一行都执行相关子查询，对于来自 master 的很多行，那就是很多子查询执行，这会降低大型集合的性能。
这使得为子查询提供合适的索引变得非常重要。理想情况下，覆盖索引...
ON transaction (id, type, amount)
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句