SQL联接3个表，其中有COUNT和GROUP BY子句 [英] SQL JOIN 3 TABLES WITH COUNT AND GROUP BY CLAUSE

问题描述

我有3张这样的桌子:

EXPEDITION (ID, CreateDate, Status);
PACKAGE (ID, EXPEDITION_ID)
ITEM (ID, EXPEDIITONPACKAGE_ID);

对于每次探险，我需要知道包装的数量和物品的数量.

I need to know, for each expedition, the quantity of packages and the quantity of items.

推荐答案

更新

这似乎有它的查询.

    SELECT 
        E.ID, 
        P.Packages, 
        I.Items 
    FROM EXPEDITION E

    LEFT JOIN (
        SELECT DISTINCT E.ID, COUNT(P.ID) AS "Packages" FROM EXPEDITION E
        LEFT JOIN PACKAGE P
        ON E.ID = P.EXPEDITION_ID
        GROUP BY E.ID
    ) P
    ON E.ID = P.ID

    LEFT JOIN (
        SELECT DISTINCT P.ID as "PackageID", COUNT(I.ID) AS "Items" FROM PACKAGE P
        JOIN ITEM I
        ON P.ID = I.EXPEDIITONPACKAGE_ID
        GROUP BY P.ID
    ) I
    ON P.ID = I.PackageId

    GROUP BY 
        E.ID, 
        P.Packages, 
        I.Items

    ORDER BY 
        E.ID

它有两个内部查询，分别对ID进行计数，并将它们与主查询结合起来以显示结果.

It has two inner queries, that count the IDs separately, and they are joined in the main query to show the results.

这篇关于SQL联接3个表，其中有COUNT和GROUP BY子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！