简化一个SQL查询，以减少连接

Question

查询给出了正确的结果，但我希望以无线的最佳查询。这可以简化吗？

SELECT 
[EventName]
  FROM [WorldEvents].[dbo].[tblEvent] EVNT
  INNER JOIN
  [WorldEvents].[dbo].[tblCountry] CTRY
  ON
  EVNT.CountryID = CTRY.CountryID
  INNER JOIN
  [WorldEvents].[dbo].[tblContinent] CNT
  ON
  CNT.ContinentID = CTRY.ContinentID
  WHERE [ContinentName] 
   IN
  (
  SELECT CNAME FROM(
            SELECT TOP 3
            [ContinentName] AS CNAME,
            COUNT(EventName) AS EVENT_PER_CONTINENT
            FROM (
            SELECT [ContinentName],
            [EventName]
            --,SUM(COUNT(EventName) OVER(PARTITION BY [ContinentName] ORDER BY [ContinentName] ,[EventName]) AS COUNT
            FROM [WorldEvents].[dbo].[tblContinent] CONTINENT
            INNER JOIN 
            [WorldEvents].[dbo].[tblCountry] COUNTRY
            ON
            CONTINENT.ContinentID = COUNTRY.ContinentID
            INNER JOIN 
            [WorldEvents].[dbo].[tblEvent] EVE
            ON 
            EVE.[CountryID] = COUNTRY.[CountryID]
            ) TABLE1
GROUP BY 
[ContinentName]
ORDER BY EVENT_PER_CONTINENT
ASC
) TABLE2
)

Answer 1

如果我正确地遵循逻辑，则需要数量从"前3"大洲中的事件。这是获取此信息的更简单方法：

SELECT EventName
FROM (SELECT ContinentID, EventName,
             DENSE_RANK() OVER (ORDER BY cnt, ContinentID) as seqnum
      FROM (SELECT c.ContinentID, e.EventName
                   COUNT(*) OVER (PARTITION BY c.ContinentID) AS cnt
            FROM [WorldEvents].[dbo].[tblCountry] c INNER JOIN 
                 [WorldEvents].[dbo].[tblEvent] e
                 ON e.[CountryID] = c.CountryID
           ) ce
     ) ce
WHERE seqnum <= 3;