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

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

postgresql sql

menteith · 技术社区 · 6 年前

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

2 回复 | 直到 6 年前

MatBailie 6 年前

我的建议是先汇总捐款表,然后将其与项目表进行比较。

(最低金额) ,而只按“标识符”分组 (项目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

dnoeth 6 年前

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;