sql删除重复行和删除字段首位

删除重复行

 user_info：

 

-- 单字段筛选重复行

SELECT *from user_info WHERE NAME in ( SELECT NAME from user_info GROUP BY NAME HAVING COUNT(NAME)>1);

 

 -- 删除重复行，保留id最小的数据
SELECT *from user_info WHERE 
NAME in ( SELECT NAME from user_info GROUP BY NAME HAVING COUNT(NAME)>1)
AND id NOT in ( SELECT min(id) from user_info GROUP BY NAME HAVING COUNT(NAME)>1);

 -- 多字段判断重复

SELECT * from user_info WHERE (name,school) in ( SELECT name,school from user_info group by name,school HAVING COUNT(*)>1);

 

 

删除字段首位末位：

user_info 

 

删除字段左边3位
update user_info set NAME=RIGHT(NAME,LENGTH(NAME)-3) where `name` like 'chu%';

删除字段右边一位

update user_info set NAME=LEFT(NAME,LENGTH(NAME)-1) where `name` ='zeng';