mysql - 使用 LEFT JOIN 结果更新表

Question

我需要一些关于 MySQL 查询的帮助，情况如下:

我有 2 个表，“phrases”和“authors”，我需要在 phrases_qt 字段中插入每个作者的短语数量，通过这个查询，我从每个表中获取短语数量:

SELECT a.name, COUNT( p.author_id ) 
FROM authors a
LEFT JOIN phrases p ON a.id = p.author_id
GROUP BY a.name, p.author_id

这给了我这个结果:

+-------------------+------------------+
|name               |COUNT(p.author_id)|
+-------------------+------------------+
|Albert Einstein    |12                |
|Bill Gates         |10                |
+-------------------+------------------+

那么我如何进行查询以将每个作者的“phrases_qt”字段更新为 COUNT 个结果？

编辑==(显示创建表)

显示创建表作者

CREATE TABLE `authors` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Author id',
 `thumbnail` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Author thumbnail image',
 `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Author name',
 `history` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT 'Author history',
 `phrases_qt` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`),
 KEY `id` (`id`),
 KEY `id_2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

显示创建表短语

CREATE TABLE `phrases` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Phrase ID',
 `text` varchar(250) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The phrase',
 `author_id` int(11) NOT NULL COMMENT 'Author ID',
 PRIMARY KEY (`id`),
 UNIQUE KEY `phrase` (`text`),
 KEY `author_id` (`author_id`),
 CONSTRAINT `phrases_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

我还没有测试过，但这应该可以工作:

UPDATE authors PA 
INNER JOIN (SELECT p.author_id, COUNT( p.author_id ) AS AuthorCount
FROM phrases p
GROUP BY p.author_id) tblA
ON tblA.author_id = PA.id
SET phrases_qt = AuthorCount