如何在MySQL的内部查询中从外部查询访问值

debugcn 发表于 Dev

奥姆卡

SELECT * FROM events e LEFT JOIN venue v ON v.id=e.venueid 
CROSS JOIN (select (((5*sum(rating = 5))+(4*sum(rating = 4))+(3*sum(rating = 3))+(2*sum(rating = 2))+sum(rating = 1))/count(*)) rating 
from feedbacks where boxid = **e.boxid**) r 
WHERE e.startdate = CURDATE() AND e.starttime >= CURTIME() 
LIMIT 10;

我无法在子查询中访问e.boxid。有人可以帮忙吗？

古尔温德·辛格（Gurwinder Singh）

使用GROUP BYonboxid将其带到子查询之外，然后将其与events表上的表连接，boxid如下所示：

SELECT 
    *
FROM
    events e
        LEFT JOIN
    venue v ON v.id = e.venueid
        JOIN
    (SELECT 
        boxid,
            (((5 * SUM(rating = 5)) + (4 * SUM(rating = 4)) + (3 * SUM(rating = 3)) + (2 * SUM(rating = 2)) + SUM(rating = 1)) / COUNT(*)) rating
    FROM
        feedbacks
    GROUP BY boxid) r ON e.boxid = r.boxid
WHERE
    e.startdate = CURDATE()
        AND e.starttime >= CURTIME()
LIMIT 10;

您还可以使用相关子查询，如下所示：

SELECT e.*,
  v.*,
  (SELECT (((5*SUM(rating = 5))+(4*SUM(rating = 4))+(3*SUM(rating = 3))+(2*SUM(rating = 2))+SUM(rating = 1))/COUNT(*)) AS rating
  FROM feedbacks f
  WHERE f.boxid = e.boxid
  ) rating
FROM events e
LEFT JOIN venue v
ON v.id           = e.venueid
WHERE e.startdate = CURDATE()
AND e.starttime  >= CURTIME() LIMIT 10;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。