sql - 使用相关子查询或连接而不是派生表

Question

请考虑这种情况:

我有一个这样的表:

CityCode        CityName        Col_6        Col_9        Col_10
----------------------------------------------------------------------
001                London        10            21               1
001                London        24            13               2
001                London        39            10               2
002                Paris         19            61               1
002                Paris         10            50               1
003                Vien          12            41               2
004                Mosco         22            27               2
004                Mosco         28            9                2
004                Mosco         41            30               1
004                Mosco         33            12               1
005                Cairo         10            21               1
006                Milan         19            41               1
006                Milan         40            32               2

我想写一个查询来为每个城市计算一些公式。现在我对不同的城市使用游标，然后选择适当的记录并进行计算:

DECLARE mycur   CURSOR  
FOR
    SELECT CityCode
    FROM tblCities 

OPEN mycur
FETCH NEXT FROM mycur INTO @param_Code  

WHILE (@@fetch_status = 0)
BEGIN
     SELECT 
         @param_Code, (SELECT COUNT(*)
                       FROM MyTable
                       WHERE col_9 = 61
                         AND (City_Code = @param_Code) 
                      ) /
                      (SELECT COUNT(*)
                       FROM MyTable
                        AND (City_Code = @param_Code) 
                      ),
         (SELECT COUNT(*)
          FROM MyTable
          WHERE col_10 = 1
            AND (City_Code = @param_Code)) /
            (SELECT COUNT(*)
             FROM MyTable
             WHERE (col_10 = 1 OR col_10 = 2)
               AND (City_Code = @param_Code)),
         Some other calculation like above

     FETCH NEXT FROM mycur INTO @param_Code
END

CLOSE mycur
DEALLOCATE mycur

@param_Code 是我的游标变量。

我怎样才能做得更好？我不想使用派生表。我更喜欢使用相关的子查询或连接。

谢谢

Answer 1

使用简单 GROUP BY子句 CASE表达式。这种情况下的光标过多。

SELECT CityCode, COUNT(CASE WHEN col_9 = 61 THEN 1 END) * 1.00 / COUNT(*),
                 COUNT(CASE WHEN col_10 = 1 THEN 1 END) * 1.00 
                   / COUNT(CASE WHEN (Col10 IN (1, 2)) THEN 1 END)
FROM dbo.MyTable
GROUP BY CityCode

演示 SQLFiddle

如果需要进一步计算，那么您可以将此脚本包装在 CTE 中:

;WITH cte AS
 (       
  SELECT CityCode, COUNT(CASE WHEN col_9 = 61 THEN 1 END) * 1.00 / COUNT(*),
                   COUNT(CASE WHEN col_10 = 1 THEN 1 END) * 1.00 
                     / COUNT(CASE WHEN (Col10 IN (1, 2)) THEN 1 END)
  FROM dbo.MyTable
  GROUP BY CityCode
  )
  SELECT --Some other calculation like above
  FROM cte