【小5聊】Sql Server基础之分组查询重复出现多条记录的SQL语句，以及group by和having、min函数运用

【开发环境】

开发系统：Windows 10

开发语言：SQL Server

开发工具：SQL Server 2008 R2

文章作用：记录、备忘、总结、分享、理解

相互学习：微信号-xgwkf566

人生格言：勤能补拙

 

【主要知识点】

• group by 分组
• having 筛选条件
• count()函数，总记录数
• min()函数，最小值
• max()函数，最大值
• union all，记录叠加，重复不合并，但是字段必须一致
• union和union all的区别

 

【知识点应用】

1、假如查询记录如下

select * from(
select 1 id,'语文' as kecheng union all
select 2 id,'数学' as kecheng union all
select 3 id,'语文' as kecheng union all
select 4 id,'英语' as kecheng union all
select 5 id,'语文' as kecheng union all
select 6 id,'数学' as kecheng
) as table_

2、分组运用，以及函数min、max、count的运用

select min(id) as minid,max(id) as maxid,count(1) as shuliang,kecheng from (
select 1 id,'语文' as kecheng union all
select 2 id,'数学' as kecheng union all
select 3 id,'语文' as kecheng union all
select 4 id,'英语' as kecheng union all
select 5 id,'语文' as kecheng union all
select 6 id,'数学' as kecheng
) as table_ group by kecheng

3、结合having筛选的使用

select min(id) as minid,max(id) as maxid,count(1) as shuliang,kecheng from (
select 1 id,'语文' as kecheng union all
select 2 id,'数学' as kecheng union all
select 3 id,'语文' as kecheng union all
select 4 id,'英语' as kecheng union all
select 5 id,'语文' as kecheng union all
select 6 id,'数学' as kecheng
) as table_ group by kecheng
having count(kecheng)>2

 

因此，从上面步骤即可排查出现重复的记录

【撸码写文档，我们是认真的】

 

【一句话一感想一心情】版本更新，增加测颜值推荐图文，来体验下句子的魅力，秀出你棒棒的文笔