sql - 使用案例条款更新

Question

如何在 SQL Management Studio 中使用基于以下查询的 case 子句执行更新：

SELECT 
CASE WHEN t1.building is null THEN 0
ELSE t1.building END AS Building,
t1.id, 
CASE WHEN t1.building is null THEN 0
ELSE t2.count END AS Count
FROM table t1
JOIN (SELECT building, COUNT(*) as count 
  FROM table 
  GROUP BY building) AS t2 ON t2.building = t1.building OR (t2.building is 
  null AND t1.building is null)

以下不起作用：

Update table
Set count=(Select count from table where count in( 
SELECT 
CASE WHEN t1.building is null THEN 0
ELSE t1.building END AS Building,
t1.id, 
CASE WHEN t1.building is null THEN 0
ELSE t2.count END AS Count
FROM table t1
JOIN (SELECT building, COUNT(*) as count 
  FROM table 
  GROUP BY building) AS t2 ON t2.building = t1.building OR (t2.building is 
  null AND t1.building is null))

我的目标是使用基于每个 ID 号的原始查询的值更新计数列。

从：

ID  Building  Count
1    10        
2    10        
3    11        
4    11        
5    11        
6    Null      

期望的结果：

ID  Building  Count
1    10        2
2    10        2
3    11        3
4    11        3
5    11        3
6    Null      0

标签： sqlsql-server

Answer 1

你可以尝试JOIN UPDATE使用CASE WHEN

UPDATE t1
SET Count = CASE WHEN t1.Building IS NULL THEN 0 ELSE t2.cnt END
FROM T t1
LEFT JOIN (
  select Building,COUNT(Building) cnt
  from T
  GROUP BY Building
) t2 on t1.Building = t2.Building 

sqlfiddle