mysql - 在子查询的子查询中使用父查询的列

Question

使用此查询:

SELECT
    `id`,
    `type`,
    `subtype`,
    `title`,
    `shortdesc`,
    (SELECT COUNT(*)
     FROM `story_comments`
     WHERE `parent_id` = t1.`id`) as comments,
    (SELECT
        (ROUND( (
            SELECT (SUM(`rating` * `count`) / SUM(`count`) ) ) * 2) ) / 2 as result
            FROM
                    (SELECT rating, COUNT(*) as count
                     FROM `story_ratings` WHERE `parent_id` = t1.`id`
                     GROUP BY rating) as val) as rating,
    `calls`,
    `user`
FROM
    `storys` t1
WHERE
    `open` = 1 AND
    `modremove` = 0 AND
    `modblock` = ''
ORDER BY
    `opening`
DESC LIMIT 16;

我收到此错误:#1054 - “where 子句”中的未知列“t1.id”，这是由子查询中的子查询(FROM 之后的子查询)引起的。

但是第一个子查询中的t1.id工作正常。为什么我不能在 FROM 子查询中使用它？我也尝试过变量，但也不起作用:

SELECT @i := `id` id, `type`, `subtype`, `title`, `shortdesc`, (SELECT COUNT(*) FROM `story_comments` WHERE `parent_id` = t1.`id`) as comments, 

(SELECT (ROUND( (SELECT (SUM(`rating` * `count`) / SUM(`count`) ) ) * 2) ) / 2 as result FROM (SELECT rating, COUNT(*) as count FROM `story_ratings` WHERE `parent_id` = @i GROUP BY rating) as val) as rating,

`calls`, `user` FROM `storys` t1 WHERE `open` = 1 AND `modremove` = 0 AND `modblock` = '' ORDER BY `opening` DESC LIMIT 16;

使用@i变量，结果每行都返回NULL，这是错误的。

Answer 1

哇。这么多嵌套子查询。不要将查询嵌套到地球的尽头，而是使用 JOIN并汇总您的数据以计算您需要的内容。我不得不对您的表结构做出一些猜测，因为您没有提供它们(在发布数据库问题时您应该始终这样做)。

SELECT
    S.id,
    S.type,
    S.subtype,
    S.title,
    S.shortdesc,
    COUNT(DISTINCT SC.id) AS comments,
    AVG(SR.rating) AS rating,
    calls,
    user
FROM
    Storys S  -- Storys isn't the plural of Story, BTW
LEFT OUTER JOIN Story_Comments SC ON SC.parent_id = S.id
LEFT OUTER JOIN Story_Ratings SR ON SR.parent_id = S.id
WHERE
    S.open = 1 AND
    S.modremove = 0 AND
    S.modblock = ''
GROUP BY
    S.id,
    S.type,
    S.subtype,
    S.title,
    S.shortdesc,
    S.calls,
    S.user
ORDER BY
    opening
DESC LIMIT 16;

我认为“* 2 .../2”的作用并不像您想象的那样，基于各种括号，并且根据 rating 的数据类型，舍入可能不正确。列(同样，没有表结构，所以我只能猜测)。

我手头没有 MySQL 服务器，但在 SQL Server 上测试此代码(调整 ROUND 函数中的差异)会得到与第一个查询相同的结果。