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

2021-09-16 00:00:00 sql variables mysql

使用此查询:

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 之后的子查询)引起的.

I get this error: #1054 - Unknown column 't1.id' in 'where clause', which is caused by the subquery in the subquery (subquery after FROM).

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

But the t1.id in the first subquery is working fine. Why cant I use it in the FROM-subquery? I also tried variables, which also didnt work:

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，这是怎么回事.

With the @i variable, result returned NULL on every row, what is wrong.

推荐答案

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

Wow. So many nested subqueries. Instead of nesting queries to the ends of the earth, use JOINs and aggregate your data to calculate what you need. I had to make some guesses about your table structures because you didn't supply them (something that you should always do when posting a database question).

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"会根据各种括号执行您认为的操作，并且根据您的评级的数据类型，此处的舍入可能不正确 列(同样，没有表结构，所以我一直在猜测).

I don't think that the "* 2 ... / 2" does what you think it does, based on the various parentheses and the rounding might not be right here depending on the data type of your rating column (again, no table structures, so I'm stuck guessing).

我手边没有 MySQL 服务器，但在 SQL Server 上测试此代码(针对 ROUND 函数中的差异进行调整)给出了与您的第一次查询相同的结果.

I don't have a MySQL server handy, but testing this code on SQL Server (adjusting for difference in the ROUND function) gave the same results as your first query.

相关文章