SQL Server 使用游标进行查询

Question

下面的查询计算加利福尼亚州 2009 财年与 2010 财年之间的产品百分比。我被分配计算剩余的状态（请参阅状态表）。 SQL Server中有没有一种方法可以计算各州的百分比？我目前一次手动计算每个状态。

（请注意，由于数据完整性，我必须单独使用状态表。我在想游标是否可以做到这一点，但我还没有做到这一点。）

DECLARE @LOCATION VARCHAR(2)
SET  @state = 'CA' 

SELECT NUMBER1/CAST(NUMBER2 as FLOAT) as PERCENTAGE
FROM
(
    SELECT COUNT (PRODUCT) as NUMBER1
    FROM SOMETABLE1
    WHERE LOCATION = @state
    AND DATE >='10/1/2008'
    AND DATE  <' 10/1/2009'
)X,

(
    SELECT COUNT (PRODUCT) as NUMBER2
    FROM SOMETABLE2
    WHERE LOCATION = @state
    AND DATE >='10/1/2009'
    AND DATE  <' 10/1/2010'
)Y

PERCENTAGE
1.400742


SELECT state
FROM STATES

STATE
CA
AZ
TX
NV
NM
UT
OR
CO
WA

Answer 1

您可以加入 STATES 表。沿着这些思路：

SELECT X.STATE, X.NUMBER1/CAST(Y.NUMBER2 as FLOAT) as PERCENTAGE
FROM
(
    SELECT S.STATE, COUNT(*) as NUMBER1
    FROM SOMETABLE1 AS S1
    INNER JOIN STATES AS S ON S1.LOCATION = S.STATE
    AND S1.DATE >='10/1/2008'
    AND S1.DATE < '10/1/2009'
    GROUP BY S.STATE
)X,

(
    SELECT S.STATE, COUNT (*) as NUMBER2
    FROM SOMETABLE2 AS S2
    INNER JOIN STATES AS S ON S2.LOCATION = S.STATE
    AND S2.DATE >='10/1/2009'
    AND S2.DATE < '10/1/2010'
    GROUP BY S.STATE
)Y
WHERE X.STATE = Y.STATE