如何避免在 friends 上重複，我仍然得到兩個 bob，而不是只有一個 bob
我的桌子設定：
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255)
);
INSERT INTO users (id, name)
VALUES (1, "Gregor"),
(2, "Liza"),
(3, "Matt"),
(4, "Tim"),
(5, "Lance"),
(6, "Bob");
CREATE TABLE committee(
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
friend_id INT,
member_id INT,
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
FOREIGN KEY (`friend_id`) REFERENCES `users` (`id`),
FOREIGN KEY (`member_id`) REFERENCES `users` (`id`)
);
INSERT INTO committee (user_id, friend_id, member_id)
VALUES (3, 5, 1),
(4, 5, 1),
(3, 6, 2),
(3, 6, 2),
(4, 6, 2);
我使用的查詢：
SELECT DISTINCT u.name,
GROUP_CONCAT(f.name) AS friends
FROM committee c
INNER JOIN users u ON (u.id = c.user_id)
INNER JOIN committee c2 ON c2.user_id = c.user_id
INNER JOIN users AS f ON (f.id = c2.friend_id)
WHERE (c.member_id = 1)
GROUP BY u.id;
當前結果：
name friends Matt Lance,Bob,Bob Tim Lance,Bob
我的期望：
name friends Matt Lance,Bob Tim Lance,Bob
您的 u.name 與 f.name 不同
試試這個
SELECT u.name, GROUP_CONCAT(distinct f.name) AS friends FROM committee c INNER JOIN users u ON (u.id = c.user_id) INNER JOIN committee c2 ON c2.user_id = c.user_id INNER JOIN users AS f ON (f.id = c2.friend_id) WHERE (c.member_id = 1) GROUP BY u.name;您只需要在
GROUP_CONCAT()中使用DISTINCT：SELECT u.name, GROUP_CONCAT(DISTINCT f.name) AS friends ................................................請注意，
SELECT DISTINCT ...在您的查詢中沒有意義，因為您使用的是GROUP BY，它為每個使用者傳回不同的行。查看演示。