mysql - 获取mysql中相关表的相关记录

Question

我有三个表ClaimHeader、ResClaim和ResActivity。 ClaimHeader 表的主键用作 ResClaim 表中的外键，ResClaim 表的主键用作 ResActivity 中的外键强>表。

下面是我的表格

ClaimHeader:

HeaderID  FileID    FileName
1         fileid1    file1.xml
2         fileid2    file2.xml
3         fileid3    file3.xml
4         fileid4    file4.xml
--------------------------------------------------
ResClaim:

ClaimPKID  HeaderPKID    ClaimDateSettlement
1          1             2017-04-08
2          1             2017-03-08
3          2             2017-04-10
4          3             2017-05-08
--------------------------------------------------
ResActivity:

ActivityPKID  ClaimPKID    ActivityNet
1             1             400
2             2             3000
3             2             2030
4             3             5000

Tables screenshot

ResClaim 表使用 HeaderPKID 作为 ClaimHeader 表的外键，ResActivity 表使用 ClaimPKID 作为 ResClaim 表中的外键

我的情况是我应该显示所有三个表中的相关记录。

例如，我想显示ClaimHeader表中的FileID、ResClaim表中的总声明计数以及ActivityNet的总和具有匹配条件的 ResActivity 表中的strong>。

我的预期结果是:

FileID   |  Total Claim(s) |  ActivityNet 
--------------------------------------------------
fileid2  |    1            |   5030 (3000+2030)
--------------------------------------------------

我尝试过以下查询:

SELECT 
  `ClaimHeader`.*, 
  count(ResClaim.ClaimPKID) as claims,  
  sum(ResActivity.ActivityNet) as net 
FROM 
  `ClaimHeader` 
  RIGHT OUTER JOIN `ResClaim` 
    ON ResClaim.ClaimPKID = ClaimHeader.HeaderID 
  RIGHT OUTER JOIN `ResActivity` 
    ON ResClaim.ClaimPKID = ResActivity.ActivityPKID

上述查询不返回相关记录值 - 相反，它返回 ResActivity 和 ResClaim 表中所有列的总和和计数。

Answer 1

目前还不太清楚你要问什么。看来您要么想要每个声明 header 一个结果行，要么每个文件一个结果行。因此，请按相关列进行分组。

通过加入所有记录，您当然可以获得声明 header 和多重声明，例如与

• 声明 header :head1、head2
• head1 的声明:声明 10、声明 11
• head2 的声明:声明 20、声明 21
• 权利要求 10 的操作:action100、action101
• 权利要求 11 的操作:action110、action111
• 权利要求 20 的操作:action200、action201

您从连接中得到这个中间结果:

header | claim   | action-------+---------+----------head1  | claim10 | action100head1  | claim10 | action101head1  | claim11 | action110head1  | claim11 | action111head2  | claim20 | action200...

Now by grouping per header, you get one aggregated result row for head1, one for head2. As the intermediate result contains one record per action, you can easily sum them. But as to claims: there are four records for head1, and if you do count(*) or count(claimpkid) you get a result of 4 (count(claimpkid) counts all claimpkid that are not null, which is always the case in this example). What you want to do instead is counting distinct claims (namly two here: claim10 and claim11). So use COUNT DISTINCT.

select 
  h.headerid, 
  count(distinct c.claimpkid) as claims,  
  coalesce(sum(a.activitynet), 0) as net 
from claimheader h
left outer join resclaim c on c.claimpkid = h.headerid 
left outer join resactivity a on a.activitypkid = c.claimpkid
group by h.headerid
order by h.headerid;

对于 header 没有声明或声明没有操作的情况，我使用左外连接(和 COALESCE)，对于这种情况，我们将显示零，而不是将它们从结果中删除。

如上所述，如果您希望每个文件都有此信息，请选择 fileid 并按其分组，而不是 headerid。