将列更新为另一列中特定值的COUNT行。 SQL Server

My Table is:

我的表是:

|ID |data | cr |
| 1 | AAA |    |
| 2 | AAA |    |
| 3 | AAA |    |
| 4 | BBB |    |
| 5 | CCC |    |
| 6 | BBB |    |

I need result:

我需要结果:

|ID |data | cr |
| 1 | AAA | 3  |
| 2 | AAA | 3  |
| 3 | AAA | 3  |
| 4 | BBB | 2  |
| 5 | CCC | 1  |
| 6 | BBB | 2  |

Found this Update a column value to the COUNT of rows for specific values in same table and tried it:

发现此更新列值为同一表中特定值的COUNT行并尝试:

UPDATE MyTbl a, 
        (SELECT data,COUNT(*) cnt 
            FROM MyTbl 
          GROUP BY data) b
    SET a.cr = b.cnt 
  WHERE a.data= b.data

SQL Server gives error :

SQL Server给出错误:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'b'.

Any idea how to do this in SQL Server (2014 Express).

知道如何在SQL Server(2014 Express)中执行此操作。

Thanks in advance.

提前致谢。

5 个解决方案

#1

It should be Update..set...from. Try this one:

它应该是Update..set ... from。试试这个:

update a
set a.cr=b.cnt
from MyTbl a join
     (SELECT data,COUNT(*) cnt 
            FROM MyTbl 
          GROUP BY data) b on a.data=b.data

Result:

ID  data    cr
--------------
1   AAA     3
2   AAA     3
3   AAA     3
4   BBB     2
5   CCC     1
6   BBB     2

Demo in SQL Fiddle

SQL小提琴中的演示

#2

Something like this

像这样的东西

UPDATE t SET
    t.cr = vv.c
from MyTbl as t
left outer join 
(
    select count(*) as c , data from MyTbl group by data
) as vv on vv.data = t.data

#3

You can use count with window function to find the count of each group. use this

您可以使用带窗口函数的count来查找每个组的计数。用这个

;WITH cte
     AS (SELECT Count(1)OVER(partition BY data) AS crc,*
         FROM   MyTbl)
UPDATE cte
SET    cr = crc

SQLFIDDLE DEMO

#4

Since you are using SQL server 2014 version, I'd recommend Windowed function used inside a self join update query type syntax.

由于您使用的是SQL Server 2014版本,因此我建议在自联接更新查询类型语法中使用Windowed函数。

update a
set a.cr= b.v
from
(select id, count(1) over(partition by data order by data) as v from myTbl) b
join 
myTbl a on a.ID=b.id
-- now see the result here
select * from MyTbl

Sql fiddle for demo http://sqlfiddle.com/#!6/bc02f/4

Sql小提琴演示http://sqlfiddle.com/#!6/bc02f/4

#5

;with x as
 (
  SELECT Data , COUNT(*) AS cr
  FROM Table 
  GROUP BY Data
 )
UPDATE t
 SET t.cr = x.cr
FROM x INNER JOIN Table t ON x.data = t.data

#1