笔记大全

Mysql逗号拼接字符串的关联查询及统计问题怎么解决

更新时间：2023-12-11

问题描述

在MySQL中，当需要将多个字段中的值进行拼接并以逗号分隔时，通常采用 GROUP_CONCAT() 函数实现。但是如果需要进行关联查询，就会出现若干个重复的值，使得最终的拼接结果出现失误。那么如何处理这个关联查询的问题，并能够正确的统计字符串的数量呢？

解决方法

要解决这个问题，最简单的方法是分别使用 GROUP BY 和 DISTINCT 关键词对字段进行去重，可以避免重复记录的情况发生。

SELECT 
    a.id, 
    GROUP_CONCAT(DISTINCT b.name) AS names,
    COUNT(DISTINCT b.name) AS name_count
FROM table_a AS a 
LEFT JOIN table_b AS b ON a.id = b.table_a_id
GROUP BY a.id;

上面的例子中，我们对表 a 和 b 进行关联查询，通过 GROUP_CONCAT() 函数将表 b 中的 name 字段进行拼接，并使用 DISTINCT 去重来避免多次出现的值。COUNT() 函数也加入DISTINCT关键字进行区分。通过 GROUP BY 分组，对每个表 a 的记录进行统计。

代码示例

假设我们有两张表 t1 和 t2，其结构示意图如下：

-- 表 t1
CREATE TABLE IF NOT EXISTS t1 (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
) DEFAULT CHARSET=utf8;

-- 表 t2
CREATE TABLE IF NOT EXISTS t2 (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    t1_id INT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL
) DEFAULT CHARSET=utf8;

在表 t1 中，我们随机插入了 5 条记录。在表 t2 中，我们对 t1 中的每条记录插入了 3 条相关记录。如下：

INSERT INTO t1 (id, name) VALUES (1, 'One');
INSERT INTO t1 (id, name) VALUES (2, 'Two');
INSERT INTO t1 (id, name) VALUES (3, 'Three');
INSERT INTO t1 (id, name) VALUES (4, 'Four');
INSERT INTO t1 (id, name) VALUES (5, 'Five');

INSERT INTO t2 (id, t1_id, name) VALUES (1, 1, 'Grapes');
INSERT INTO t2 (id, t1_id, name) VALUES (2, 1, 'Orange');
INSERT INTO t2 (id, t1_id, name) VALUES (3, 1, 'Apple');

INSERT INTO t2 (id, t1_id, name) VALUES (4, 2, 'Pear');
INSERT INTO t2 (id, t1_id, name) VALUES (5, 2, 'Orange');
INSERT INTO t2 (id, t1_id, name) VALUES (6, 2, 'Banana');

INSERT INTO t2 (id, t1_id, name) VALUES (7, 3, 'Lemon');
INSERT INTO t2 (id, t1_id, name) VALUES (8, 3, 'Apple');
INSERT INTO t2 (id, t1_id, name) VALUES (9, 3, 'Strawberry');

INSERT INTO t2 (id, t1_id, name) VALUES (10, 4, 'Pineapple');
INSERT INTO t2 (id, t1_id, name) VALUES (11, 4, 'Orange');
INSERT INTO t2 (id, t1_id, name) VALUES (12, 4, 'Mango');

INSERT INTO t2 (id, t1_id, name) VALUES (13, 5, 'Kiwi');
INSERT INTO t2 (id, t1_id, name) VALUES (14, 5, 'Banana');
INSERT INTO t2 (id, t1_id, name) VALUES (15, 5, 'Orange');

现在我们想要对表 t1 进行拼接求和，并统计拼接后的数量：

SELECT 
    t1.id, 
    t1.name, 
    GROUP_CONCAT(DISTINCT t2.name SEPARATOR ', ') AS names,
    COUNT(DISTINCT t2.name) AS name_count 
FROM t1 
LEFT JOIN t2 ON t1.id = t2.t1_id 
GROUP BY t1.id;

结果如下：

| id | name  | names                      | name_count |
|----|-------|----------------------------|------------|
| 1  | One   | Grapes, Orange, Apple      | 3          |
| 2  | Two   | Pear, Orange, Banana       | 3          |
| 3  | Three | Lemon, Apple, Strawberry   | 3          |
| 4  | Four  | Pineapple, Orange, Mango   | 3          |
| 5  | Five  | Kiwi, Banana, Orange       | 3          |

总结

由于在关联查询中 GROUP_CONCAT() 函数会出现重复问题，我们可以通过 GROUP BY 和 DISTINCT 来避免重复值的出现，正确的统计出字段中字符串的数量。在实际开发中，我们可以灵活运用上述方法解决涉及到字符串拼接和统计的各种问题。

c语言编程笔录