mysql - 连接两个表并计数

This question already has answers here:

Is it possible to count two columns in the same query
（4个答案）
5年前关闭。
我有这种结构
柯达数据

+------------+-----------+
| code | ket             |
+------------+-----------+
|20401 | code one        |
|21401 | code two        |
|22401 | code three      |
|etc   | etc             |
+------------------------+
+-----+-----------------+-----------+
| id  | code1           | code2     |
+-----+-----------------+-----------+
|1    | 20401           | 21402     |
|2    | 21401           | 22401     |
|3    | 22401           | 20401     |
|4    | 20401           | 21401     |
+-----------------------+-----------+

两次计数和求和如何实现如下输出
+-----------+-------------+-------------+ |code | count code1 | count code2 | +-----------+-------------+-------------+ |code one | 2 | 1 | |code two | 1 | 1 | |code three | 3 | 1 | |etc | etc | etc | +-----------+-------------+-------------+
基本上我想要的是选择表ket并在code1和code2中计数，这两个代码用一个查询hace代码。

最佳答案

可以执行两个连接：

SELECT    ket AS code,
          cnt1 AS "count code1"
          cnt2 AS "count code2"
FROM      kode
LEFT JOIN (SELECT   code1, COUNT(*) AS cnt1
           FROM     data
           GROUP BY code1) c1 ON kode.code = c1.code1
LEFT JOIN (SELECT   code2, COUNT(*) AS cnt2
           FROM     data
           GROUP BY code2) c2 ON kode.code = c2.code2