SQL ORACLE - 在多个表中分别计数

Question

我有表格:USER、SUBSCRIPTION、FAVORITE 和 HISTORY。我想统计每个用户的订阅数、收藏数和历史数

SELECT 
    U.idUser,
    COUNT(S.idUser) AS nb_sub,
    COUNT(F.idUser) AS nb_fav,
    COUNT(H.idUser) AS nb_his
FROM
    USER U,
    SUBSCRIPTION S, 
    HISTORY H, 
    FAVORITE F
WHERE
    U.idUser = S.idUser AND
    U.idUser = F.idUser AND
    U.idUser = H.idUser
GROUP BY
    U.idUser;

我试过了，但这不是我想要的结果..

谢谢你帮助我

Answer 1

试试这个:

SELECT 
    U.idUser,
    (SELECT COUNT(*) FROM SUBSCRIPTION S WHERE U.idUser = S.idUser) AS nb_sub,
    (SELECT COUNT(*) FROM HISTORY H WHERE U.idUser = H.idUser) AS nb_fav,
    (SELECT COUNT(*) FROM FAVORITE F WHERE U.idUser = F.idUser) AS nb_his
FROM USER U