选择子查询的结果

Question

我正在尝试选择FROM子查询的结果q1.res：

    (SELECT SUM(gwr.res1)/(COUNT(*)*1000) as 'res' 
     FROM process.gwr 
     WHERE started = 1 AND gwr.user2 = g.id AND gwr.loss = g.id) AS q1

问题是，我试图选择子查询中的g.id，但由于某种原因，它无法访问它，并返回错误消息：

[Err] 1054 - Unknown column 'g.id' in 'where clause'

这没有意义，因为g.id (grand.id有id列)

SELECT
    g.name, g.point, g.mx, q1.res
FROM
    (SELECT SUM(gwr.res1)/(COUNT(*)*1000) as 'res' 
     FROM process.gwr 
     WHERE (started = 1) AND (gwr.user2 = g.id) AND (gwr.loss = g.id)) AS q1, grand g
LEFT JOIN process.users p ON (p.id = g.owner)
WHERE
    p.name != ""
ORDER BY
    g.point DESC,
    g.mx DESC
LIMIT 5

问题出在哪里？

Answer 1

尝试从子查询的'res‘中删除’‘。

SELECT
    g.name, g.point, g.mx, q1.res
FROM
    (SELECT SUM(gwr.res1)/(COUNT(*)*1000) as res FROM process.gwr WHERE started = 1 AND gwr.user2 = g.id AND gwr.loss = g.id) AS q1, grand g
LEFT JOIN process.users p ON (p.id = g.owner)
WHERE
    p.name != ""
ORDER BY
    g.point DESC,
    g.mx DESC
LIMIT 5

Answer 2

您需要提到DB对象'g‘所指的是什么。这意味着，您需要将其指定为'FROM‘部分中的一个表(或视图)，要么作为一个纯交叉引用，要么--如果您愿意的话--然后使用某种联接。

e.g

SELECT SUM(gwr.res1)/(COUNT(*)*1000) as 'res' 
FROM process.gwr, process.g 
WHERE started = 1 
    AND gwr.user2 = g.id 
    AND gwr.loss = g.id 

请注意“FROM”部分中的“process.g”。

希望这能帮上忙。

K