将两个选择查询合并为一个

I have three tables : history, video and user. The two following queries display for each video the number of views for french users or for german users :

我有三个表:历史,视频和用户。以下两个查询为每个视频显示法国用户或德国用户的观看次数:

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;

and

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

But how combine this two queries to have only one ? I would like something like that :

但是如何将这两个查询结合起来只有一个?我想要这样的东西:

idVideo | nb_fr | nb_ge
-----------------------
    1   |   5   |   4
    2   |   3   |   6
    3   |   2   |   8
    4   |   3   |   3

4 个解决方案

#1

Use case expressions to do conditional aggregation:

用例表达式执行条件聚合:

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;

Note the change to GROUP BY V.idVideo, beacuse that's the selected column.

请注意对GROUP BY V.idVideo的更改,因为这是所选列。

#2

You can do it using conditional aggregation with CASE EXPRESSION :

你可以使用带有CASE EXPRESSION的条件聚合来完成它:

SELECT V.idVideo,
       COUNT(CASE WHEN  U.nationality = 'french' THEN 1 END) AS nb_fr,
       COUNT(CASE WHEN  U.nationality = 'german' THEN 1 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
GROUP BY H.idVideo
ORDER BY V.idVideo

#3

Use conditional aggregation:

使用条件聚合:

SELECT H.idVideo,
       SUM(CASE WHEN U.nationality = 'french' THEN 1 ELSE 0 END) as nb_fr,
       SUM(CASE WHEN U.nationality = 'german' THEN 1 ELSE 0 END) as nb_ge
FROM HISTORY H INNER JOIN
     USER U 
     ON U.idUser = H.idUser
GROUP BY H.idVideo
ORDER BY H.idVideo;

Also note that the query can be simplified. The JOIN to VIDEO is probably not needed (assuming that idVideo is unique, which is a very reasonable assumption).

另请注意,查询可以简化。可能不需要JOIN到VIDEO(假设idVideo是唯一的,这是一个非常合理的假设)。

Also, the ORDER BY and GROUP BY should use the same column reference.

此外,ORDER BY和GROUP BY应使用相同的列引用。

#4

Starting from Oracle 11.1, you can use the PIVOT operation:

从Oracle 11.1开始,您可以使用PIVOT操作:

select idvideo, nb_fr, nb_ge
from   ( select h.idvideo, u.nationality
         from   history h inner join user u on h.iduser = u.iduser
       )
pivot  ( count(*) for nationality in ('french' as nb_fr, 'german' as nb_ge) )
order by idvideo
;

#1