求解，一个sql语句
有一个这样的情况,表字段ID Name 其中ID为主键 Name序号 ID NAME1 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 allselect 2,'A','张三' union allselect 3,'A','李四' union allselect 4,'B','王五' union allselect 5,'B','王五' union allselect 6,'B','李四';with tas(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 NAME1 A 张三2 A 张三3 A 张三4 B 王五5 B 王五6 B 王五*/
[解决办法]
方法有些麻烦，试试吧
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 王五*/
[解决办法]
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_selectfrom #users group by ID,NAME ----select * from #user_select----删除 只出现一次的，也就是人名没有异常的数据delete #user_selectfrom (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_selectfrom(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 ID,MAX(c_n) as c_nmax from #user_selectgroup by id) user_nmax,#user_select where user_nmax.id = #user_select.id and user_nmax.c_nmax = #user_select.c_n) user_update where #users.ID = user_update.id and #users.NAME <> user_update.name
[解决办法]
if object_id('tb') is not null drop table tbcreate table tb([序号] int,[ID] varchar(1),[NAME] varchar(4))insert tbselect 1,'A','张三' union allselect 2,'A','张三' union allselect 3,'A','李四' union allselect 4,'B','王五' union allselect 5,'B','王五' union allselect 6,'B','李四'update tb set tb.name=tt.namefrom (select id,(select top 1 t.name from (select a.[NAME],count(1) as c from tb a where a.id=b.id group by [name]) t order by c desc) as name from tb b group by id) ttwhere tb.id=tt.id and tb.name<>tt.nameselect * from tb
[解决办法]
create table tb(序号 int,ID varchar(10),NAME varchar(10))insert into tb values(1 , 'A', '张三')insert into tb values(2 , 'A', '张三')insert into tb values(3 , 'A', '李四')insert into tb values(4 , 'B', '王五')insert into tb values(5 , 'B', '王五')insert into tb values(6 , 'B', '李四')goupdate tb set name = n.namefrom tb m , (select id , name , count(1) cnt from tb group by id , name) nwhere m.id = n.id and n.cnt = (select max(cnt) from (select id , name , count(1) cnt from tb group by id , name) t where t.id = n.id)select * from tb drop table tb/*序号 ID NAME ----------- ---------- ---------- 1 A 张三2 A 张三3 A 张三4 B 王五5 B 王五6 B 王五（所影响的行数为 6 行）*/