mysql - "WHERE"在 mySQL 语句中不起作用

Question

此处的 mySQL 语句尝试按年份打印发票总金额。

> select YEAR(I.INV_DATE), SUM(LINE_UNITS * LINE_PRICE) as total    from
> INVOICE I, LINE L     where I.INV_NUMBER = L.INV_NUMBER   group by 1

但如果我添加类似内容(仅打印当年大于 500 的内容)

> select YEAR(I.INV_DATE), SUM(LINE_UNITS * LINE_PRICE) as total    from
> INVOICE I, LINE L     where I.INV_NUMBER = L.INV_NUMBER   group by 1
>         where total > 500

给我错误消息:语法错误

我做错了什么？谢谢!!

Answer 1

使用HAVING而不是WHERE

SELECT....
FROM ...
GROUP BY ...
HAVING SUM(LINE_UNITS * LINE_PRICE) > 500

或者您可以将其包装在这样的子查询中，

SELECT *
FROM
(
    SELECT  YEAR(I.INV_DATE), SUM(LINE_UNITS * LINE_PRICE) as total
    FROM    INVOICE I, LINE L
    WHERE   I.INV_NUMBER = L.INV_NUMBER   
    GROUP   BY 1
) a
WHERE total > 500