计算第二个表中外键数量并与第一个表中的行一起显示的有效方法 - PHP - MySQL [英] Efficient way to calculate number of foreign keys in second table and display it with rows from first table - PHP - MySQL
本文介绍了计算第二个表中外键数量并与第一个表中的行一起显示的有效方法 - PHP - MySQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个表 tablea
和 tableb
如下所示;
I have two tables tablea
and tableb
like below;
表格
+--------+-------+-------+-------+------+
| fa | fb | fc | fd | fe |
+--------+-------+-------+-------+------+
| col1 | f11 | f12 | f13 | x1 |
+--------+-------+-------+-------+------+
| col2 | f21 | f22 | f23 | x2 |
+--------+-------+-------+-------+------+
| col3 | f31 | f32 | f33 | x3 |
+--------+-------+-------+-------+------+
| col4 | f41 | f42 | f43 | x4 |
+--------+-------+-------+-------+------+
tableb
+--------+-------+-------+-------+------+
| tbba | tbbb | tbbc | tbbd | tbbe |
+--------+-------+-------+-------+------+
| cola | fa1 | fa2 | fa3 | x1 |
+--------+-------+-------+-------+------+
| colb | fb1 | fb2 | fb3 | x1 |
+--------+-------+-------+-------+------+
| colc | fc1 | fc2 | fc3 | x1 |
+--------+-------+-------+-------+------+
| cold | fd1 | fd2 | fd3 | x2 |
+--------+-------+-------+-------+------+
| cole | fe1 | fe2 | fe3 | x2 |
+--------+-------+-------+-------+------+
| colf | ff1 | ff2 | ff3 | x3 |
+--------+-------+-------+-------+------+
| colg | fg1 | fg2 | fg3 | x3 |
+--------+-------+-------+-------+------+
| colh | fh1 | fh2 | fh3 | x3 |
+--------+-------+-------+-------+------+
| coli | fi1 | fi2 | fi3 | x3 |
+--------+-------+-------+-------+------+
| colj | fj1 | fj2 | fj3 | x4 |
+--------+-------+-------+-------+------+
我想生成一个表格;
+--------+-------+-----+
| col1 | f11 | 3 |
+--------+-------+-----+
| col2 | f21 | 2 |
+--------+-------+-----+
| col3 | f31 | 4 |
+--------+-------+-----+
| col4 | f41 | 1 |
+--------+-------+-----+
数字表示tablea
的fe
字段中的条目出现在tableb
中的次数.目前,我正在从 tablea
中获取所有行,并且在每个循环中,我使用另一个查询来获取 tableb
中的行数,使用 tablea
的 fe
字段.因此,我有 1 个主查询和 4 个子查询.有没有什么有效的方法,比如加入什么的?
The number denotes the times where entry in fe
field of tablea
appears in tableb
. Currently, I am fetching all rows from the tablea
, and in each loop, I use another query to get number of rows in tableb
, using tablea
's fe
field. Thus, I have 1 main query, and 4 sub queries. Is there any efficient way like join or something?
谢谢
推荐答案
试试这个:
SELECT a.fa , a.fb , COUNT( b.tbbe)
FROM tablea a
LEFT JOIN tableb b ON a.fe = b.tbbe
GROUP BY a.fa
这篇关于计算第二个表中外键数量并与第一个表中的行一起显示的有效方法 - PHP - MySQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文