mysql - 连接3个表mysql

Question

客户

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

CusSeaFood

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

CusPizza

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

CusSnack

╔════╦═══════╦══════╗
║ 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 种食物。

Answer 1

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

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 ║
╚═══════╩══════════╩══════════════╩════════════╩════════════╝