gpt4 book ai didi

mysql - MySQL 查询连接表中的 GROUP BY、COUNT()

转载 作者:行者123 更新时间:2023-11-29 07:33:11 24 4
gpt4 key购买 nike

我希望在一组两个日期内按类别对我的所有项目进行计数。我有一个主表:

表格项目

+-----+-------------+------------------+
| uid | name | project_category |
+-----+-------------+------------------+
| 1 | Testproject | 1 |
| 2 | Anothertest | 2 |
| 3 | Fietspomp | 1 |
| 4 | Caramba | 1 |
+-----+-------------+------------------+

其类别存储在project_category

+-----+-------------+
| uid | name |
+-----+-------------+
| 1 | Automotive |
| 2 | Hospitality |
| 3 | Other |
+-----+-------------+

但是,我的日期存储在下表中。每个项目可以有更多日期(类次)。我只想查找每个项目的最早日期(类次):我需要筛选每个项目的开始日期(= 第一个类次)。

project_shift

+-----+-------------+------------+
| uid | project_uid | date |
+-----+-------------+------------+
| 1 | 1 | 2015-05-03 |
| 2 | 2 | 2015-05-02 |
| 3 | 2 | 2015-06-04 |
| 4 | 1 | 2015-03-08 |
| 5 | 1 | 2015-08-08 |
+-----+-------------+------------+

我一直在寻找高低,但我的计数总是变得困惑。这是我尝试过的:

SELECT pc.name as category, p.name, count(p.uid)
FROM project p
INNER JOIN project_category pc ON pc.uid = p.project_category
INNER JOIN project_shift ps ON ps.project_uid = p.uid AND ps.date BETWEEN "2015-01-01" AND "2015-08-08"
GROUP BY pc.uid

另外:

SELECT    pc.name as category, count(pc.uid) as amount
FROM project p
LEFT JOIN project_category pc ON pc.uid = p.project_category
WHERE (SELECT MIN(date) FROM project_shift ps WHERE ps.project_uid = p.uid LIMIT 1) BETWEEN :date_from AND :date_to
GROUP BY pc.uid

最后一个已经很接近了,但 29 个项目仍然过多。

我在这里做错了什么?

编辑:所需的结果如下(虚拟数据)

+-------------+--------+
| category | amount |
+-------------+--------+
| Automotive | 70 |
| Hospitality | 22 |
| Other | 2 |
+-------------+--------+

最佳答案

您的查询看起来几乎是正确的。只是,您应该选择的是project_category,并且项目应该是外部连接的(例如获取具有零项目的类别)。因此,您想要按类别计算项目(即 p.uid)。

那么你的 LIMIT 子句就没有意义了。 MIN(date) 为您提供一条包含项目最小日期的记录。这里不需要 LIMIT 子句。

SELECT    pc.name as category, count(p.uid) as amount
FROM project_category pc
LEFT JOIN project p ON p.project_category = pc.uid
WHERE (SELECT MIN(date) FROM project_shift ps WHERE ps.project_uid = p.uid)
BETWEEN :date_from AND :date_to
GROUP BY pc.uid;

关于mysql - MySQL 查询连接表中的 GROUP BY、COUNT(),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/31879893/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com