代码之家 › 专栏 › 技术社区 › menteith

CTE查询失败,出现关于未知列名的错误

sql

-1

menteith · 技术社区 · 6 年前

我正在学CTE,我正忙于做一个练习。请看下表:

    +----+------------+--------------+---------+------------+------------+
    | id | project_id | supporter_id | amount  | amount_eur | donated    |
    +----+------------+--------------+---------+------------+------------+
    | 1  | 4          | 4            | 928.40  | 807.70     | 2016-09-07 |
    | 2  | 8          | 18           | 384.38  | 334.41     | 2016-12-16 |
    | 3  | 6          | 12           | 367.21  | 319.47     | 2016-01-21 |
    | 4  | 2          | 19           | 108.62  | 94.50      | 2016-12-29 |
    | 5  | 10         | 20           | 842.58  | 733.05     | 2016-11-30 |
    | 6  | 4          | 15           | 653.76  | 568.77     | 2016-08-05 |
    +----+------------+--------------+---------+------------+------------+

项目

+----+------------+-----------+----------------+
| id | category   | author_id | minimal_amount |
+----+------------+-----------+----------------+
| 1  | music      | 1         | 1677           |
| 2  | music      | 5         | 21573          |
| 3  | travelling | 2         | 4952           |
| 4  | travelling | 5         | 3135           |
| 5  | travelling | 2         | 8555           |
+----+------------+-----------+----------------+

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1  | Marlene    | Wagner    |
| 2  | Lonnie     | Goodwin   |
| 3  | Sophie     | Peters    |
| 4  | Edwin      | Paul      |
| 5  | Hugh       | Thornton  |
| 6  | Dianne     | Harris    |
+----+------------+-----------+

目的是显示尚未成立的项目数量(总计数量 < 最小金额 )他们创造了。

我的SQL:

WITH projects AS
( 
    SELECT 
        first_name AS firstName,
        last_name AS lastName,
        project_id,
        SUM(amount) AS amountSum,
        minimal_amount
    FROM 
        donation d
    INNER JOIN 
        project p ON p.id = d.project_id
    INNER JOIN 
        supporter s ON s.id = p.author_id
    GROUP BY 
        FIRST, LAST, project_id
)
SELECT 
    firstName,
    lastName,
    COUNT(project_id)
FROM 
    projects
WHERE 
    amountSum < minimal_amount;

我有个错误:

列“first”不存在

1 回复 | 直到 6 年前

Zaynul Abadin Tuhin 6 年前

    WITH projects AS
      ( 
       SELECT first_name AS firstName,
               last_name AS lastName,
               project_id,
               sum(amount) AS amountSum,
               minimal_amount
       FROM donation d
       INNER JOIN project p ON p.id = d.project_id
       INNER JOIN supporter s ON s.id = p.author_id
       GROUP BY first_name ,
                last_name ,
                project_id,minimal_amount
)
    SELECT firstName,
           lastName,
           count(project_id)
    FROM projects
    WHERE amountSum < minimal_amount
   group by firstName,
           lastName