我有以下问题:我有三个表:日志,媒体和用户。
CREATE TABLE `users` (
`id` INTEGER NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
ROW_FORMAT=DEFAULT;
CREATE TABLE `media` (
`id` INTEGER NOT NULL AUTO_INCREMENT,
`title` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
ROW_FORMAT=DEFAULT;
CREATE TABLE `log` (
`id` INTEGER NOT NULL AUTO_INCREMENT,
`action` VARCHAR(20) NOT NULL,
`action_time` DATETIME NOT NULL,
`user_id` INTEGER NOT NULL,
`affected_user_id` INTEGER DEFAULT NULL,
`media_id` INTEGER DEFAULT NULL,
`comment` TEXT DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
ROW_FORMAT=DEFAULT;
日志表的重点是,每一个动作都是由用户完成的,但是某些动作会影响用户,而某些动作会影响媒体,但并非总是如此。一些动作会影响用户和媒体。
user_id列,affected_user_id是用户的外国密钥。ID
media_id列是媒体的外键
如果列没有值,则值为NULL。
我要做的是在日志上进行SELECT查询,而将INNER JOIN与其他两个表进行INNER JOIN。
首先,我尝试了此查询,不知道出了点问题,所以这将是我第一次尝试这样的事情:
SELECT log.action, log.action_time, log.user_id, user_name_table.name AS user_name,
log.affected_user_id, affected_user_name_table.name AS affected_user_name,
log.media_id, media.title AS media_title, log.comment
FROM log
INNER JOIN users AS user_name_table ON user_name_table.id = log.user_id
INNER JOIN users AS affected_user_name_table ON affected_user_name_table.id = log.affected_user_id
INNER JOIN media ON media.id = log.media_id
WHERE log.user_id=1 OR log.affected_user_id=1
我在互联网上抬头如何在两列上INNER JOIN相同的桌子,我真的不知道它是否正确,也许这也是问题所在。我希望此查询能够将日志表的所有列以及与user_id和affected_user_id关联的用户的名称返回。
在日志表中,有足够的行具有user_id = 1或affected_user_id = 1的行。但是不知何故,我的结果集为空。我没有收到错误消息,因此我的查询语法应该可以。
我还尝试了其他一些类似的版本:
SELECT log.action, log.action_time, log.user_id, users.name AS user_name, log.affected_user_id,
log.media_id, media.title AS media_title, log.comment
FROM log
INNER JOIN users ON users.id = log.user_id OR users.id=log.affected_user_id
INNER JOIN media ON media.id = log.media_id
WHERE log.user_id=1 OR log.affected_user_id=1
SELECT log.action, log.action_time, log.user_id, users.name AS user_name,
log.affected_user_id,
log.media_id, media.title AS media_title, log.comment
FROM log
INNER JOIN users ON users.id = log.user_id = affected_user_id
INNER JOIN media ON media.id = log.media_id
WHERE log.user_id=1 OR log.affected_user_id=1
我已经搜索了互联网的解决方案,但我似乎找不到。我什至问过chatgpt :),但它说我的查询完全可以,应该返回我的结果集。
分析解答
如果我正确理解您,这会有所帮助吗?
SELECT log.action,
log.action_time,
log.user_id,
users.name AS user_name,
log.affected_user_id,
log.media_id,
media.title AS media_title,
log.comment
FROM log
INNER JOIN users
ON ( users.id = log.user_id
AND log.user_id = 1)
OR ( users.id = log.affected_user_id
AND log.affected_user_id = 1)
INNER JOIN media ON media.id = log.media_id
[EDIT],发布示例数据后:
SQL> SELECT * FROM users;
ID NAME
---------- -----
1 Alex
2 Heinz
3 Frank
SQL> SELECT * FROM media;
ID TITLE
---------- ---------------
1 Harry Potter
2 Some other book
3 No idea
SQL> SELECT * FROM log;
ID ACTION ACTION_TIME USER_ID AFFECTED_USER_ID MEDIA_ID
---------- ------------ ------------------- ---------- ---------------- ----------
1 MEDIA_EDIT 2023-08-23 17:56:04 1 5
2 USER_EDIT 2023-08-23 17:56:04 1 3
3 MEDIA_BORROW 2023-08-23 17:56:04 1 3 1
查询,将log
表连接到users
表两次:一次:log.user_id
(内连接),另一个时间log.affected_user_id
(外部联接)。
SQL> SELECT l.action,
2 l.action_time,
3 u.name AS user_name,
4 au.name AS affected_user_name,
5 m.title media_title
6 FROM LOG l
7 JOIN users u ON u.id = l.user_id
8 LEFT JOIN users au ON au.id = l.affected_user_id
9 LEFT JOIN media m ON m.id = l.media_id;
ACTION ACTION_TIME USER_NAME AFFECTED_USER_NAME MEDIA_TITLE
------------ ------------------- --------- ----------------------- ---------------
MEDIA_BORROW 2023-08-23 17:56:04 Alex Frank Harry Potter
USER_EDIT 2023-08-23 17:56:04 Alex Frank
MEDIA_EDIT 2023-08-23 17:56:04 Alex
SQL>