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

Question

我有一个表 [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

我希望结果能够根据不同的出发日期计算酒店、餐饮的数量:

    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

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

Answer 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

我对此查询不太满意，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