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

Question

我正在学习 CTE，遇到了一个我无法解决的练习。这不是家庭作业，而是我学习SQL的在线课程中的练习。我对我犯错的地方和一些解释感兴趣，所以只用正确的代码回答对我学习 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

Answer 2

您需要从分组中删除amount，这应该返回预期的结果：

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