mysql - select * from table where id in (select group_concat(id) from table ) 如何使用 group_concat 函数 IN IN() 实现批量和新数据？

Question

在此处输入图像描述SELECT * FROM userWHERE id IN (select group_concat(id) from userGROUP BY sex) ;

我想要以下结果

UPDATE `user` SET average_age =  (SELECT AVG(score) FROM `score` WHERE id IN (select user_id from `score`))  

SELECT AVG(score) FROM userWHERE id IN (1,2,3) ; SELECT AVG(score) FROM userWHERE id IN (2,5) ; ……

如何使用 group_concat 函数 IN IN() 实现批量和新数据？

数据 ：

  SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'man', 21, 'Tom', 300);
INSERT INTO `user` VALUES (2, 'man', 23, 'Jerry', 350);
INSERT INTO `user` VALUES (3, 'woman', 18, 'Mary', 400);
INSERT INTO `user` VALUES (4, 'woman', 12, 'Lily', 450);
INSERT INTO `user` VALUES (5, 'woman', 16, 'Jasmine', 320);
INSERT INTO `user` VALUES (6, 'woman', 16, 'Jasmine', 420);
INSERT INTO `user` VALUES (7, 'woman', 18, 'Violet', 500);
INSERT INTO `user` VALUES (8, 'woman', 18, 'Violet', 580);
INSERT INTO `user` VALUES (9, 'woman', 20, 'Sophia', 520);
INSERT INTO `user` VALUES (10, 'man', 22, 'Charlotte', 450);
INSERT INTO `user` VALUES (11, 'man', 18, 'Jackson', 400);

SET FOREIGN_KEY_CHECKS = 1;

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `average_score` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, '1,2,3', NULL);
INSERT INTO `score` VALUES (2, '2,5', NULL);
INSERT INTO `score` VALUES (3, '1,3,4,5', NULL);
INSERT INTO `score` VALUES (4, '2,3,6,8,11', NULL);
INSERT INTO `score` VALUES (5, '2,3,6', NULL);
INSERT INTO `score` VALUES (6, '2,3,4,5,6,7', NULL);
INSERT INTO `score` VALUES (7, '2,3,4,5,6,7', NULL);
INSERT INTO `score` VALUES (8, '3,4,5,9,10', NULL);
INSERT INTO `score` VALUES (9, '2', NULL);
INSERT INTO `score` VALUES (10, '3,5,8,9,10', NULL);
INSERT INTO `score` VALUES (11, '3,5,8,9,10', NULL);

SET FOREIGN_KEY_CHECKS = 1;

标签： mysql

Answer 1

首先，我想纠正问题标题所表明的一个基本误解

SELECT * 
FROM user 
WHERE name IN (select group_concat(name) from user GROUP BY sex)

（我已将 id 更改为 name 以更好地说明我的观点）

此查询在概念上等同于：

SELECT * 
FROM user 
WHERE name IN (
  'John,Tim,Dave',
  'Sarah,Sally,Anne'
)

这是合法的语法，但几乎可以肯定不是你想要的。您不会通过像上面那样提供由逗号分隔的值的字符串来编写使用 IN 的查询，而是向 IN 提供这样的值列表：

SELECT * 
FROM user 
WHERE name IN (
  'John','Tim','Dave',
  'Sarah','Sally','Anne'
)

这是一个微妙但本质上巨大的差异，如果您没有看到，请给我留言

您的 in 查询应该只是您要提供给 IN 的各个值的查询输出列表，而不是 CSV 字符串或 CSV 字符串列表。因此，您的查询应该是：

SELECT * 
FROM user 
WHERE name IN (
  select name from othertable...
)

现在对于您的实际问题，您似乎想用另一个表中的摘要数据更新一个表：

UPDATE 
  `user` u 
  INNER JOIN
  (SELECT user_id, AVG(score) avgscore FROM `score` GROUP BY user_id)  a
  ON u.id = a.user_id
SET
  u.average_age = a.avgscore

我们按人创建分数摘要，将其加入用户表并更新用户表

但我会提醒您不要这样做，因为您存储的数据可能会不同步。无需将平均值存储在用户表中，只需将数据保存在分数表中，并在每次需要时重新计算平均值：

SELECT *
FROM
  `user` u 
  INNER JOIN
  (SELECT user_id, AVG(score) avgscore FROM `score` GROUP BY user_id)  a
  ON u.id = a.user_id

这也可以写成：

SELECT u.*, AVG(s.score) as avgscore
FROM
  `user` u 
  INNER JOIN
  `score` s
  ON u.id = s.user_id
GROUP BY 
  <list of columns in u>

我倾向于避免这种模式，倾向于“首先在子查询中分组，然后加入”，因为如果您有两个表要加入并汇总第三个表，则可以通过导致值重复来更容易地破坏第二个表的统计信息。相比之下，作为 subquwery 的分组提供了连接，其中 a 中只有一行与 b、c 等中的一行匹配。哦，按块编写分组可能真的很繁琐/无聊