当前位置:   article > 正文

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

having min

【开发环境】

开发系统:Windows 10

开发语言:SQL Server

开发工具:SQL Server 2008 R2

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

相互学习:微信号-xgwkf566

人生格言:勤能补拙

 

【主要知识点】

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

 

【知识点应用】

1、假如查询记录如下

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

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

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

3、结合having筛选的使用

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

 

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

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

 

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

 

 

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/我家自动化/article/detail/409834
推荐阅读
相关标签
  

闽ICP备14008679号