我希望在两个日期内按类别对我的所有项目进行计数。我有一张主桌子:
表项目
+-----+-------------+------------------+
| 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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句