mysql - 根据另一个表的每条记录计算 MySQL 表中的记录

Question

我在 MySQL 数据库中有两个表 -

table_a：

+----+---------+-------------+-----------------
| id | section | sub_section | ...other_fields
+----+---------+-------------+-----------------
| 1  | A       | X           |
| 2  | A       | Y           |
| 3  | A       | Z           |
| 4  | B       | P           |
| 5  | B       | Q           |
| 6  | C       | L           |
| 7  | C       | M           |
| 8  | C       | N           |
| 9  | C       | O           |
+----+---------+-------------+-----------------

table_b：

+----+-------------+---------+-----------------
| id | sub_section | b_count | ...other_fields
+----+-------------+---------+-----------------
| 1  | X           | 1       |
| 2  | Y           | 1       |
| 3  | L           | 0       |
| 4  | P           | 1       |
| 5  | P           | 1       |
| 6  | X           | 0       |
| 7  | M           | 1       |
| 8  | Y           | 0       |
| 9  | Q           | 1       |
+----+-------------+---------+-----------------

我想从表 B 中找到每个不同in的sub_sectionin计数table_b和总和-b_countsectiontable_a

预期结果：

+---------+--------------------+--------------+
| section | COUNT(sub_section) | SUM(b_count) |
+---------+--------------------+--------------+
| A       | 4                  | 2            |
| B       | 3                  | 3            |
| C       | 2                  | 1            |
+---------+--------------------+--------------+

一种方法是运行 Count(section) 数量的查询，然后组合结果。

就像是：

SELECT 'A' AS section, COUNT(sub_section), SUM(b_count) FROM table_b WHERE sub_section IN (SELECT DISTINCT sub_section FROM table_a WHERE section='A')
UNION
SELECT 'B' AS section, COUNT(sub_section), SUM(b_count) FROM table_b WHERE sub_section IN (SELECT DISTINCT sub_section FROM table_a WHERE section='B')
UNION
SELECT 'C' AS section, COUNT(sub_section), SUM(b_count) FROM table_b WHERE sub_section IN (SELECT DISTINCT sub_section FROM table_a WHERE section='C');

有没有更好的方法在查询中做到这一点？

中的section列表table_a是动态的并且可能会更改，我不想在每次值更改时更新我的​​查询。

标签： mysqlsql

Answer 1

SELECT t1.section, COUNT(DISTINCT t1.sub_section), SUM(t2.b_count)
FROM table_a t1
LEFT JOIN  table_b t2 USING (sub_section)
GROUP BY t1.section;

小提琴