Bad Luck on Subquery

面试碰到这样一道SQL题:

数据库结构如图

要求取出

1. Country Name

2. 在这个Ｃｏｕｎｔｒｙ的city count

3. 在这个Ｃｏｕｎｔｒｙ的hotel count

 

当时一想后面题还多着呢，　祭出Ｓｕｂｑｕｅｒｙ　三下五除二，　搞定，　

 

代码

SELECT
Country.Name,
( SELECT
　　　　　　 COUNT (City.CityID)
　　　　 FROM
　　　　　　tblCity City
　　　　 WHERE
　　　　　　City.CountryID = Country.CountryID
　　　　) AS Cities,
( SELECT
　　　　　　 COUNT (Hotel.HotelID)
　　　　 FROM
　　　　　　tblCity City
　　　　 LEFT JOIN tblHotel Hotel
　　　　　　　　 ON City.CityID = Hotel.CityID
　　　　 WHERE
　　　　　　City.CountryID = Country.CountryID
　　　　) AS Hotels
FROM
　　tblCountry Country
ORDER BY
　　Country.Name

 

离开面试公司后，　一直觉得不妥，咋就觉得那是个设好的套让我钻呢．

 

琢磨着用ＪＯＩＮ也能解决这个问题如下：

代码

SELECT
Country.Name,
ISNULL (Cities, 0 ) AS Cities,
ISNULL (Hotels, 0 ) AS Hotels
FROM
tblCountry Country LEFT JOIN
( SELECT
City.CountryID,
COUNT (Hotel.HotelID) AS Hotels
FROM
tblCity City LEFT JOIN tblHotel Hotel
ON City.CityID = Hotel.CityID
GROUP BY
City.CountryID
) AS CountryHotels
ON Country.CountryID = CountryHotels.CountryID
LEFT JOIN
( SELECT
City.CountryID,
COUNT (City.CityID) AS Cities
FROM
tblCity City
GROUP BY
City.CountryID
) AS CountryCities
ON Country.CountryID = CountryCities.CountryID

 

 

放在一起比较下Execution Plan, 竟发现Ｓｕｂｑｕｅｒｙ解法的执行效率竟只有ＪＯＩＮ解法的一半．这时才惊醒，原来是上来鬼子的当了．

 

 

原来，　如果子查询中有使用主查询的字段的话，　ｓｕｂｑｕｅｒｙ会执行ｎ次，如果主查询有ｎ的备选结果．　如果子查询没有使用到主查询的字段的话，　子查询只会在执行一次．

 

以此成贴，引以为戒．

转载于:https://www.cnblogs.com/jeffwang/archive/2010/04/14/1712007.html