我有以下表格：
CREATE TABLE `country` (
`name` VARCHAR(60) NOT NULL,
`code` VARCHAR(3) UNIQUE NOT NULL,
PRIMARY KEY (`code`)
);
CREATE TABLE `user` (
`userId` INT UNIQUE NOT NULL AUTO_INCREMENT,
`country` VARCHAR(3) NOT NULL,
`age` INT NOT NULL,
PRIMARY KEY (`userId`),
CONSTRAINT `fk_user_country` FOREIGN KEY (`country`) REFERENCES `country`(`code`)
);
CREATE TABLE `bookRating` (
`userId` INT NOT NULL,
`isbn` VARCHAR(13) NOT NULL,
`rate` INT NOT NULL,
`date` DATE NOT NULL,
CONSTRAINT `fk_bookRating_user` FOREIGN KEY (`userId`) REFERENCES `user`(`userId`),
CONSTRAINT `fk_bookRating_book` FOREIGN KEY (`isbn`) REFERENCES `book`(`isbn`)
);
CREATE TABLE `book` (
`isbn` varchar(13) UNIQUE NOT NULL,
`bookTitle` VARCHAR(280),
`bookAuthor` VARCHAR(150),
`yearPublication` int(4),
-- `yearPublication` must be an integer because we have value less that 1901 in dataset
`publisher` VARCHAR(135),
PRIMARY KEY (`isbn`),
CONSTRAINT `publication_yea_chk` check ((`yearPublication` > -1) && (`yearPublication` < 2101))
);
正如我在標題上所說，我想找到平均評分最高的書，對于每個國家
我試過這個查詢：
select T1.name, T1.BookTitle, Rate
from
(
select C.Code, AVG(BR.rate) MAXRating
from `bookRating` BR
inner join `book` B on BR.isbn = B.isbn
INNER JOIN `USER` U ON BR.UserID = U.USERId
INNER JOIN `COUNTRY` C ON U.country = C.Code
group by C.Code
) T
inner join
(
select C.Code, C.name, B.BookTitle, BR.ISBN, BR.rate
from `bookRating` BR
inner join `book` B on BR.isbn = B.isbn
INNER JOIN `USER` U ON BR.UserID = U.USERId
INNER JOIN `COUNTRY` C ON U.country = C.Code
) T1 ON T.Code = T1.Code AND T.MAXRATING = T1.RATE;
我很確定這行得通。但我想讓它像如果兩本書或更多的書具有相同的平均評分，我想要評分最高的一本。
我想我可以使用 If() 陳述句，但我怎么能 If(... , a condition)
我怎么能做到？
更新
我已經創建了資料庫并在 db fidle 中插入了一些資訊： https ://www.db-fiddle.com/f/s6wKhKhxXMX1W2x9VZn9da/1
uj5u.com熱心網友回復：
您可以加入表格，按國家和書籍聚合以獲得所有平均評分，并使用視窗函式MAX()和FIRST_VALUE()聚合結果來獲得每個國家/地區平均評分最高的書籍：
SELECT DISTINCT c.name,
FIRST_VALUE(b.bookTitle) OVER (
PARTITION BY c.Code
ORDER BY AVG(r.rate) DESC, COUNT(*) DESC
) bookTitle,
MAX(AVG(r.rate)) OVER () AverageRating
FROM country c
INNER JOIN users u ON u.country = c.Code
INNER JOIN bookRating r ON r.UserID = u.UserID
INNER JOIN book b ON b.isbn = r.isbn
GROUP BY c.Code, b.isbn;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/439341.html
上一篇：找到使數量最大化的列組合
下一篇：R使用條件/連接資料操作串列串列