問題描述

使用此查詢:

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 變量，結(jié)果在每一行都返回 NULL，這是怎么回事.

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

推薦答案

哇.這么多嵌套的子查詢.不要將查詢嵌套到地球的盡頭，而是使用 JOIN 并聚合您的數(shù)據(jù)以計算您需要的內(nèi)容.我不得不對你的表結(jié)構(gòu)進(jìn)行一些猜測，因為你沒有提供它們(在發(fā)布數(shù)據(jù)庫問題時你應(yīng)該總是這樣做).

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;

我不認(rèn)為* 2 .../2"會根據(jù)各種括號執(zhí)行您認(rèn)為的操作，并且根據(jù)您的評級的數(shù)據(jù)類型，此處的舍入可能不正確 列(同樣，沒有表結(jié)構(gòu)，所以我一直在猜測).

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 服務(wù)器，但在 SQL Server 上測試此代碼(針對 ROUND 函數(shù)中的差異進(jìn)行調(diào)整)給出了與您的第一次查詢相同的結(jié)果.

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.

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