MySQL从另一个表连接计数匹配的记录 [英] MySQL joins count matched record from another table

问题描述

我有2个表，第一个表希望它显示所有结果，没有"where"或任何限制它的东西.

What I have is 2 tables, the first table I want it to display all results, no "where" or anything to limit it.

第二个表我想将一个ID与第一个表匹配，它可以有多个引用它的行，所以我想对数字进行计数.

The second table I want to match an id to the first table, it can have multiple rows referencing it so I want to count the number.

所以可以说第一个表是这样的:

So lets say the first table is like this:

ID - name
1  - one
2  - two
3  - three
4  - four

第二张桌子是这样的

ID - REF
1  - 1
2  - 1
3  - 2
4  - 2
5  - 3
6  - 3
7  - 4
8  - 4

我想像这样合并它们:

ID - name - count
1  - one  - 2
2  - two  - 2
3  - three- 2
4  - four - 2

我尝试使用子查询，左联接，右联接，内部联接，子查询联接，分组，并且在十分之九的情况下，我得到了应该得到的1300个结果中的第一个ID的20个结果.其余的我只得到一个错误的计数，没有名字.

I have tried using subqueries, left joins, right joins, inner joins, sub query joins, grouping and 9 times out of ten I get 20 results of the first ID out of 1300 results I should get. The rest I only get an incorrect count and no name.

我觉得这是MySQL 101，但是尝试了多种变体却一无所获之后，我觉得肯定缺少某些东西.

I feel this is MySQL 101 but after attempting multiple variations and coming up with nothing I feel there must be something I am missing.

我很乐意针对一个处于完全相同情况的问题(2小时的查询，没有任何事情可以像这样工作)或一个简单的查询来指出这种方法的逻辑，在此先谢谢您对任何回答的人来说，您将成就我的一天.

I would be happy to be directed to a question that is in the exact same situation (2 hours of looking and nothing that works exactly like this) Or a simple query to point out the logic of this method, Thanks in an advance to anyone that answers, you will have made my day.

如果需要任何其他信息，请告诉我，我故意遗漏了该查询，因为我已经对其进行了多次修改，以至于它没有太大的相关性(我必须列出我尝试过的每个查询，而那样做会很遥远.进行大量滚动)

If any additional information is needed let me know, I have left out the query deliberately because I have adapted it so many times that it will not have much relevance (I would have to list every query I tried and that would be far to much scrolling)

好吧，我已经测试了第一个答案，并且似乎可以在这种情况下使用，所以我将扩大答案，这个问题将得到答案"，因此，如果没有答复，这只是一个扩充，我将用答案将其结束如下:

Ok I have tested the first and answer and it seemed to work in this context so I will expand my answer, the question is "answered" so this is just an expansion if there are no replies I will close this with the answer as follows:

SELECT t.id, t.name, count(*) AS suppliers 
FROM #__tiresku AS t 
LEFT JOIN #__mrsp AS m ON t.name = m.tiresku_id 
GROUP BY t.id, t.name

扩展是一个内部联接，我还有另一个表，该表更多是列表，它具有ID和名称，仅此而已，我使用ID引用该表以获取名称".

The expansions is an inner join, I have another table that is more of a list, it has an id and a name and that's it, I reference that table with an id to get the "name" instead.

与连接(例如外键之类)相比，这可能有更好的选择.

This might have a better option then joins (like foreign keys or something).

我已将此添加到选择b.name AS brand_name

还有一个联接INNER JOIN #__brands AS b ON t.brand = b.id

使用子查询而不是加入

推荐答案

这是具有聚合功能的基本联接:

This is a basic join with aggregation:

select t1.id, t1.name, count(*) as `count`
from table1 t1 join
     table2 t2
     on t1.id = t2.ref
group by t1.id, t1.name;

这篇关于MySQL从另一个表连接计数匹配的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！