sqlserver常用语句（报表，递归，分页等）

sqlserver

1.创建数据库：

create database DB002

create table Student (
stu_no char(50) not null unique check(len(stu_no)=8),
stu_name char(50) not null unique,
stu_major char(50) ,
stu_sex char(50) check(stu_sex='男' or stu_sex='女'), 
stu_birthday date
)

create table [Subject] (
sub_no char(50) not null unique check(len(sub_no)=2),
sub_name char(50) not null unique,
sub_credit int
)

create table StudentSubject (
stu_no char(50),
sub_no char(50),
grade int check(grade>=0 or grade <= 100)
)

2.修改表

//增加约束
alter table Student add constraint stu_sex check(stu_sex='男' or stu_sex='女')
//增加约束
alter table Student add constraint stu_no check(len(stu_no)=8)
//增加约束
alter table [subject] add constraint sub_no check(len(sub_no)=2)
//修改字段数据类型
alter table studentsubject alter column grade int
//修改字段数据类型
alter table [subject] alter column sub_credit int
//增加约束
alter table studentsubject add constraint grade check(grade>=0 or grade <= 100)
//删除约束
alter table Student drop constraint stu_no
//删除约束
alter table [subject] drop constraint sub_no

3.插入数据

insert into dbo.student(stu_no,stu_name,stu_major,stu_sex,stu_birthday) 
values('20220001','张雪','土木工程','女','2003-04-23 00:00:00:000'),
('20220002','李学周','计算机','男','2004-01-15 00:00:00:000'),
('20220003','王欢','软件工程','男','2002-03-03 00:00:00:000'),
('20220004','赵有才','土木工程','男','2005-01-16 00:00:00:000'),
('20220005','关晓莹','电子','女','2004-07-11 00:00:00:000'),
('20220006','李世友','计算机','男','2005-11-09 00:00:00:000'),
('20220007','叶舒凡','软件工程','女','2003-02-03 00:00:00:000'),
('20220008','刘雪涛','土木工程','男','2005-08-13 00:00:00:000'),
('20220009','吴天放','软件工程','男','2004-12-01 00:00:00:000'),
('20220010','胡雪赫','计算机','男','2004-06-01 00:00:00:000')

select * from dbo.student

insert into dbo.[subject](sub_no,sub_name,sub_credit) 
values('01','计算机基础','5'),
('02','C语言','4'),
('03','数据结构','2'),
('04','计算机网络','3'),
('05','离散数学','2'),
('06','英语','1'),
('07','软件工程','5')

select * from dbo.[subject]

insert into dbo.StudentSubject(stu_no,sub_no,grade) 
values('20220001','01','70'),
('20220002','02','79'),
('20220003','01','98'),
('20220004','03','87'),
('20220005','02','69'),
('20220006','06','78'),
('20220007','01','88'),
('20220008','02','92'),
('20220009','01','81'),
('20220010','03','77'),
('20220001','03','80'),
('20220002','03','78'),
('20220003','04','85'),
('20220004','04','63'),
('20220005','04','72'),
('20220006','06','89'),
('20220007','04','93'),
('20220008','03','67'),
('20220009','03','89'),
('20220010','04','94'),
('20220001','07','66'),
('20220002','04','72'),
('20220003','05','76'),
('20220004','07','83'),
('20220005','05','79'),
('20220006','04','62'),
('20220007','06','77'),
('20220008','04','82'),
('20220009','05','73'),
('20220010','05','89'),
('20220001','04','97'),
('20220002','06','87'),
('20220003','06','77'),
('20220004','06','91'),
('20220005','07','90'),
('20220006','02','77'),
('20220007','07','63'),
('20220008','05','76'),
('20220009','06','83'),
('20220010','07','76')

select * from dbo.studentsubject

4.联表查询：
用sql语句统计每个人的所选学科学分和成绩学分（成绩/100*学分），按照所选学分从高到低显示，输出：学号、姓名、所选学分和成绩学分

select stu.stu_no,stu.stu_name,sum(sub_credit) as credit , sum(ss.grade*sub_credit/100) as grades from dbo.student as stu
left join dbo.StudentSubject as ss on stu.stu_no = ss.stu_no
left join dbo.[subject] as sub on sub.sub_no = ss.sub_no 
group by stu.stu_no,stu_name

5.找出所选学分高于8分的和成绩学分、所选学分对比不低于60%的人，输出：学号、姓名、所选学分和成绩学分

select Student.stu_no,Student.stu_name,sum(sub_credit)credit,sum(grade*sub_credit/100)grade_credit
from Student
left join StudentSubject on  Student.stu_no = StudentSubject.stu_no
left join Subject on  Subject.sub_no = StudentSubject.sub_no
group by  Student.stu_no,Student.stu_name having(sum(grade*sub_credit/100)>=sum(sub_credit)*0.6)

6.按照姓名、各种学科对应的成绩显示，如果没有成绩显示空，（动态二维表，都没有成绩的学科就不显示）

//第一种方法
//查询出要实现的数据
select names = stuff((select ',['+ sub_name +']'  
from StudentSubject left join Subject on
Subject.sub_no = StudentSubject.sub_no
group by sub_name for xml path('')), 1, 1, '') 

//根据上面的结果可动态实现
select t.stu_no,stu_name ,[C语言],[计算机基础],[计算机网络],[离散数学],[软件工程],[数据结构],[英语]
from (
select Student.stu_no,Student.stu_name,sub_name,grade
from Student
left join StudentSubject on  Student.stu_no = StudentSubject.stu_no
left join Subject on  Subject.sub_no = StudentSubject.sub_no
)t1
pivot (  
    sum(grade)  
    for sub_name in   
	([C语言],[计算机基础],[计算机网络],[离散数学],[软件工程],[数据结构],[英语])
) as t 
//第二种方法(容易理解，但是写法麻烦无法动态实现)
select stu_no,stu_name ,sum(C语言)as 'C语言',sum(计算机基础)as '计算机基础',sum(计算机网络)as '计算机网络',sum(离散数学)as '离散数学',sum(软件工程)as '软件工程',sum(数据结构)as '数据结构',sum(英语)as '英语' from (
select Student.stu_no,Student.stu_name ,(case  when Subject.sub_name = 'C语言' then StudentSubject.grade end) as 'C语言'
,(case  when Subject.sub_name = '计算机基础' then StudentSubject.grade end) as '计算机基础'
,(case  when Subject.sub_name = '计算机网络' then StudentSubject.grade end) as '计算机网络'
,(case  when Subject.sub_name = '离散数学' then StudentSubject.grade end) as '离散数学'
,(case  when Subject.sub_name = '软件工程' then StudentSubject.grade end) as '软件工程'
,(case  when Subject.sub_name = '数据结构' then StudentSubject.grade end) as '数据结构'
,(case  when Subject.sub_name = '英语' then StudentSubject.grade end) as '英语'
from Student
left join StudentSubject on  Student.stu_no = StudentSubject.stu_no
left join Subject on  Subject.sub_no = StudentSubject.sub_no
)as t
group by stu_no,stu_name 

7.时间递归

declare @start date = '2015-01-01'
declare @end date = '2022-01-01';
with cte as(
	select @start hs_date
	union all
	select dateadd(month,1,hs_date) from cte
	where hs_date < @end
)
select * from cte

8.按照姓名、成绩（计算机基础：60，英语：50，C语言：60，C语言：60）输出报表

with  cte as (
select Student.stu_no,Student.stu_name,sub_name+':'+cast(grade as nvarchar(10)) sub_name
from Student
left join StudentSubject on  Student.stu_no = StudentSubject.stu_no
left join Subject on  Subject.sub_no = StudentSubject.sub_no)
select stu_no,stu_name,names = stuff((
select distinct '，'+ sub_name from cte temp where temp.stu_no = cte.stu_no
and temp.stu_name = cte.stu_name 
for xml path ('')),1,1,'') from cte
group by stu_no,stu_name

9.分页

/*3~6*/
/*
通过使用top和子查询来实现该需求；
若所求数据为a到b行；
第一个top的值是是查询的行数，即b-a+1
第二个top的值为排除的行数，即a-1
那么第一个top就是6-3+1=4
第二个top的值就是3-1=2
*/
select top 4  stu_no from student where stu_no not in 
(select top 2 stu_no from student) 
/*
使用OFFSET x rows fetch next y rows only实现
x=a-1是前面跳过的行数，即3-1=2
y=b-a+1是查询行数的值，即6-3+1=4
*/
select stu_no from student order by stu_no OFFSET 2 rows fetch next 4 rows ONLY