我怎樣才能避免重復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
uj5u.com熱心網友回復：
你只需要DISTINCT在里面GROUP_CONCAT()：
SELECT u.name,
GROUP_CONCAT(DISTINCT f.name) AS friends
................................................
請注意，SELECT DISTINCT ...這在您的查詢中沒有意義，因為您正在使用GROUP BY它為每個用戶回傳不同的行。
請參閱演示。
uj5u.com熱心網友回復：
你有不同的 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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/428251.html