我在 MySQL 8 中有以下表格：
Restaurant (Id, Name)Order (Id, RestaurantId)Rating (OrderId, Rating, CreatedAt)
現在我想計算一家餐廳過去 12 個月的評分，但也需要計算所有評分，所以它會在前端顯示為“4.7 星（120 條評論）”，這里 4.7 星是 12 個月評分的平均值，但 120 是所有評分的計數。這是我嘗試過的查詢，但 count 每行顯示 1：
SELECT COUNT(r.Id), r.Rating FROM `Order` o
JOIN `rating` r ON r.OrderId = o.Id
WHERE o.RestaurantId = 1 AND r.CreatedAt > now() - INTERVAL 12 month
GROUP BY r.Id
uj5u.com熱心網友回復：
SELECT o.RestaurantId,
COUNT(*) AS total,
AVG(CASE WHEN r.CreatedAt > now() - INTERVAL 12 month
THEN r.Rating
END) AS rating_12_months
FROM `Order` o
JOIN `rating` r ON r.OrderId = o.Id
GROUP BY o.RestaurantId
uj5u.com熱心網友回復：
使用條件聚合
SELECT re.Name, -- restaurant
COUNT(*) total_count, -- total count for it, without date filtering
AVG(CASE WHEN ra.CreatedAt > NOW() - INTERVAL 1 YEAR -- average rating for it with date filtering,
THEN ra.Rating
END) average_rating_last_year
FROM Restaurant re
JOIN Order or ON re.id = or.RestaurantId
JOIN Rating ra ON or.id = ra.OrderId
GROUP BY re.Name
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/328747.html
標籤：mysql
上一篇：直接在mysql中插入后計算值