mysql - 复杂的 MYSQL 从 5 个表中选择

Question

我有五个表存储设施、员工、完成的单位、项目和类次。

我需要确定每个设施的项目生产率(累计完成数和累计小时数)，前提是该设施在项目上记录了小时数。该设施存储在员工表中。

这是我的表结构，(减少为这个问题的必要字段):

facilities
+------------+
| id         |
+------------+
| label      |
+------------+

employees
+------------+
| id         |
+------------+
| facility   |
+------------+

completes
+------------+
| id         |
+------------+
| project    |
+------------+
| completes  |
+------------+
| employee   |
+------------+

projects
+------------+
| id         |
+------------+
| title      |
+------------+

shifts
+------------+
| id         |
+------------+
| project    |
+------------+
| length     |
+------------+
| employee   |
+------------+

我遇到麻烦的地方是选择设施的生产力，当它与员工一起存储时，而不是与类次或完成的#一起存储。也许这是一个设计缺陷？ shift 和 completes 表是否也应该有一个 facility 列？当员工已经确定设施时，设施列似乎是多余的。

SELECT p.title, SUM(c.completes), SUM(s.length)
FROM projects as p
LEFT JOIN completes as c ON c.project = p.id
LEFT JOIN shifts as s ON s.project = p.id
GROUP BY p.id, shifts.employee.facility;

GROUP BY 语句当然是无效的。但它展示了我想要完成的事情。我还想在声明中选择设施标签。我该如何处理？

提前感谢您提供的任何见解!

Answer 1

这是我认为 CROSS JOIN 是最佳方法的罕见实例之一:

SELECT p.title, f.label, SUM(c.completes), SUM(s.length)
FROM facilities as f        
CROSS JOIN projects as p
JOIN employees as e ON f.id = e.facility
LEFT JOIN completes as c ON c.employee = e.id AND c.project = p.id
LEFT JOIN shifts as s ON s.employee = e.id AND s.project = p.id
WHERE c.id IS NOT NULL OR s.id IS NOT NULL
GROUP BY p.id, p.title, f.id, f.label

这本质上并不是一个糟糕的设计，但它确实让你不能在这里使用典型的父子关系变得困难，因为在你的 completes 和 上实际上有 2 个 parent >转移表。

另一种方法是使用不带 LEFT JOIN 的 UNION。这可能会返回更快的结果，具体取决于您的 facilities 和 projects 表中当前的行数:

SELECT p.title, 
       f.label, 
       SUM(CASE aggregated.type WHEN 'completes' THEN units ELSE 0 END),
       SUM(CASE aggregated.type WHEN 'shifts' THEN units ELSE 0 END)
FROM (
    SELECT 'completes' as type, project, employee, completes as units
    FROM completes
    UNION
    SELECT 'shifts', project, employee, length
    FROM shifts ) aggregated
JOIN projects as p ON aggregated.project = p.id
JOIN employees as e ON aggregated.employee = e.id
JOIN facilities as f ON e.facility = f.id
GROUP BY p.id, p.title, f.id, f.label

从这个查询中可以看出，如果 completes 和 shifts 存储在同一个表中，问题就可以很容易地解决。根据您的需要，这可能不适合您，但可以考虑将其作为替代架构。