mysql - 加入3表mysql

顾客

╔═══════╦══════════╗
║ CUSID ║ CUS NAME ║
╠═══════╬══════════╣
║     1 ║ AA       ║
║     2 ║ BB       ║
║     3 ║ CC       ║
╚═══════╩══════════╝

CusSeaFood

╔════╦═══════╦══════════╗
║ ID ║ CUSID ║   NAME   ║
╠════╬═══════╬══════════╣
║  1 ║     1 ║ SeaFoodA ║
║  2 ║     1 ║ SeaFoodB ║
║  3 ║     2 ║ SeaFoodC ║
╚════╩═══════╩══════════╝

比萨

╔════╦═══════╦══════╗
║ ID ║ CUSID ║ NAME ║
╠════╬═══════╬══════╣
║  1 ║     1 ║ PAA  ║
║  2 ║     2 ║ PBB  ║
╚════╩═══════╩══════╝

库斯纳克

╔════╦═══════╦══════╗
║ ID ║ CUSID ║ NAME ║
╠════╬═══════╬══════╣
║  1 ║     2 ║ SAA  ║
║  2 ║     3 ║ SBB  ║
╚════╩═══════╩══════╝

要计算如下客户示例所订购的海鲜，比萨饼或小吃的数量。

Cus ID, Cus Name, Number Of SeaFood, Sea Food info, Number Of Pizza, Pizza info, Number of Snack, Snack info.

可能未向客户订购所有3种食物。

最佳答案

您需要通过子查询分别计算每个计数，以便获得正确的结果。这将防止您计算重复记录。

SELECT  a.*,
        COALESCE(b.totalSeaFood, 0) totalSeaFood,
        COALESCE(c.totalPizza, 0) totalPizza,
        COALESCE(d.totalSnack, 0) totalSnack
FROM    Customer a
        LEFT JOIN
        (
            SELECT  cusID, COUNT(*) totalSeaFood
            FROM    CusSeaFood
            GROUP   BY cusID
        ) b ON a.cusID = b.CusID
        LEFT JOIN
        (
            SELECT  cusID, COUNT(*) totalPizza
            FROM    CusPizza
            GROUP   BY cusID
        ) c ON a.cusID = c.CusID
        LEFT JOIN
        (
            SELECT  cusID, COUNT(*) totalSnack
            FROM    CusSnack
            GROUP   BY cusID
        ) d ON a.cusID = d.CusID

SQLFiddle Demo


要进一步获得有关联接的知识，请访问以下链接：


Visual Representation of SQL Joins


结果

╔═══════╦══════════╦══════════════╦════════════╦════════════╗
║ CUSID ║ CUS NAME ║ TOTALSEAFOOD ║ TOTALPIZZA ║ TOTALSNACK ║
╠═══════╬══════════╬══════════════╬════════════╬════════════╣
║     1 ║ AA       ║            2 ║          1 ║          0 ║
║     2 ║ BB       ║            1 ║          1 ║          1 ║
║     3 ║ CC       ║            0 ║          0 ║          1 ║
╚═══════╩══════════╩══════════════╩════════════╩════════════╝

关于mysql - 加入3表mysql，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15265251/