对单个派生表进行多个查询？

Question

我有一个如下所示的查询，它会生成大量信息

mysql> SELECT impacted.incident_id AS id,
    -> impacted.severity_id as sev,
    -> ((UNIX_TIMESTAMP(incident.stable_time) - UNIX_TIMESTAMP(incident.start_time)) / 60) AS duration,
    -> ops.department.name AS department,
    -> ops.country.name AS country
    -> FROM incident
    -> LEFT JOIN incident_impactedDepartments AS impacted ON incident.incident_id = impacted.incident_id
    -> LEFT JOIN incident_impacted ON incident.incident_id = incident_impacted.incident_id
    -> LEFT JOIN ops.department ON impacted.dept_id = ops.department.dept_id
    -> LEFT JOIN ops.country ON incident_impacted.entity_id = ops.country.country_id
    -> WHERE incident.status in ('OPEN','STABLE','ALL CLEAR','POST MORTEM COMPLETE')
    -> AND incident.incident_id in (35880,35992,33304);
+-------+------+-----------+-------------------+---------------+
| id    | sev  | duration  | department          | country       |
+-------+------+-----------+-------------------+---------------+
| 33304 |    1 |   29.0000 | Marketing         | NULL          | 
| 33304 |    1 |   29.0000 | Marketing         | GLOBAL        | 
| 33304 |    1 |   29.0000 | Accounting        | NULL          | 
| 33304 |    1 |   29.0000 | Accounting        | GLOBAL        | 
| 35880 |    1 |  109.5833 | Marketing         | Argentina     | 
| 35880 |    1 |  109.5833 | Marketing         | Brazil        | 
| 35880 |    1 |  109.5833 | Marketing         | Canada        | 
| 35880 |    1 |  109.5833 | Marketing         | Chile         | 
| 35880 |    1 |  109.5833 | Marketing         | Mexico        | 
| 35880 |    1 |  109.5833 | Marketing         | Peru          | 
| 35880 |    1 |  109.5833 | Marketing         | United States | 
| 35880 |    1 |  109.5833 | Accounting        | Argentina     | 
| 35880 |    1 |  109.5833 | Accounting        | Brazil        | 
| 35880 |    1 |  109.5833 | Accounting        | Canada        | 
| 35880 |    1 |  109.5833 | Accounting        | Chile         | 
| 35880 |    1 |  109.5833 | Accounting        | Mexico        | 
| 35880 |    1 |  109.5833 | Accounting        | Peru          | 
| 35880 |    1 |  109.5833 | Accounting        | United States | 
| 35880 |    2 |  109.5833 | Finance           | Argentina     | 
| 35880 |    2 |  109.5833 | Finance           | Brazil        | 
| 35880 |    2 |  109.5833 | Finance           | Canada        | 
| 35880 |    2 |  109.5833 | Finance           | Chile         | 
| 35880 |    2 |  109.5833 | Finance           | Mexico        | 
| 35880 |    2 |  109.5833 | Finance           | Peru          | 
| 35880 |    2 |  109.5833 | Finance           | United States | 
| 35992 |    3 | 1295.2667 | Accounting        | Italy         | 
+-------+------+-----------+-------------------+---------------+

。您可以看到事件 35880 影响了 7 个国家/地区的金融部门。我的目标是收集每个事件的以下信息。

+-------+------+----------+------------------------------+---------------------------------------------------------+
| id    | sev  | duration | department                   | country                                                 |
+-------+------+----------+------------------------------+---------------------------------------------------------+
| 35880 |    1 | 109.5833 | Marketing,Accounting,Finance | Argentina,Brazil,Canada,Chile,Mexico,Peru,United States | 
+-------+------+----------+------------------------------+---------------------------------------------------------+

我可以使用这样的查询来执行此操作：

SELECT id, sev, duration, GROUP_CONCAT(DISTINCT department SEPARATOR ',') as department, GROUP_CONCAT(DISTINCT country SEPARATOR ',') as country
FROM
(SELECT impacted.incident_id AS id,
impacted.severity_id as sev,
((UNIX_TIMESTAMP(incident.stable_time) - UNIX_TIMESTAMP(incident.start_time)) / 60) AS duration,
ops.department.name AS department,
ops.country.name AS country
FROM incident
LEFT JOIN incident_impactedDepartments AS impacted ON incident.incident_id = impacted.incident_id
LEFT JOIN incident_impacted ON incident.incident_id = incident_impacted.incident_id
LEFT JOIN ops.department ON impacted.dept_id = ops.department.dept_id
LEFT JOIN ops.country ON incident_impacted.entity_id = ops.country.country_id
WHERE incident.status in ('OPEN','STABLE','ALL CLEAR','POST MORTEM COMPLETE')
AND incident.incident_id in (35880))
AS q1

但是提取的严重性是 id 35880 整体的严重性。请参阅，35880 影响了不同级别的多个部门，我希望能够查看特定部门查询的严重性与主 ID 的严重性。但是，如果我在查询中指定部门 ID，就会破坏事情。

mysql> SELECT id, sev, duration, GROUP_CONCAT(DISTINCT department SEPARATOR ',') as department, GROUP_CONCAT(DISTINCT country SEPARATOR ',') as country
    -> FROM
    -> (SELECT impacted.incident_id AS id,
    -> impacted.severity_id as sev,
    -> ((UNIX_TIMESTAMP(incident.stable_time) - UNIX_TIMESTAMP(incident.start_time)) / 60) AS duration,
    -> ops.department.name AS department,
    -> ops.country.name AS country
    -> FROM incident
    -> LEFT JOIN incident_impactedDepartments AS impacted ON incident.incident_id = impacted.incident_id
    -> LEFT JOIN incident_impacted ON incident.incident_id = incident_impacted.incident_id
    -> LEFT JOIN ops.department ON impacted.dept_id = ops.department.dept_id
    -> LEFT JOIN ops.country ON incident_impacted.entity_id = ops.country.country_id
    -> WHERE incident.status in ('OPEN','STABLE','ALL CLEAR','POST MORTEM COMPLETE')
    -> AND incident.incident_id in (35880)
    -> AND impacted.dept_id = 1473)
    -> AS q1;
+-------+------+----------+------------+---------------------------------------------------------+
| id    | sev  | duration | department | country                                                 |
+-------+------+----------+------------+---------------------------------------------------------+
| 35880 |    2 | 109.5833 | Finance    | Argentina,Brazil,Canada,Chile,Mexico,Peru,United States | 
+-------+------+----------+------------+---------------------------------------------------------+

我不再获得受影响部门的完整列表。

这需要两个单独的查询吗？

Answer 1

我不确定我完全理解您的查询，但我希望以下任一查询可能有所帮助：

查询 1：

SELECT id, sev, sev1, duration, GROUP_CONCAT(DISTINCT department SEPARATOR ',') as department, GROUP_CONCAT(DISTINCT country SEPARATOR ',') as country
FROM
(SELECT impacted.incident_id AS id, impacted.severity_id as sev, impacted1.severity_id as sev1, ((UNIX_TIMESTAMP(incident.stable_time) - UNIX_TIMESTAMP(incident.start_time)) / 60) AS duration, ops.department.name AS department,
ops.country.name AS country
FROM incident
LEFT JOIN incident_impactedDepartments AS impacted ON incident.incident_id = impacted.incident_id
LEFT JOIN incident_impactedDepartments AS impacted1 ON incident.incident_id = impacted1.incident_id AND impacted1.dept_id = 1473
LEFT JOIN incident_impacted ON incident.incident_id = incident_impacted.incident_id
LEFT JOIN ops.department ON impacted.dept_id = ops.department.dept_id
LEFT JOIN ops.country ON incident_impacted.entity_id = ops.country.country_id
WHERE incident.status in ('OPEN','STABLE','ALL CLEAR','POST MORTEM COMPLETE')
AND incident.incident_id in (35880))
AS q1;

查询 2：

SELECT id, sev, duration, GROUP_CONCAT(DISTINCT department SEPARATOR ',') as department, GROUP_CONCAT(DISTINCT country SEPARATOR ',') as country
FROM
(SELECT impacted.incident_id AS id, impacted.severity_id as sev, ((UNIX_TIMESTAMP(incident.stable_time) - UNIX_TIMESTAMP(incident.start_time)) / 60) AS duration, ops.department.name AS department,
ops.country.name AS country
FROM incident
LEFT JOIN incident_impactedDepartments AS impacted ON incident.incident_id = impacted.incident_id
LEFT JOIN incident_impacted ON incident.incident_id = incident_impacted.incident_id
LEFT JOIN ops.department ON impacted.dept_id = ops.department.dept_id
LEFT JOIN ops.country ON incident_impacted.entity_id = ops.country.country_id
WHERE incident.status in ('OPEN','STABLE','ALL CLEAR','POST MORTEM COMPLETE')
AND incident.incident_id in (35880))
AS q1
GROUP BY department;

我希望上述查询之一应给出您期望的结果。