带有where子句的聚合函数

Meem 发表于 Dev

记忆

我有一种情况，我需要在update语句以及select语句的where条件中使用聚合函数。

范例：

情况一：

 Update test_table
 set ID_Number = 0
 where count(ID_Number)=1; /* Not possible with where */

注意：上面的脚本无法通过where子句使用。

情况2：

例如，我有下表：测试

 ID_Number Column1  Column2
 --------------------------
  1          XYZ      ZYX
  1          MMM      NNN
  2          III      JJJ
  3          AAA      BBB
  3          CCC      DDD

现在，我只需要显示ID_Number出现两次的那些记录。

预期结果：

 ID_Number   Column1    Column2
 ------------------------------
  1           XYZ        ZYX
  1           MMM        NNN
  3           AAA        BBB
  3           CCC        DDD

我的尝试：

 select * from Test
 group by ID_Number,Column1,Column2 
 having count(ID_Number)>1;

注意：这不会给我任何结果。

所罗门·鲁兹基（Solomon Rutzky）

SET NOCOUNT ON;
DECLARE @Test TABLE
(
   ID_Number INT NOT NULL,
   Column1 VARCHAR(50),
   Column2 VARCHAR(50)
);
INSERT INTO @Test VALUES (1, 'XYZ', 'ZYX');
INSERT INTO @Test VALUES (1, 'MMM', 'NNN');
INSERT INTO @Test VALUES (2, 'III', 'JJJ');
INSERT INTO @Test VALUES (3, 'AAA', 'BBB');
INSERT INTO @Test VALUES (3, 'CCC', 'DDD');

-- Situation #1
;WITH cte AS
(
   SELECT ID_Number
   FROM   @Test
   GROUP BY ID_Number
   HAVING COUNT(*) = 1
)
UPDATE ts
SET    ts.ID_Number = 0
FROM   @Test ts
INNER JOIN cte
        ON cte.ID_Number = ts.ID_Number;

-- Situation #2
;WITH cte AS
(
   SELECT ID_Number
   FROM   @Test
   GROUP BY ID_Number
   HAVING COUNT(*) > 1
)
SELECT ts.*
FROM   @Test ts
INNER JOIN cte
        ON cte.ID_Number = ts.ID_Number;