SQL 统计报表的实现

create table sales(

sid int identity primary key,

pamount numeric(10,2),

sdate date

);

 

insert into sales values(4000,'2012-5-14');

insert into sales values(8000,'2012-3-14');

insert into sales values(4200,'2012-3-26');

insert into sales values(4200,'2012-3-26');

insert into sales values(20000,'2012-4-14');

insert into sales values(4200,'2012-5-12');

insert into sales values(8400,'2012-6-14');

insert into sales values(4000,'2012-2-18');

insert into sales values(12000,'2012-3-14');

insert into sales values(4200,'2012-3-12');

insert into sales values(4200,'2012-4-12');

insert into sales values(8600,'2012-3-12');

insert into sales values(11000,'2012-5-12');

insert into sales values(12000,'2012-4-14');

insert into sales values(4800,'2012-4-18');

 

 

 

--日报表

select sdate,SUM(pamount) as '总额' from tbSales group by sdate;

 

--周报表

select YEAR(sdate) as '年份',DATEPART(week,sdate) as '周次',SUM(pamount) as '总额' from tbSales group by YEAR(sdate),DATENAME(week,sdate);

 

--月报表

select YEAR(sdate) as '年份',MONTH(sdate) as '月份',SUM(pamount) as '总额' from tbSales group by YEAR(sdate),MONTH(sdate);

 

--季度报表

select y as '年份',SUM(case when m between 1 and 3 then total else 0 end) as '一季度',

SUM(case when m between 4 and 6 then total else 0 end) as '二季度',

SUM(case when m between 7 and 9 then total else 0 end) as '三季度',

SUM(case when m between 10 and 12 then total else 0 end) as '四季度' from(

select YEAR(sdate) as y,MONTH(sdate) as m,SUM(pamount) as total from tbSales group by YEAR(sdate),MONTH(sdate) 

) oo group by y;

 

 

--年报表

select YEAR(sdate) as '年分',SUM(pamount) as '总额' from tbSales group by YEAR(sdate);

 

 

 

use master;

go

 

if exists(select * from sysdatabases where name='test')

   drop database test;

go

   

create database test;

go

 

use test;

 

if exists(select * from sys.objects where name='stuinfo')

   drop table stuifo;

go

 

create table stuinfo(--学生

   stuid int primary key identity(1,1),

   sname nvarchar(50) unique,

   sage int not null 

                  constraint CK_age check(sage>=18 and sage<=30),

   saddress nvarchar(50),

   sex varchar(2) default '男'

);

go

   

if exists(select * from sys.objects where name='course')

   drop table course;

go

  

create table course(--科目

  cid int primary key identity(1,1),

  cname nvarchar(50)      

);

go  

   

if exists(select * from sys.objects where name='score')

   drop table score;

go

   

create table score(--成绩

   scoreid int primary key identity(1,1),

   studentid int constraint FK_studentid

                          foreign key references stuinfo(stuid),

   courseid int constraint FK_courseid

                          foreign key references course(cid),

   score int

);

go

   

insert into course values('j2se精讲');

insert into course values('sql server');

insert into course values('html网页设计');

insert into course values('软件测试基础');

   

insert into stuinfo values('张果老',22,null,'男');

insert into stuinfo values('李豹',22,null,'男');

insert into stuinfo values('老胡',22,'北京','男');

insert into stuinfo values('老江',24,'湖南','男');

insert into stuinfo values('张无忌',26,'衡阳','男');

insert into stuinfo values('二师兄',28,'长沙','男');

insert into stuinfo values('凤姐',24,'上海','女');

insert into stuinfo values('元芳',28,'上海','男');

insert into stuinfo values('离歌',23,'上海','男');

insert into stuinfo values('八戒',24,'上海','男');

 

 

 

insert into score values(5,1,95);

insert into score values(5,2,58);

insert into score values(5,3,56);

insert into score values(5,4,81);

insert into score values(5,2,80);

 

insert into score values(1,1,78);

insert into score values(1,3,81);

insert into score values(1,4,92);

 

 

insert into score values(2,3,30);

insert into score values(2,3,78);

insert into score values(3,3,66);

insert into score values(4,3,66);

insert into score values(6,3,78);

insert into score values(7,3,76);

insert into score values(8,2,81);

insert into score values(9,2,90);

insert into score values(10,3,42);

 

insert into score values(2,1,80);

insert into score values(4,2,78);

insert into score values(8,3,69);

insert into score values(8,1,69);

 

 

--按成绩分科分段统计

select * from score

select cname,sum(case when score between 85 and 100 then 1 else 0 end) as [100 - 85],

sum(case when score between 70 and 85 then 1 else 0 end) as [85 - 70],

sum(case when score between 60 and 70 then 1 else 0 end) as [70 - 60],

sum(case when score < 60 then 1 else 0 end) as [60 -0] from score,course where courseid=cid group by cname;  --没有考虑补考的

 

 

--去掉补考的

select cname,sum(case when score between 85 and 100 then 1 else 0 end) as [100 - 85],

sum(case when score between 70 and 85 then 1 else 0 end) as [85 - 70],

sum(case when score between 60 and 70 then 1 else 0 end) as [70 - 60],

sum(case when score < 60 then 1 else 0 end) as [60 -0] from (

select studentid,courseid,cid,cname,max(score) as score from

score,course where courseid=cid group by studentid,courseid,cid,cname) oo where courseid=cid group by cname;

转载于:https://www.cnblogs.com/navyzhou/archive/2013/05/07/3065753.html