时间戳

首页 > 解决方案 > CTE 查询失败，出现关于未知列名的错误

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

问题描述

我正在学习 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    |
+----+------------+-----------+

目的是显示支持者的名字和姓氏以及他们创建的尚未创建项目的数量（金额总和< minimum_amount ）。

我的 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;

我收到一个错误：

“第一”列不存在

标签： sql

解决方案

您在 GROUP BY FIRST、LAST 中提及列名是错误的，这在您的表中不存在，并且 minimum_amount 也需要包含在 group by 子句中，因为您没有对该列使用任何聚合

    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

推荐阅读