SQL Server 更新分组依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2853403/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Update Group by
提问by Gerardo Abdo
I'm trying to execute this on MS-SQL but returns me an error just at the Group by line
我正在尝试在 MS-SQL 上执行此操作，但仅在 Group by line 中返回一个错误
update #temp
Set Dos=Count(1)
From Temp_Table2010 s
where Id=s.Total and s.total in (Select Id from #temp)
group by s.Total
Do anyone knows how can I solve this problem having good performance.
有谁知道我该如何解决这个具有良好性能的问题。
回答by Code Magician
Try
尝试
;with counts
AS
(
SELECT total, COUNT(*) as dos
FROM temp_table2010
WHERE total in (select id from #temp)
)
UPDATE T
SET dos=counts.dos
FROM #temp T
INNER JOIN counts
ON t.id = counts.total
回答by MattInNY
In SQL Server you can do aggregation in an update query you just have to do it in a subquery and then join it on the table you want to update.
在 SQL Server 中，您可以在更新查询中进行聚合，您只需在子查询中进行聚合，然后将其加入要更新的表中。
UPDATE #temp
SET Dos = Cnt
FROM #temp
INNER JOIN (SELECT Total, COUNT(*) AS Cnt FROM Temp_Table2010 GROUP BY Total) AS s
ON Id = s.Total
Doing this:
这样做：
WHERE total in (select id from #temp)
And then:
进而：
INNER JOIN counts
ON t.id = counts.total
Is redundant.
是多余的。
The join solves the "total in (...)" requirement. Group on the key and then join.
连接解决了“total in (...)”要求。对密钥进行分组，然后加入。
回答by SqlRyan
You can't use an aggregate in an UPDATEquery, for starters - though you didn't include the error message in your original question, I suspect that's what it's telling you.
UPDATE对于初学者，您不能在查询中使用聚合- 尽管您没有在原始问题中包含错误消息，但我怀疑这就是它告诉您的。
You'll need to calculate the aggregate before your updateand store the results in a temp table, and then join to that table to do your update.
您需要先计算聚合update并将结果存储在临时表中，然后加入该表以执行您的update.
