我有這張帶有單位/房屋的桌子
CREATE TABLE `units` (
`id` bigint NOT NULL AUTO_INCREMENT,
`cancellation_policy` varchar(255) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`image_url` varchar(255) NOT NULL,
`price` decimal(19,2) NOT NULL,
`region` varchar(255) NOT NULL,
`title` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UKha7gwhuig6p6vftvoghfi2b7g` (`title`,`image_url`),
UNIQUE KEY `UK_pdd7pto9vch2kb58kohy96a5f` (`image_url`),
UNIQUE KEY `UK_58rre8c1gk28a7d5p6wguiti9` (`title`)
)
這個有評論
CREATE TABLE `reviews` (
`id` bigint NOT NULL AUTO_INCREMENT,
`description` varchar(255) DEFAULT NULL,
`stars` int NOT NULL,
`user_id` varchar(255) NOT NULL,
`unit_id` bigint NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UKc3rd8vjkpbcda34jomifuybu9` (`user_id`,`unit_id`),
KEY `FKbgbdator49pjrbriaktrbv1q2` (`unit_id`),
CONSTRAINT `FKbgbdator49pjrbriaktrbv1q2` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`)
)
我想把所有電影的平均收視率放在一起。但是，此查詢不會回傳沒有評級 (NULL) 值的電影。此查詢不回傳沒有評分的電影
select
u.id, u.cancellation_policy, u.description, u.image_url, u.price, u.region, u.title, round(avg(stars),0) as ratings
from units u
inner join
reviews r
ON u.id = r.unit_id
group by u.id
獲取所有電影（包括那些沒有明星的電影）的正確方法是什么？
uj5u.com熱心網友回復：
LEFT JOIN 會顯示所有單元，即使是沒有評論的單元
SELECT
u.id,
u.cancellation_policy,
u.description,
u.image_url,
u.price,
u.region,
u.title,
ROUND(AVG(IFNULL(stars,0)), 0) AS ratings
FROM
units u
LEFT JOIN
reviews r ON u.id = r.unit_id
GROUP BY u.id
uj5u.com熱心網友回復：
嘗試 aLEFT JOIN而不是INNER JOIN
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/406928.html
標籤：