为什么我可以对内联 SELECT 值进行排序，但不能在 WHERE 子句中使用它？

发布于 2024-07-18 02:08:49 字数 843 浏览 13 评论 0原文

我有一个小的 SQL 查询。

SELECT a.`id` , a.`title` , a.`date` , 
(
    SELECT MAX( grade )
    FROM tests
    WHERE userid = 41
    AND presid = a.`id`
) AS grade
FROM `presentations` a
WHERE a.`visible` = 1
AND `grade` >= 5
ORDER BY `grade` DESC

这给了我错误

1054 - “where 子句”中存在未知列“等级”

但是如果我删除倒数第二行，它就可以正常工作。我尝试过执行 AND a.grade 甚至给测试表命名并将该名称附加到成绩中，但仍然没有成功。

如何在 WHERE 子句中使用此内联查询？

我发现这可行，但这是唯一的方法吗？

SELECT a.`id` , a.`title` , a.`date` , 
(
    SELECT MAX( grade )
    FROM tests
    WHERE userid = 41
    AND presid = a.`id`
) AS grade
FROM `presentations` a
WHERE a.`visible` = 1
AND (
    SELECT MAX( grade )
    FROM tests
    WHERE userid = 41
    AND presid = a.`id`
) >= 5
ORDER BY `grade` DESC

原文

I have this small SQL query.

SELECT a.`id` , a.`title` , a.`date` , 
(
    SELECT MAX( grade )
    FROM tests
    WHERE userid = 41
    AND presid = a.`id`
) AS grade
FROM `presentations` a
WHERE a.`visible` = 1
AND `grade` >= 5
ORDER BY `grade` DESC

This gives me the error

1054 - Unknown column 'grade' in 'where clause'

But if i remove the 2nd last line, it works fine. I have tried to do AND a.grade and even give the tests table a name and append that name to grade but still no luck.

How can I use this inline query in a WHERE clause?

I have found that this works, but is it the only way?

SELECT a.`id` , a.`title` , a.`date` , 
(
    SELECT MAX( grade )
    FROM tests
    WHERE userid = 41
    AND presid = a.`id`
) AS grade
FROM `presentations` a
WHERE a.`visible` = 1
AND (
    SELECT MAX( grade )
    FROM tests
    WHERE userid = 41
    AND presid = a.`id`
) >= 5
ORDER BY `grade` DESC

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜点 2024-07-25 02:08:49

Sql 语句按以下顺序进行计算：

FROM
WHERE
SELECT
GROUP
HAVING
ORDER

因此，您在 SELECT 子句中定义的内容在 WHERE 子句中不可用。您需要将该约束放入 HAVING 子句中：

SELECT a.`id` , a.`title` , a.`date` , 
(
    SELECT MAX( grade )
    FROM tests
    WHERE userid = 41
    AND presid = a.`id`
) AS grade
FROM `presentations` a
WHERE a.`visible` = 1
HAVING `grade` >= 5
ORDER BY `grade` DESC

Sql statements are somewhat evaluated in the following order:

FROM
WHERE
SELECT
GROUP
HAVING
ORDER

So things you define in the SELECT-clause are not available in the WHERE-clause. You would need to put that constraint into a HAVING-clause:

SELECT a.`id` , a.`title` , a.`date` , 
(
    SELECT MAX( grade )
    FROM tests
    WHERE userid = 41
    AND presid = a.`id`
) AS grade
FROM `presentations` a
WHERE a.`visible` = 1
HAVING `grade` >= 5
ORDER BY `grade` DESC

回复收藏 0 原文

ζ澈沫 2024-07-25 02:08:49

SELECT  a.`id` , a.`title` , a.`date` , 
        (
        SELECT  MAX( grade )
        FROM    tests
        WHERE   userid = 41
                AND presid = a.`id`
       ) AS grade
FROM    `presentations` a
WHERE   a.`visible` = 1
HAVING  `grade` >= 5
ORDER BY
        `grade` DESC

SELECT  a.`id` , a.`title` , a.`date` , 
        (
        SELECT  MAX( grade )
        FROM    tests
        WHERE   userid = 41
                AND presid = a.`id`
       ) AS grade
FROM    `presentations` a
WHERE   a.`visible` = 1
HAVING  `grade` >= 5
ORDER BY
        `grade` DESC

回复收藏 0 原文

~没有更多了~