如何从子查询中的不同表中获取特定行数 [英] How to get number of specific rows from a different table in a subquery

问题描述

我知道有可能，但是我没有足够的经验来知道如何执行子查询.
情况如下:

I know it's possible, but I'm not experienced enough to know how to do subqueries.
Here's the situation:

Table 1:
+--------------------+--------------------+
|               v_id |             v_name |
+--------------------+--------------------+
|                  1 |            v_name1 |
+--------------------+--------------------+
| etc...

Table 2:
+--------------------+--------------------+
|               a_id |             a_name |
+--------------------+--------------------+
|                  1 |            a_name1 |
+--------------------+--------------------+
| etc...

Table 3:
+--------------------+--------------------+
|               v_id |               a_id |
+--------------------+--------------------+
|                  1 |                  1 |
+--------------------+--------------------+
|                  1 |                  2 |
+--------------------+--------------------+
|                  1 |                  3 |
+--------------------+--------------------+
|                  2 |                  3 |
+--------------------+--------------------+
|                  2 |                  1 |
+--------------------+--------------------+

我认为这是一种很常见的情况.
因此，我在Table 1和Table 2中具有唯一的条目.
我想SELECT从Table 1的所有行，并获取(作为每行的最后一个单元格)具有Table 3中的相应值的行数.

I believe this is a quite common situation.
So, I have unique entries in Table 1 and Table 2.
I want to SELECT all rows from Table 1 and get (as the last cell in each row) the number of rows with the corresponding value in Table 3.

这不起作用:

SELECT t1.* , COUNT(SELECT t3.* FROM  `table_3` t3 WHERE t3.v_id = t1.v_id) as entries
FROM  `table 1` t1;

我敢肯定，这里的专家会告诉我，这都是错误的，但是坦率地说，这就是我想要的(还有一些有用的解决方案！). ;)

I'm sure I'm gonna be told off by experts here that it's all wrong, but frankly, that's what I'm looking for (and some helpful solution as well!). ;)

推荐答案

使用:

   SELECT t1.*,
          COALESCE(x.num_rows, 0) AS entries
     FROM `table 1` t1
LEFT JOIN (SELECT t3.v_id,
                  COUNT(*) 'num_rows'
             FROM `table_3` t3
         GROUP BY t3.v_id) x ON x.v_id = t1.v_id

这篇关于如何从子查询中的不同表中获取特定行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！