选择计数（*）和“加入”超过3个表 [英] Select count(*) and "join" over 3 tables

问题描述

我有3个表，每个表都有一个表。我需要加入以获得预期的数据（工作流，消息和 Message_Workflow_Relation ）。

我想得到按状态分组的工作流计数+连接的消息的一个字段在我的结果（ related_name ）。相关名称应取自适配器字段等于 wf 的条目，但有时会有多个消息记录匹配这个条件，这将导致更多的数据集在我的计数，然后是真正存在的那些。

我很肯定必须能够整理出来，但只是不能得到它的工作。
很遗憾，我不能改变表结构，因为它是我们使用的产品的给定模式。

我的表结构如下：

工作流程：

  workflow_id | starttime | endtime |状态
 --------------------------------------------- --------- 
 1 | 22 | 0 | 200 | OK 
 2 | 23 | 220 | 920 |错误
 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 | 
  

讯息：

  id | message_id | starttime | endtime |适配器| related_name | 
 ---------------------------------------------- ------------------ 
 1 | 122 | 0 | 2335 | wf | workflow_1 | 
 2 | 235 | 222 | 1000 |其他|其他| 
 3 | 456 | 343 | 2330 |另一个另一个| 
 4 | 982 | 222 | 2200 | wf | workflow_1 | 
 5 | 444 | 2223 | 3333 | wf | workflow_2 | 
 6 | 445 | 1123 | 1244 |手册|手册| 
 7 | 585 | 5555 | 5566 | wf | workflow_1 | 
 8 | 738 | 655 | 999 | wf | worfklow_1 | 
 9 | 399 | 6655 | 7732 |另一个|另一个| 
  

这应该返回以下结果：

  count（*）| related_name |状态| 
 ---------------------------------- 
 2 | workflow_1 | OK | 
 1 | workflow_2 |错误| 
  

我遇到这个下面的语句，但我不知道如何
为每个工作流选择 adapter = wf unique ：

  
 count（*），
 m.related_name，
 w.status 
从
工作流作为w，
消息为m，
 msg_bpm_rel as rel 
其中rel.workflow_id = w.workflow_id 
和rel.message_id = m.message_id 
和m.adapter ='PE'
 group by m.related_name，w。 status 
  

这会返回我（4 workflow_1 of 2）：

  count（*）| associated_name |状态| 
 ---------------------------------- 
 4 | workflow_1 | OK | 
 1 | workflow_2 |错误| 
  

如何才能做出正确的查询？

任何帮助感谢。

解决方案

您可以通过分组和计数一个不同的值。

像这样：

  select count（distinct w.workflow_id） related_name，w.status 
从工作流as w，消息为m，msg_bpm_rel为rel 
其中rel.workflow_id = w.workflow_id和rel.message_id = m.message_id 
和m.adapter = 'PE'
 group by m.related_name，w.status 
  

这是未经测试，工作我相信：）

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.

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

I want to get the count of workflows grouped by status + one field of the connected Message table in my result (related_name). The related name should be taken from entries where the adapter field 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.

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 unique for each workflow:

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_1 instead of 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.

解决方案

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 :)

这篇关于选择计数（*）和“加入”超过3个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！