sql - 简化 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

)

标签： sqlsql-servertsql

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;