我有三个表：历史,视频和用户.
以下两个查询为每个视频显示法国用户或德国用户的观看次数：
以下两个查询为每个视频显示法国用户或德国用户的观看次数：
- SELECT V.idVideo,COUNT(H.idVideo) AS nb_fr
- FROM HISTORY H
- INNER JOIN VIDEO V ON V.idVideo = H.idVideo
- INNER JOIN USER U ON U.idUser = H.idUser
- WHERE U.nationality = 'french'
- GROUP BY H.idVideo
- ORDER BY V.idVideo;
和
- SELECT V.idVideo,COUNT(H.idVideo) AS nb_ge
- FROM HISTORY H
- INNER JOIN VIDEO V ON V.idVideo = H.idVideo
- INNER JOIN USER U ON U.idUser = H.idUser
- WHERE U.nationality = 'german'
- GROUP BY H.idVideo
- ORDER BY V.idVideo
但是如何将这两个查询结合起来只有一个？
我想要这样的东西：
- idVideo | nb_fr | nb_ge
- -----------------------
- 1 | 5 | 4
- 2 | 3 | 6
- 3 | 2 | 8
- 4 | 3 | 3
解决方法
用例表达式执行条件聚合：
- SELECT V.idVideo,COUNT(case when U.nationality = 'french' then H.idVideo end) AS nb_fr,COUNT(case when U.nationality = 'german' then H.idVideo end) AS nb_ge
- FROM HISTORY H
- INNER JOIN VIDEO V ON V.idVideo = H.idVideo
- INNER JOIN USER U ON U.idUser = H.idUser
- WHERE U.nationality in ('french','german')
- GROUP BY V.idVideo
- ORDER BY V.idVideo;
请注意对GROUP BY V.idVideo的更改,因为这是所选列.
