Mysql - 用 t2 行的计数更新 t1，其中两列与 t1 相同 [英] Mysql - update t1 with count of t2 rows where two columns are the same as for t1

问题描述

我有两个表 t1 和 t2，并希望获得输出 t1，并添加一个列，给出 t2 中的行数，其中 (id, category)存在.

I have two tables t1 and t2, and would like to get as output t1 with an added column giving the count of rows in t2 where (id, category) are present.

这是一个小数据集的例子:

Here is an example on a small data set:

CREATE TABLE IF NOT EXISTS `t1` (
  `key` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `category` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `t1` (`key`, `id`, `category`) VALUES
(1, 12, 101),
(2, 12, 104),
(3, 13, 102),
(4, 14, 101),
(5, 15, 102);


CREATE TABLE IF NOT EXISTS `t2` (
  `key` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `category` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `t2` (`key`, `id`, `category`) VALUES
(1, 12, 101),
(2, 12, 102),
(3, 13, 101),
(4, 13, 104),
(5, 12, 101),
(6, 15, 102);

这是我想要的输出，最后一列是所需的信息:

Here is the output I wish to have, the last column being the desired infos:

t1 updated
key, id, category, count_t2_id_category
1, 12, 101, 2     # because (12,101) appears 2 times in t2
2, 12, 104, 0     # because (12,104) appears 0 times in t2
3, 13, 102, 0     # etc
4, 14, 101, 0
5, 15, 102, 1

我尝试了以下命令开始，但它在输出中遗漏了一些 t1 行:

I tried the following command to start with, but it misses some t1 rows in the output:

SELECT *
FROM t1
LEFT OUTER JOIN t2 ON t1.id=t2.id AND t1.category = t2.category
GROUP BY t1.id

输出缺少 t1 键 #2:

output missing t1 key #2:

key id  category    key id  category    
1   12  101 1   12  101 
3   13  102 NULL    NULL    NULL    
4   14  101 NULL    NULL    NULL    
5   15  102 6   15  102 

推荐答案

由于您希望不匹配的行的值为零，因此对于 LEFT JOIN 来说这是一项工作，例如:

Since you want zero values for your non-matched rows, it's a work for LEFT JOIN, like:

SELECT 
  t1.*, 
  IF(t2.`key` IS NULL, 0, COUNT(t1.`key`)) AS t2_row_count 
FROM 
  t1 
    LEFT JOIN t2 
      ON t1.id=t2.id 
      AND 
      t1.category=t2.category 
GROUP BY 
  t1.`key`

我们计算 t1.key 因为对于匹配的行，它们在 first 表(而不是第二个)中是相同的 - 因此，我们应该按它分组 - 和不是按第二个表中的字段.

We're counting t1.key because for matched rows they will be same in first table (and not second) - thus, we should group by it - and not by field in second table.

提示:避免使用 mysql 保留字命名表/列.如果您不小心忘记了反引号，这将为您节省大量时间.

Tip: avoid to name your tables/columns with mysql reserved words. This will save you lots of time if you'll accidentally forget backticks.

这篇关于Mysql - 用 t2 行的计数更新 t1，其中两列与 t1 相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！