MySQL-多个联接表的求和计数 [英] Mysql - Summing counts of multiple joined tables

问题描述

我将尽力清楚地解释这一点.我正在尝试完成两件事，但即使遇到第一个无法正常工作的情况，也遇到了麻烦.

I will do my best to explain this clearly. I am trying to accomplish two things, but am having trouble even getting the first to work correctly.

我有一个架构，该架构具有一个成员表，该成员表具有指向多个表的外键.最后，我将从大约10个表中提取数据，这些表可能有也可能没有特定成员的记录.我正在尝试获得所有计数的总和.我的查询看起来像这样:

I have a schema that has a member table which has foreign keys to multiple tables. In the end I am going to be drawing from about 10 tables that may or may not have records for a particular member. I am trying to get the sum of all the counts. My query looks like this:

SELECT (COUNT(tb1.member_id) + COUNT(tb2.member_id)) as total
FROM members m
LEFT JOIN table_1 tb1 ON tb1.member_id = m.member_id
LEFT JOIN table_2 tb2 ON tb2.member_id = m.member_id
WHERE m.member_id = 27

其中27是我正在使用的测试帐户的member_id.这不会产生准确的结果，我认为这是由于左连接，似乎正在丢东西，尽管每种记录只有两个，但我总共得到了8个结果.如果我消除左连接之一，那么我会得到预期的结果.

Where 27 is the member_id of the test account I am working with. This doesn't produce accurate results and I believe it is because of the left join, it seems to be throwing things off and I am getting a total of 8 even though there are only two of each kind of record. If I eliminate one of the left joins then I get the expected result.

谁能告诉我该怎么做?

那是我的问题之一.第二个问题是，在某些情况下，我希望每个结果都计为1或0，即使有2或3个对应的记录也是如此.我一直在寻找类似将结果强制转换为bool的方法，但没有发现任何东西.有人可以建议一种方法吗?

That is part one of my problem. The second issue is that in some of these cases I will want each result to count as either 1 or 0, that is even if there are 2 or 3 corresponding records. I was looking for something like casting a result as a bool but have not found anything. Could anyone suggest a way to do this?

非常感谢您的阅读，任何建议将不胜感激.可能是我以错误的方式解决了这个问题，再次感谢您的任何建议.

Thanks much for reading, any advice would be very much appreciated. It could be that I am approaching this problem in the wrong way, again any advice is appreciated.

推荐答案

虽然我不熟悉mysql的艺术水平，但是我很确定这样的东西会起作用:

Eventhough i am not familiar with state of the art of mysql i am pretty sure something like this will work:

SELECT 
(select COUNT(*) from table_1 = where member_id = m.member_id) 
+ 
(select COUNT(*) from table_2 = where member_id = m.member_id)
as total

FROM members m
WHERE m.member_id = 27

这篇关于MySQL-多个联接表的求和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！