对新创建的列使用大小写

我在SQL Server中有一个表，我已经按ID对其进行了分组，并使用其他数据的计数创建了2个新列。我希望能够同时创建另一列，如果两列中的计数都大于一个数字，则显示1，否则将显示2。但是，当我尝试时，它显示无效列名，我猜是因为我的计数列不在原始表中？

我的数据类似于：

    ID    Data1    Data2
-------------------------
0    1       1       5
1    1       2       5
2    1       5       8
3    1       7       9
4    2       8       5
5    2       7       3
6    2       9       2
7    3       3       1
8    3       3       6
9    3       2       7
10   3       6       3
11   3       8       0

所需输出(其中，如果&>=4，则代码为1，否则为2)：

   ID  CountData1  CountData2  Code
------------------------------------
0   1           4           4     1
1   2           3           3     2
2   3           5           5     1

当前查询：

SELECT 
    ID,
    COUNT(Data1) AS CountData1,
    COUNT(Data2) AS CountData2,
    (CASE WHEN (CountData1 >= 4 and CountData2 >= 4) THEN 1 ELSE 2 END) AS Code
FROM 
    Table
GROUP BY 
    ID

---

解决方案

分析SQL语句的方式，不能引用刚在同一作用域创建的表达式。

您可以再次重复表达式：

SELECT 
    ID,
    COUNT(Data1) AS CountData1,
    COUNT(Data2) AS CountData2,
    (CASE WHEN (COUNT(Data1) >= 4 and COUNT(Data2) >= 4) THEN 1 ELSE 2 END) 
    AS Code
FROM 
    dbo.Table
GROUP BY 
    ID;

或使用CTE或派生表：

-- CTE

;WITH cte AS 
(
  SELECT ID, 
      COUNT(Data1) AS CountData1,
      COUNT(Data2) AS CountData2
    FROM dbo.Table 
    GROUP BY ID
)
SELECT ID, CountData1, CountData2,
  CASE WHEN CountData1 >= 4 AND CountData2 >- 4 
    THEN 1 ELSE 2 END AS Code
FROM cte;

-- Derived Table

SELECT ID, CountData1, CountData2,
  CASE WHEN CountData1 >= 4 AND CountData2 >- 4 
    THEN 1 ELSE 2 END AS Code
FROM     
(
  SELECT ID, 
      COUNT(Data1) AS CountData1,
      COUNT(Data2) AS CountData2
    FROM dbo.Table 
    GROUP BY ID
) AS DerivedTable;

虽然有些人认为第一个更差，因为您引用了额外的COUNT()次，但这些都是一样的。SQL Server非常擅长不重复它不需要的工作，事实上，上面的所有三个查询都会生成完全相同的执行计划，具有完全相同的成本、相同的读取次数、相同的输出和计算出的完全相同的表达式数量。CPU和持续时间会略有不同，因为是计算机。

如果您遇到CTE生成的比重复表达式更好的计划的情况(请注意，可能在某些情况下，两个变体都会生成多个计数表达式)，请将其张贴在某个位置。