简单常用的sql,统计语句，陆续整理添加吧

 1. 分段统计分数

    

if object_id('[score]') is not null drop table [score]
go
create table [score]([学号] int,[课程编号] varchar(8),[成绩] int)
insert [score]
select 2006091001,'04010101',75 union all
select 2006091001,'04010102',84 union all
select 2006091001,'04010103',68 union all
select 2006091001,'04010104',68 union all
select 2006091002,'04010101',86 union all
select 2006091002,'04010102',90 union all
select 2006091002,'04010103',67 union all
select 2006091003,'04010101',74 union all
select 2006091003,'04010102',45 union all
select 2006091004,'04010101',72 union all
select 2006091005,'04010101',56
  
---查询---
select 
  课程编号,
  [80分上]=sum(case when 成绩>=80 then 1 else 0 end),
  [80分下]=sum(case when 成绩<80 then 1 else 0 end),
  [合计]=count(1)
from
  score
group by
  课程编号
 
 
---结果---
课程编号     80分上        80分下        合计          
-------- ----------- ----------- ----------- 
04010101 1           4           5
04010102 2           1           3
04010103 0           2           2
04010104 0           1           1
 
（所影响的行数为 4 行）

2. 查询，删除重复数据

 

 
查询：
select * from admin a  ,  (
 
 SELECT   b.password,b.reallyname
  FROM [db_Blog].[dbo].[Admin] b  group by password,reallyname
  having count(*)>1 )  b where a.password=b.password and a.reallyname=b.reallyname
  
   

删除：
  delete from admin where id   in (  select min(id) from admin group by userName,password having count(*)>1)

)