在T-SQL中设置一个等于计数的列

I am trying to set a column equal to the number of times the values occurs in a table, but running into issues when I try to store that as a column. What am I missing?

我试图设置一个列等于表中值发生的次数,但当我尝试将其存储为列时遇到问题。我错过了什么?

Goal

   id col1 count
  --------------
   1   a    3
   2   a    3
   3   a    3
   4   b    2
   5   b    2

I've tried:

select count(col1) as repidck 
from [User] u 
group by u.id

which works by itself, but when I try to set a column I get

它本身就可以工作,但是当我尝试设置一个列时,我得到了

update [User] 
set [count] = (select count(col1) as repidck 
               from [User] u 
               group by u.id)

Error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

子查询返回的值超过1。当子查询遵循=,!=,<,<=,>,> =或子查询用作表达式时,不允许这样做。

3 个解决方案

#1

You can use a correlated subquery. One way is:

您可以使用相关子查询。一种方法是:

update u
    set [count] = (select count(col1) from [User] u2 where u2.id = u.id)
    from [User] u;

But I would probably use an updatable CTE:

但我可能会使用可更新的CTE:

with toupdate as (
      select u.*, count(u.col1) over (partition by u.id) as new_count
      from [User] u
     )
update toupdate
    set [count] = new_count;

Note: count and user are lousy names for identifiers because they conflict with SQL keywords.

注意:count和user是标识符的糟糕名称,因为它们与SQL关键字冲突。

#2

update [User] u1
set [count] = (select count(*) 
               from [User] u2 
               where u1.col1 = u2.col1)

#3

I would typically approach this by creating a FROM clause that calculates the data I want, then join it back into the original table.

我通常会通过创建一个计算我想要的数据的FROM子句来解决这个问题,然后将它连接回原始表。

UPDATE [user]
SET [count] = repidck
FROM
    [user]
    INNER JOIN 
    (
        SELECT col1, COUNT(*) repidck
        FROM [user]
        GROUP BY col1
    ) counts
        ON counts.col1 = [user].col1

Hope this helps

希望这可以帮助

#1