SQL SERVER数据库常见数据统计功能

1. 用一条SQL语句 查询出每门课都大于80分的学生姓名（原表数据如下图所示）

 

查询结果如下图所示：

实现该功能的SQL语句代码为：

use db2011

createtable NameOfScoreOver80(StudentName nvarchar(10),ClassName nvarchar(10),Score smallint)

insertinto NameOfScoreOver80 values('张三','语文',81)
insertinto NameOfScoreOver80 values('张三','数学',75)
insertinto NameOfScoreOver80 values('李四','语文',76)
insertinto NameOfScoreOver80 values('李四','数学',90)
insertinto NameOfScoreOver80 values('王武儿','语文',82)
insertinto NameOfScoreOver80 values('王武儿','数学',100)
insertinto NameOfScoreOver80 values('王武儿','英语',90)

--select * from NameOfScoreOver80
selectdistinct StudentName
from NameOfScoreOver80
where StudentName notin(
            select StudentName from NameOfScoreOver80 where Score<80
            )

2.删除一个表中重复的记录（除了自动编号列不同以外），原数据如下图所示：

 

删除重复数据后的数据为：

实现该功能的SQL语句代码为：

use db2011

createtable RemoveDuplicatedRecordsTable(autoid intIDENTITY(1,1) NOTNULL,
                                          studentid int,
                                          studentname nvarchar(10),
                                          classid    nvarchar(4),
                                          classname nvarchar(10),
                                          classscore smallint)
insertinto RemoveDuplicatedRecordsTable(studentid,
                                         studentname,
                                         classid,
                                         classname,
                                         classscore) values(2005001,'张三','0001','数学',69)
insertinto RemoveDuplicatedRecordsTable(studentid,
                                         studentname,
                                         classid,
                                         classname,
                                         classscore) values(2005002,'李四','0001','数学',89)
insertinto RemoveDuplicatedRecordsTable(studentid,
                                         studentname,
                                         classid,
                                         classname,
                                         classscore) values(2005001,'张三','0001','数学',69)
insertinto RemoveDuplicatedRecordsTable(studentid,
                                         studentname,
                                         classid,
                                         classname,
                                         classscore) values(2005001,'张三','0001','数学',69)
--select * from RemoveDuplicatedRecordsTable
deletefrom RemoveDuplicatedRecordsTable 
where autoid notin (
                        selectmin(autoid) as autoid 
                        from RemoveDuplicatedRecordsTable
                        groupby studentid,studentname,classid,classname,classscore
                    )

3.针对公司销售数据按【年、月份、销售额】存储数据，查询报表需要显示按月进行横向铺开的需求，编写SQL查询语句：

 

原销售数据截图为：

查询出来的数据截图为：

把年、月、销售额数据按月进行横向铺开的SQL代码为：

use db2011

createtable MonthSaleDetail(YearId int,MonthId smallint,Achievement nvarchar(20))

insertinto MonthSaleDetail values(2010,8,'3.2亿元')
insertinto MonthSaleDetail values(2010,9,'3.3亿元')
insertinto MonthSaleDetail values(2010,10,'3.4亿元')
insertinto MonthSaleDetail values(2010,11,'3.5亿元')
insertinto MonthSaleDetail values(2010,12,'4.2亿元')
insertinto MonthSaleDetail values(2011,1,'4.2亿元')
insertinto MonthSaleDetail values(2011,2,'4.6亿元')
insertinto MonthSaleDetail values(2011,3,'4.65亿元')
insertinto MonthSaleDetail values(2011,4,'4.8亿元')
insertinto MonthSaleDetail values(2011,5,'4.8亿元')
insertinto MonthSaleDetail values(2011,6,'4.9亿元')

--select * from MonthSaleDetail 

select YearId,
        (select Achievement from MonthSaleDetail M where MonthId=1and M.YearId=N.YearId) as'一月',
        (select Achievement from MonthSaleDetail M where MonthId=2and M.YearId=N.YearId) as'二月',
        (select Achievement from MonthSaleDetail M where MonthId=3and M.YearId=N.YearId) as'三月',
        (select Achievement from MonthSaleDetail M where MonthId=4and M.YearId=N.YearId) as'四月',
        (select Achievement from MonthSaleDetail M where MonthId=5and M.YearId=N.YearId) as'五月',
        (select Achievement from MonthSaleDetail M where MonthId=6and M.YearId=N.YearId) as'六月',
        (select Achievement from MonthSaleDetail M where MonthId=7and M.YearId=N.YearId) as'七月',
        (select Achievement from MonthSaleDetail M where MonthId=8and M.YearId=N.YearId) as'八月',
        (select Achievement from MonthSaleDetail M where MonthId=9and M.YearId=N.YearId) as'九月',
        (select Achievement from MonthSaleDetail M where MonthId=10and M.YearId=N.YearId) as'十月',
        (select Achievement from MonthSaleDetail M where MonthId=11and M.YearId=N.YearId) as'十一月',
        (select Achievement from MonthSaleDetail M where MonthId=12and M.YearId=N.YearId) as'十二月'
from MonthSaleDetail N groupby YearId

4. 有一个表数据（具有自动增长列AutoId），选择第31至40条数据

 

查询SQL语句如下：

use db2011

createtable RecordsFrom30To40(AutoId intidentity(1,1) notnull,Msg nvarchar(100))

declare@iint
declare@maxint
declare@itemnvarchar(100)
set@i=1
set@max=50
while(@i<=@max)
begin
    set@item='this is message '+cast(@iaschar)
    set@i=@i+1
    insertinto RecordsFrom30To40(Msg) values(@item)
end

selecttop10*from RecordsFrom30To40 where AutoId notin(selecttop30 AutoId from RecordsFrom30To40)

-- drop table RecordsFrom30To40

5.关于Row_Number()函数和Over函数的使用

 

现有表数据（员工工号、基本工资、补助工资）截图为：

现在需要输出工号、基本工资、补助工资、工资和、同时显示出工资高低排名；效果截图如下：

实现输出该格式数据的SQL语句代码为：

use db2011

begintran
    createtable Wages(EmpId bigintnotnullprimarykey, BasicSalary money,ExtraSalary money) 
    go
    insertinto Wages(EmpId,BasicSalary,ExtraSalary)values(1,1.00,1.00) 
    insertinto Wages(EmpId,BasicSalary,ExtraSalary)values(2,1.00,2.00) 
    insertinto Wages(EmpId,BasicSalary,ExtraSalary)values(3,1.00,3.00) 
    insertinto Wages(EmpId,BasicSalary,ExtraSalary)values(4,1.00,4.00) 
    if@@error>0
        rollback
    else
    committran
--select * from Wages
select EmpId,
        BasicSalary,
        ExtraSalary,
        BasicSalary+ExtraSalary as TotalSalary,
        ('NO.'+cast(row_number() over(orderby BasicSalary+ExtraSalary desc) asnvarchar(5))) as RangeNo 
from Wages orderby TotalSalary 

--drop table Wages