代码之家 › 专栏 › 技术社区 › Ólafur Waage

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

mysql-error-1054 sql mysql

1

Ólafur Waage · 技术社区 · 15 年前

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

2 回复 | 直到 13 年前

1

5

soulmerge 15 年前

Sql语句的计算顺序如下:

从…起
哪里
组
有
顺序

因此,您在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

2

0

Quassnoi 15 年前

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