在类型之间，哪种电影似乎更受年龄段的评价？

Question

我该如何加入三张桌子，找到来自类型的最额定类型并将其分组。

我已经创建了

我做过这样的事情的桌子，但我知道是错误的

SELECT count(CASE WHEN (genre and age < 40 ) THEN rating ELSE null END),
count(CASE WHEN (genre age < 40 )  THEN rating ELSE null END)
FROM user
INNER JOIN data ON data.userid = user.userid
RIGHT JOIN item ON item.movieid = data.itemid

Answer 1

如果我正确理解您的问题，我认为您不需要案例声明。

SELECT age, genre, count(rating) total_rating
from User u
inner join Data d on u.userId = d.userId 
inner join Item i on i.itemId = d.itemId
WHERE age >= 40
Group by age, genre;

我也尝试使用案例语句，但这有点丑陋。

SELECT age, 
       genre, 
       sum (CASE WHEN age>=40 
            then 1 
            ELSE 0 
            end)  as 'UNDER40'
       
from User u
inner join Data d on u.userId = d.userId 
inner join Item i on i.itemId = d.itemId
where age >=40
Group by age, genre

db fiddle