mysql - 同一表上的多个联接SQL Server

我有一个表[item]应该具有数千条记录：

    ID  ItemNo  DepartDate  Country Item    Amount
    1       1   2016-01-01  France  HOTEL   100
    1       2   2016-01-01  France  HOTEL   150
    1       3   2016-01-01  France  MEALS   150
    2       1   2016-02-01  England HoTEL   150
    2       1   2016-02-01  England MEALS   200
    2       1   2016-02-02  England MEALS   200
    2       1   2016-02-02  WALES   MEALS   200

我希望结果取决于不同的departDate的酒店数量，用餐次数：

    Country hotelCount  mealCount
    France  1             1
    England 1             2
    WALES   0             1


    Select i.Country, Count(distinct iHotel.DepartDate) as hotelCount, Count(distinct iMeal.DepartDate) as mealCount
    FROm item i
    left join item iHotel on i.id = v.id and i.Country =iHotel.Country
    left join item iMeal  on i.id = iMeal .id and i.Country =iMeal .Country
    where i.Country is not null
    group by i.country

但是它不适用于数千条记录，因为它总是会导致超时问题。有什么帮助吗？非常感谢。

最佳答案

在此查询中，您无需做任何区分餐和旅馆的操作：

Select i.Country, Count(distinct iHotel.DepartDate) as hotelCount, Count(distinct iMeal.DepartDate) as mealCount
    FROm item i
    left join item iHotel on i.id = v.id and i.Country =iHotel.Country
    left join item iMeal  on i.id = iMeal .id and i.Country =iMeal .Country
    where i.Country is not null
    group by i.country

我对此查询不是很满意，sql Fiddle无法再次工作：

Select i.Country, MAX(coalesce(H.CT,0)) as hotelCount  , MAX(coalesce(M.CT,0)) as mealCount
    FROM item i
    LEFT JOIN
    (SELECT COUNT(*) as CT,Country, Item
       FROM item
         WHERE item = 'HOTEL' GROUP BY Country, Item) H
      ON H.Country = i.Country AND i.Item = H.item
    LEFT JOIN
    (SELECT COUNT(*) as CT,Country, Item
         FROM item
          WHERE item = 'MEALS' GROUP BY Country, Item) M
      ON M.Country = i.Country AND i.Item = M.item
    GROUP BY i.Country

关于mysql - 同一表上的多个联接SQL Server，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36452272/