sql重复数据查询,sql分组数据查询,sql分组数据的总条数,sql分组后数据的条数（条数大于1的显示）, 删除多余重复数据

sql重复数据查询,

sql分组数据查询,

sql分组数据的总条数,

sql分组后数据的条数（条数大于1的显示）,

删除多余重复数据 ,保留最小ID

sql语句代码 

USE[quanxi_apps]

--1 获取原始数据和原始数据的总条数
select ID,title  
from [dbo].[T_BL_symptom_new]
order by title

select count(title) as '原始数据：总条数'  
from [dbo].[T_BL_symptom_new] 
 

--2 获取分组数据和分组数据的总条数
select title 
from [dbo].[T_BL_symptom_new]
group by title  
order by title

select count(*) as '分组数据：总条数'
from 
(
select title
from [dbo].[T_BL_symptom_new]
group by title
) as T_BL_symptom_new2020


--3 获取分组数据和分组后数据的条数（条数大于1的显示）
select title ,count(title)as '分组数据：条数'
from [dbo].[T_BL_symptom_new]
group by title  
having count(title)>1
 

--4 删除多余重复数据,保留最小ID
delete 
from T_BL_symptom_new
where title in 
(
select title
from [dbo].[T_BL_symptom_new]
group by title  
having count(title)>1
) 
and 
minID not in 
(
select min(minID) 
from [dbo].[T_BL_symptom_new]
group by title 
having count(title)>1
)

删除重复数据保留最小ID--步骤

1.设计表

右键表[表名]>>设计，在表中增加列【minID 】设置成主键、自增、增量1

（其中：主键可忽略）

2.执行sql删除语句

delete 
from [表名]
where (重复字段名) in 
(
select (重复字段名)
from [表名]
group by (重复字段名)
having count((重复字段名))>1
) 
and 
(最小ID字段名) not in 
(
select min((最小ID字段名)) 
from  [表名]
group by (重复字段名)
having count((重复字段名))>1
)

实例sql代码 

delete 
from T_BL_symptom_new
where title in 
(
select title
from [dbo].[T_BL_symptom_new]
group by title  
having count(title)>1
) 
and 
minID not in 
(
select min(minID) 
from [dbo].[T_BL_symptom_new]
group by title 
having count(title)>1
)

 

扩展阅读：

SQL 删除重复记录，并保留其中一条

https://blog.csdn.net/cplvfx/article/details/108192540