SQL 选择 count(*) 并在 3 个表上“加入”

Select count(*) and "join" over 3 tables

提问by Sebastian

I am looking for a count query within a join of 3 tables that can get me counts on distinct values of one of these tables.

我在 3 个表的连接中寻找计数查询，它可以让我对这些表之一的不同值进行计数。

I have 3 tables that I need to join to get the expected data (Workflow, Messageand Message_Workflow_Relation).

我需要加入 3 个表才能获得预期的数据 ( Workflow,Message和Message_Workflow_Relation)。

I want to get the count of workflows grouped by status + one field of the connected Messagetable in my result (related_name). The related name should be taken from entries where the adapterfield equals wf, however there are sometimes more than one Message record that matches this condition, which will result in more datasets in my count then the ones that are really there.

我想Message在我的结果 ( related_name) 中获取按状态分组的工作流数 + 连接表的一个字段。相关名称应取自adapter字段等于 的条目wf，但是有时匹配此条件的 Message 记录不止一个，这将导致我统计的数据集比真正存在的数据集多。

I am pretty sure it must be possible to sort out the , but just don't get it working. Sadly I cannot change the table structure as it's a given schema of a product we use.

我很确定一定有可能整理出 ，但只是不要让它工作。遗憾的是，我无法更改表结构，因为它是我们使用的产品的给定架构。

My table structure looks like this:

我的表结构如下所示：

Workflow:

工作流程：

id | workflow_id | starttime | endtime | status
------------------------------------------------------
1  |          22 |         0 |     200 |     OK
2  |          23 |       220 |     920 |  ERROR
3  |          55 |       202 |     588 |     OK

Message_Workflow_Relation:

消息_工作流_关系：

id | message_id | workflow_id |
-------------------------------
1  |        122 |          22 |
2  |        235 |          22 |
3  |        456 |          22 |
4  |        982 |          22 |
5  |        444 |          23 |
6  |        445 |          23 |
7  |        585 |          55 |
8  |        738 |          55 |
9  |        399 |          55 |

Message:

信息：

id | message_id | starttime | endtime | adapter | related_name |
----------------------------------------------------------------
 1 |        122 |         0 |    2335 |      wf |   workflow_1 |
 2 |        235 |       222 |    1000 |   other |        other |
 3 |        456 |       343 |    2330 | another |      another |
 4 |        982 |       222 |    2200 |      wf |   workflow_1 |
 5 |        444 |      2223 |    3333 |      wf |   workflow_2 |
 6 |        445 |      1123 |    1244 |  manual |       manual |
 7 |        585 |      5555 |    5566 |      wf |   workflow_1 |
 8 |        738 |       655 |     999 |      wf |   worfklow_1 |
 9 |        399 |      6655 |    7732 | another |      another |

This should return the following result:

这应该返回以下结果：

count(*) | related_name | status |
----------------------------------
       2 |   workflow_1 |     OK |
       1 |   workflow_2 |  ERROR |

I am stuck with this following statement, but I am not sure how to make the selection on the adapter = wf uniquefor each workflow:

我坚持以下声明，但我不确定如何adapter = wf unique为每个工作流程进行选择：

select distinct
  count(*),
  m.related_name,
  w.status
from
  workflow as w,
  message as m,
  msg_bpm_rel as rel
where rel.workflow_id = w.workflow_id
  and rel.message_id = m.message_id
  and m.adapter = 'PE'
group by m.related_name,w.status

This returns me (4 workflow_1instead of 2):

这使我返回（4workflow_1而不是 2）：

count(*) | related_name | status |
----------------------------------
       4 |   workflow_1 |     OK |
       1 |   workflow_2 |  ERROR |

How can I make a correct query to achieve this?

如何进行正确的查询来实现这一目标？

Any help appreciated.

任何帮助表示赞赏。

回答by F.B. ten Kate

You can do this by grouping and counting a distinct value.

您可以通过对不同的值进行分组和计数来做到这一点。

So something like:

所以像：

select count(distinct w.workflow_id), m.related_name,w.status 
from workflow as w, message as m, msg_bpm_rel as rel 
where rel.workflow_id = w.workflow_id and rel.message_id = m.message_id 
and m.adapter = 'PE' 
group by m.related_name, w.status

This is untested but should work i believe :)

这是未经测试的，但我相信应该可以工作:)

回答by Stephen Perelson

My first attempt at getting the query to work. I don't like having to use the distinct. It makes me think there might still be something wrong:

我第一次尝试让查询工作。我不喜欢必须使用不同的。这让我觉得可能仍然有问题：

SELECT distinct theCount
      ,m.related_name
      ,w.status
FROM workflow as w
    ,message as m
    ,msg_bpm_rel as rel
    ,(SELECT count(1) as theCount
            ,w.workflow_id as wf_id
      FROM workflow as w
          ,message as m
          ,msg_bpm_rel as rel 
      WHERE rel.workflow_id = w.workflow_id 
      AND   rel.message_id = m.message_id 
      AND   m.adapter = 'wf' 
      GROUP BY w.workflow_id) AS t

WHERE t.wf_id = w.workflow_id 
AND   rel.workflow_id = w.workflow_id 
AND   rel.message_id = m.message_id 
AND   m.adapter = 'wf'

What is important to note is how the count is performed in this query. You were simply grouping on two columns that happened to be in the SELECT part of the SQL statement. The reality is that to count what you wanted, you have to group by the workflow ID only. This query does that and then feeds that result into another query to display what you wanted.

需要注意的重要一点是在此查询中如何执行计数。您只是对碰巧位于 SQL 语句的 SELECT 部分的两列进行分组。现实情况是，要计算您想要的数量，您必须仅按工作流 ID 进行分组。此查询执行此操作，然后将该结果提供给另一个查询以显示您想要的内容。

回答by Sukhdeep Singh

    SELECT distinct theCount
    ,m.related_name
    ,w.status
    FROM workflow as w
    ,message as m
    ,msg_bpm_rel as rel
    ,(SELECT count(1) as theCount
    ,w.workflow_id as wf_id
    FROM workflow as w
    ,message as m
    ,msg_bpm_rel as rel 
    WHERE rel.workflow_id = w.workflow_id 
    AND   rel.message_id = m.message_id 
    AND   m.adapter = 'wf' 
    GROUP BY w.workflow_id) AS t
    WHERE t.wf_id = w.workflow_id 
    AND   rel.workflow_id = w.workflow_id 
    AND   rel.message_id = m.message_id 
    AND   m.adapter = 'wf'