“having子句”中的未知列“a.revenue”

Question

SELECT 
      a.seller_id seller_id
FROM 
     (SELECT seller_id, sum(price) revenue 
      FROM Sales GROUP BY seller_id) as a
HAVING 
     a.revenue = min(a.revenue)

我得到的错误是Unknown column 'a.revenue' in 'having clause'，我想得到的卖家，有最低的收入。似乎我既不能使用where也不能使用having。我该怎么办？

下面是表结构，列名“price”实际上是每种产品的收入。

+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 2          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 4        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+

预期输出为

+-------------+
| seller_id   |
+-------------+
| 2           |
+-------------+

Answer 1

您需要做的是首先计算子查询中的最低收入，并使用它来检查哪些卖家的收入等于最低收入。

像这样的东西

select 
  seller_id,sum(price) 
from sales 
group by seller_id 
having sum(price) = (select sum(price) from sales group by seller_id order by 1 limit 1)

Answer 2

revenue仅在子查询中可用。您需要将其“导出”到外部查询的结果中，以便外部查询的HAVING能够访问它。

SELECT 
      a.seller_id seller_id, a.revenue revenue ...

Answer 3

现在我不能测试它，但是我想像这样的东西可以工作

Select * from (
SELECT a.seller_id seller_id FROM (SELECT seller_id, sum(price) revenue FROM Sales) as a) as res GROUP BY res.seller_id  having res.revenue = min(res.revenue)