有一个这样的情况,表字段
ID Name 其中ID为主键 Name

序号    ID     NAME
1        A    '张三'
2        A    '张三'
3        A    '李四'
4        B    '王五'
5        B    '王五'
6        B    '李四'

其中ID=A的 Name等于‘张三’最多，所以序号3的Name 也需要修改为’张三‘
ID=B的Name 等于’王五‘最多，所以序号6的记录Name也修改成为’王五‘

这些记录。求一个sql语句来操作。。。。

这些sql，我要定时sql

if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([序号] int,[ID] varchar(1),[NAME] varchar(4))
insert [tbl]
select 1,'A','张三' union all
select 2,'A','张三' union all
select 3,'A','李四' union all
select 4,'B','王五' union all
select 5,'B','王五' union all
select 6,'B','李四'

;with t
as(
select ROW_NUMBER()over(partition by [ID] order by times desc) as num,* from(
select [ID],[NAME],COUNT([NAME]) as times from tbl group by [ID],[NAME])a)
update tbl set NAME=(select NAME from t where num=1 and t.ID=tbl.ID)
select * from tbl

/*
序号    ID    NAME
1    A    张三
2    A    张三
3    A    张三
4    B    王五
5    B    王五
6    B    王五
*/

------解决方案--------------------
方法有些麻烦，试试吧
SQL code

CREATE TABLE Table1
(
    序号 INT NOT NULL,
    ID VARCHAR(10) NOT NULL,
    Name VARCHAR(10) NOT NULL
)

INSERT INTO Table1 
SELECT  1,'A','张三' UNION 
SELECT  2,'A','张三' UNION 
SELECT  3,'A','李四' UNION 
SELECT  4,'B','王五' UNION 
SELECT  5,'B','王五' UNION 
SELECT  6,'B','李四'

UPDATE Table1 SET name = C.MaxName FROM (select A.Id AS MaxId,A.Name AS MaxName 
                                         from (SELECT ID,Name,COUNT(1) AS Num
                                               FROM Table1
                                               GROUP BY ID,Name) AS A 
                                         where not exists(select 1 from (SELECT ID,Name,COUNT(1) AS Num
                                                                         FROM Table1
                                                                         GROUP BY ID,Name) AS B 
                                                          where ID = A.Id  and Num > A.Num)) AS C
                                WHERE ID = C.MaxId
                            
                            
SELECT * FROM table1    

/*
1    A    张三
2    A    张三
3    A    张三
4    B    王五
5    B    王五
6    B    王五
*/

------解决方案--------------------
SQL code

create table #users
(序号 int,
ID varchar(10),
NAME varchar(20))

insert into #users(序号,ID,NAME) values (1,'A','张三')
insert into #users(序号,ID,NAME) values (2,'A','张三')
insert into #users(序号,ID,NAME) values (3,'A','李四')
insert into #users(序号,ID,NAME) values (4,'B','王五')
insert into #users(序号,ID,NAME) values (5,'B','王五')
insert into #users(序号,ID,NAME) values (6,'B','李四')
insert into #users(序号,ID,NAME) values (7,'C','赵二')
insert into #users(序号,ID,NAME) values (8,'B','李四')

----select * from #users

---drop table #user_select
----统计相同ID不同人名出现次数
select ID,NAME,COUNT(*) as c_n 
into #user_select
from #users
    group by ID,NAME
    
----select * from #user_select

----删除 只出现一次的，也就是人名没有异常的数据
delete #user_select
from 
(select ID,COUNT(*) as c_n from #user_select
    group by id
    having COUNT(*) = 1) user_only
    where user_only.id = #user_select.id

----删除 不相同的名字出现的次数相同而且次数都是最大值的数据
----等下次执行，数据在外部发生变化，出现个数差异的时候再确定以哪一个为准
delete #user_select
from
(select user_a.id,COUNT(*) as c_n 
    from #user_select user_a,
    (select ID,MAX(c_n) as c_nmax from #user_select
        group by id) user_b
    where user_a.id = user_b.id
    and user_a.c_n = user_b.c_nmax
    group by user_a.id
    having COUNT(*) >1) user_multi
    where user_multi.id = #user_select.id

----已名字出现最多的次数为准，修改原始表    
update #users set NAME = user_update.name
    from 
    (select user_nmax.id,#user_select.name from
(select