php - MySQL查询显示问题

Question

我有这个查询，应该显示与某个相关的所有帖子特定标签，但由于某种原因它只会显示一个结果而不是所有结果有人可以帮助我解决这个问题吗？

这是 MySQL 代码。

"SELECT users.*, users_posts.*, tags.*, posts_tags.*, 
FROM users
INNER JOIN users_posts ON users_posts.user_id = users.user_id 
INNER JOIN posts_tags ON users_posts.id = posts_tags.posts_id
INNER JOIN tags ON posts_tags.tag_id = tags.id
WHERE tags.tag = '" . $tag_id . "'
GROUP BY tags.tag"

这是我的 MySQL 表。

CREATE TABLE posts_tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag_id INT UNSIGNED NOT NULL,
users_posts_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);


CREATE TABLE users_posts (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
content TEXT NOT NULL,
PRIMARY KEY (id)
);


CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
pass CHAR(40) NOT NULL,
PRIMARY KEY (user_id)
);

Answer 1

我在您的表中添加了一些测试数据:

INSERT INTO tags VALUES (1, 'mysql');
INSERT INTO tags VALUES (2, 'javascript');
INSERT INTO tags VALUES (3, 'c++');

INSERT INTO users VALUES (1, 'user a', 'pass');
INSERT INTO users VALUES (2, 'user b', 'pass');
INSERT INTO users VALUES (3, 'user c', 'pass');

INSERT INTO users_posts VALUES (1, 1, 'some content 1');
INSERT INTO users_posts VALUES (2, 1, 'some content 2');
INSERT INTO users_posts VALUES (3, 2, 'some content 3');
INSERT INTO users_posts VALUES (4, 2, 'some content 4');
INSERT INTO users_posts VALUES (5, 2, 'some content 5');
INSERT INTO users_posts VALUES (6, 3, 'some content 6');

INSERT INTO posts_tags VALUES (1, 1, 1);
INSERT INTO posts_tags VALUES (2, 2, 1);
INSERT INTO posts_tags VALUES (3, 1, 2);
INSERT INTO posts_tags VALUES (4, 3, 2);
INSERT INTO posts_tags VALUES (5, 2, 3);
INSERT INTO posts_tags VALUES (6, 2, 4);
INSERT INTO posts_tags VALUES (7, 3, 4);
INSERT INTO posts_tags VALUES (8, 1, 5);
INSERT INTO posts_tags VALUES (9, 2, 6);
INSERT INTO posts_tags VALUES (10, 3, 6);

然后删除GROUP BY:

SELECT      *
FROM        users
INNER JOIN  users_posts ON users_posts.user_id = users.user_id
INNER JOIN  posts_tags ON users_posts.id = posts_tags. users_posts_id
INNER JOIN  tags ON posts_tags.tag_id = tags.id
WHERE       tags.tag = 'mysql';

返回:

+---------+--------+------+----+---------+----------------+----+--------+----------------+----+-------+
| user_id | name   | pass | id | user_id | content        | id | tag_id | users_posts_id | id | tag   |
+---------+--------+------+----+---------+----------------+----+--------+----------------+----+-------+
|       1 | user a | pass |  1 |       1 | some content 1 |  1 |      1 |              1 |  1 | mysql |
|       1 | user a | pass |  2 |       1 | some content 2 |  3 |      1 |              2 |  1 | mysql |
|       2 | user b | pass |  5 |       2 | some content 5 |  8 |      1 |              5 |  1 | mysql |
+---------+--------+------+----+---------+----------------+----+--------+----------------+----+-------+
3 rows in set (0.00 sec)

只要帖子没有多次使用相同标签标记，每个帖子就只有一行。事实上，为了防止这种情况发生，您可能需要考虑消除 surrogate key在 posts_tags 中，并在 (tag_id, users_posts_id) 上使用复合主键:

CREATE TABLE posts_tags (
   tag_id INT UNSIGNED NOT NULL,
   users_posts_id INT UNSIGNED NOT NULL,
   PRIMARY KEY (tag_id, users_posts_id)
);