mysql - 连接同一个表中的两个外键引用同一个主键
问题描述
我正在尝试将多个表格BooksReview
(Followers
_ Books
_ _ _ _ _ _想想问题出在哪里)有两个来自同一个主键的引用,它来自表中,也来自表中。Users
Books
userId
Users
BooksReview
userId
userId
bookId
Books
Followers
follower
userId
Users
followed
userId
Users
问题:我创建了一个 MySQL 查询来获取基于特定用户的特定书籍的书评数量,并获取该用户的书籍数量和他/她拥有的关注者数量,但是当我将Followers
连接部分添加到我的查询显示所有值的结果为 0,预期值为 4 本书、4 条评论和 1 个关注者。
我试图更改查询中的连接类型,但它最终得到相同的结果,并搜索在同一个表中连接两个外键以获得相同的主键,但我没有发现任何有用的东西。
```
CREATE TABLE IF NOT EXISTS `Authors`.`Users` (
`userId` VARCHAR(100) NOT NULL,
`username` VARCHAR(25) NOT NULL,
`password` VARCHAR(16) NOT NULL,
`email` VARCHAR(254) NOT NULL,
`birthday` DATE NULL,
`aboutMe` TEXT(300) NOT NULL,
`facebookAccount` VARCHAR(25) NULL,
`twitterAccount` VARCHAR(25) NULL,
`linkedinAccount` VARCHAR(25) NULL,
`profileImage` VARCHAR(200) NULL,
PRIMARY KEY (`userId`),
UNIQUE INDEX `username_UNIQUE` (`username` ASC),
UNIQUE INDEX `email_UNIQUE` (`email` ASC))
ENGINE = InnoDB;
```
CREATE TABLE IF NOT EXISTS `Authors`.`Books` (
`bookId` VARCHAR(100) NOT NULL,
`bookCategory` VARCHAR(25) NOT NULL,
`title` VARCHAR(25) NOT NULL,
`bookCover` VARCHAR(45) NOT NULL,
`bookDescription` VARCHAR(200) NOT NULL,
`userId` VARCHAR(100) NOT NULL,
`price` DECIMAL(2,2) NOT NULL,
`introduction` VARCHAR(300) NOT NULL,
PRIMARY KEY (`bookId`),
INDEX `userId_idx` (`userId` ASC),
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `Authors`.`Users` (`userId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
```
```
CREATE TABLE IF NOT EXISTS `Authors`.`BooksReview` (
`bookId` VARCHAR(100) NOT NULL,
`rateMessage` VARCHAR(100) NULL,
`rateNumber` DECIMAL(1,1) NULL,
`userId` VARCHAR(100) NOT NULL,
INDEX `userId_idx` (`userId` ASC),
CONSTRAINT `bookId`
FOREIGN KEY (`bookId`)
REFERENCES `Authors`.`Books` (`bookId`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `Authors`.`Users` (`userId`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
```
CREATE TABLE IF NOT EXISTS `Authors`.`Followers` (
`follower` VARCHAR(100) NOT NULL,
`followed` VARCHAR(100) NOT NULL,
INDEX `follower_idx` (`follower` ASC),
INDEX `followed_idx` (`followed` ASC),
CONSTRAINT `follower`
FOREIGN KEY (`follower`)
REFERENCES `Authors`.`Users` (`userId`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `followed`
FOREIGN KEY (`followed`)
REFERENCES `Authors`.`Users` (`userId`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
THIS IS THE QUERY
SELECT count(br.bookId) AS reviewsCount, count(b.bookId) AS booksCount, count(f.follower) AS followersCount
FROM Users AS u
LEFT JOIN Books AS b ON b.userId = u.userId
JOIN Followers AS f ON b.userId = f.followed AND f.follower = u.userId
INNER JOIN BooksReview AS br ON br.bookId = b.bookId
AND b.bookId IN (SELECT bookId
FROM Books
WHERE userId = 'dbb21849-ccce-4af1-aa0f-6653919bf956');
我预计结果应该是 1 位追随者、4 本书和 4 条评论,但实际结果是 0。
DML:
Users
->
userId: dbb21849-ccce-4af1-aa0f-6653919bf956
username: mostafabbbaron
ETC...
Books
->
userId: dbb21849-ccce-4af1-aa0f-6653919bf956
bookId: 5f39c1ae-5e99-4b3a-8ee0-97a80c1ba9b1
ETC...
Followers
->
follower: dbb21849-ccce-4af1-aa0f-6653919bf956
folllowed: b39c8e0c-4124-4339-8c30-e1fc8db5f2d4
ETC...
BooksReviews
->
userId: dbb21849-ccce-4af1-aa0f-6653919bf956
bookId: aa44a455-dc28-476f-b4b9-47563a717f03
ETC...
解决方案
无论哪种方式,您的查询都是错误的。但是结果为零的原因可能是这些条件:b.userId = u.userId
和b.userId = f.followed AND f.follower = u.userId
。
如果b.userId = u.userId
然后b.userId = f.followed
_f.followed = u.userId
如果f.followed = u.userId
然后f.follower = u.userId
_f.followed = f.follower
这意味着用户必须跟随他/她自己,我怀疑是这种情况。
我将按以下方式编写查询:
SELECT
count(DISTINCT b.bookId) AS booksCount,
count(br.bookId) AS reviewsCount,
(SELECT COUNT(*) FROM Followers AS f WHERE f.followed = u.userId) AS followersCount
FROM Users AS u
LEFT JOIN Books AS b ON b.userId = u.userId
LEFT JOIN BooksReview AS br ON br.bookId = b.bookId
WHERE u.userId = 'dbb21849-ccce-4af1-aa0f-6653919bf956'
注意:虽然它很好Users LEFT JOIN Books LEFT JOIN BooksReview
,因为你有一个“关系链” Users <- Books <- BooksReview
。但是您不应该只加入Followers
表,因为它与Books
or没有真正的关系BooksReview
,也不适合该链。这就是为什么我在 SELECT 子句中使用子查询来计算关注者的原因。
推荐阅读
- android - 如何在多个类中使用一个特定函数,而无需在 Kotlin 的所有类中实际复制其代码?
- sql - 在 phpmyadmin 中创建一个 sql 查询
- amazon-web-services - 无服务器应用程序中的微服务和报告
- amazon-web-services - SQS -> StepFunction 使用无服务器框架
- c - 使用奇异值分解的矩阵的 Moore-Penrose 伪逆
- c - C套接字编程-只打印一行
- visual-studio - Github 中的结构和组织?
- r - UseMethod("pull") 中的 unnest_tokens 和 ERROR
- python - 浏览器检查器将 tbody 添加到原始 HTML 中不存在的表中
- html - 淡出幻灯片单张图像