問題描述

使用此查詢:

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.

這篇關于在子查詢的子查詢中使用父查詢的列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！