sql - 使用 CTE 计算内部查询中的行数

Question

我正在学习 CTE，遇到了一个我无法解决的练习。它不是家庭作业，而是我学习 SQL 的在线类(class)中的练习。我对我在哪里犯了错误和一些解释很感兴趣，所以只用正确的代码回答不会帮助我学习 CTE。

任务是计算筹集到最低金额的 100% 到 150% 的项目，以及筹集到超过 150% 的项目。

我编写了以下 CTE:

WITH nice_proj AS
  (SELECT project_id AS pid,
          amount AS amount,
          minimal_amount AS minimal
   FROM donation d
   INNER JOIN project p ON (d.project_id = p.id)
   GROUP BY pid,
            minimal,
            amount
   HAVING sum(amount) >= minimal_amount)
SELECT count(*) AS COUNT,
       (CASE
            WHEN sum(amount)/minimal <=1.5 THEN 'good projects'
            ELSE 'great projects'
        END) AS tag
FROM nice_proj
GROUP BY minimal;

查询不返回任何内容，但它应该产生类似于:

+-------+----------------+
| count | tag            |
+-------+----------------+
| 16    | good projects  |
+-------+----------------+
| 7     | great projects |
+-------+----------------+

请看一下表格(它们被截断了):

捐赠

+----+------------+--------------+---------+------------+------------+
| 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 |
+----+------------+--------------+---------+------------+------------+
| 7  | 4          | 14           | 746.52  | 649.48     | 2016-08-03 |
+----+------------+--------------+---------+------------+------------+
| 8  | 10         | 3            | 962.36  | 837.25     | 2016-10-30 |
+----+------------+--------------+---------+------------+------------+
| 9  | 1          | 20           | 764.05  | 664.72     | 2016-08-24 |
+----+------------+--------------+---------+------------+------------+
| 10 | 10         | 4            | 1033.42 | 899.08     | 2016-02-26 |
+----+------------+--------------+---------+------------+------------+
| 11 | 5          | 6            | 571.90  | 497.55     | 2016-10-06 |
+----+------------+--------------+---------+------------+------------+

项目

+----+------------+-----------+----------------+
| 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           |
+----+------------+-----------+----------------+
| 6  | video      | 4         | 6835           |
+----+------------+-----------+----------------+
| 7  | video      | 4         | 7978           |
+----+------------+-----------+----------------+
| 8  | games      | 1         | 4560           |
+----+------------+-----------+----------------+
| 9  | games      | 2         | 4259           |
+----+------------+-----------+----------------+
| 10 | games      | 1         | 5253           |
+----+------------+-----------+----------------+

Answer 1

我的建议是先聚合捐赠表，然后将其与项目表进行比较。

通过这样做，捐赠和项目之间的连接始终是 1:1。这反过来意味着您不必按“值”(minimal_amount) 进行分组，而是仅按“标识符”(project_id) 进行分组。

WITH
  donation_summary AS
(
  SELECT
    project_id,
    SUM(amount)   AS total_amount
  FROM
    donation
  GROUP BY
    project_id
)
SELECT
  CASE WHEN d.total_amount <= p.minimal_amount * 1.5
       THEN 'good projects'
       ELSE 'great projects'
  END
     AS tag,
  COUNT(*)   AS project_count
FROM
  donation_summary   AS d
INNER JOIN
  project            AS p
    ON p.id = d.project_id
WHERE
  d.total_amount >= p.minimal_amount
GROUP BY
  tag

也就是说，我通常会使用以下最终查询并获得两列而不是两行...

SELECT
  SUM(CASE WHEN d.total_amount <= p.minimal_amount * 1.5 THEN 1 ELSE 0 END)  AS good_projects,
  SUM(CASE WHEN d.total_amount >  p.minimal_amount * 1.5 THEN 1 ELSE 0 END)  AS great_projects
FROM
  donation_summary   AS d
INNER JOIN
  project            AS p
    ON p.id = d.project_id
WHERE
  d.total_amount >= p.minimal_amount