进阶sql
A. sql Server数据库进阶考试题 要求用SQL语句实现（全部利用SQL语句完成）
create database empsystem
on (filename='d:\database\empsystem_data.mdf',
size=3mb,maxsize=20mb,filegrowth=2mb)
go
--use empsystem
create table Departments(
DepartId int not null primary key,
departname nvarchar(50),
location nvarchar(100))
create table Employees(
EmpId Int IDENTITY(1,1) primary key ,
EmpName Nvarchar(50),
Job Nvarchar(50),
MgrId int,
HireDate date,
Salary float,
Commission float ,
DepartId int REFERENCES Departments(DepartId))
go
insert Departments values(1,'市场部','湖北武汉')
insert Departments values(2,'销售部','湖北武汉')
insert Departments values(3,'研发部','湖北武汉')
insert Departments values(4,'人事部','湖北武汉')
insert Departments values(5,'企划部','湖北武汉')
insert Departments values(6,'产品部','湖北武汉')
go
insert Employees(Empname,Job,MgrId,HireDate,Salary,Commission,DeptId) values
('小刘','程序员',2,'2009-0708',2200,1500,3)
go
--1
select empname 姓名,job 工作,HireDate 入职日期,salary+commission 工资 from employees
where (salary+commission)>(select salary+commission from employees where empname='陈芳')
--2
select job 工作,salary+commission 工资 from employees
where (salary+commission)>3000
--3
create procere proce1
@name Nvarchar(50) ,@mgrId int output
as
select @mgrId=mgrId from employees where empname=@name
--4
--5
ALTER TABLE departments ADD DepartId int
CONSTRAINT DepartId_pk PRIMARY KEY
B. SQL进阶教程
网络网盘兄搜锋地址：https://pan..com/s/1cZIHvO43AJRhaXDxmCfaMg
解压码：漏租rzy2
封羡晌面图片
C. SQL进阶 1-2 自连接的用法
1.针对相同的表进行连接的技术称为自连接。
2.集合是SQL能处理唯一的数据结构。
3.自连接的性能开销很大(特别是与非等值连接结合使用的时候，用于自连接的列推荐使用主键或者相关列上建立索引)。
select p1.name as name_1, p2.name as name_2 from Procts p1, Procts p2
where p1.name >= p2.name
方法一：窗口函数
select district, name, price, rank() over (partition by distinct order by price DESC) as rank_1
from DistrictProcts;
partitition by 具体将表分割李让成若干个小的子集的作用。因为本题以地区作为分割条件，所以指定distinct列。
方哪丛局法二：标量子查询
select p1.district, p1.name, p1.price,
(select count(p2.price) from DistrictProcts p2
where p1.district = p2.district --在同一个地区内进行比较
and p2.price > p1.price) + 1 as rank_1
from DistrictProcts p1；
方法三：自连接
select p1.district, p1.name, max(p1.price) as price, count(p2.name)+1 as rank_1
from DistrictProcts p1 left outer join DistrictProcts p2
on p1.district = p2.district
and p1.price < p2.price
group by p1.district,p1.name
UPDATE DistrictProcts2 P1
SET ranking = (SELECT COUNT(P2.price) + 1
FROM DistrictProcts2 P2
WHERE P1.district = P2.district
AND P2.price >郑明 P1.price);
方法一：在update语句的set子句中加入计算位次的逻辑
update DistrictProcts1 p1 set ranking = (select count(p2.price) + 1 from DistrictProcts2 p2
where p1.district = p2.district and p2.price > p1.price )
方法二：窗口函数
update DistrictProcts2 set ranking = rank() over(partition by district order by price desc)
D. 数据库进阶：循序渐进讲解SQL查询语句的高级应用技巧[1]
一 简单查询
简单的Transact SQL查询只包括选择列表 FROM子句和WHERE子句 它们分别说明所查询列 查询的
表或视图 以及搜索条件等
例如 下面的语句查询testtable表中姓名为 张三 的nickname字段和email字段
SELECT nickname emailFROM testtable
WHERE name= 张三
(一) 选择列表
银毕选择列表(select_list)指出所查询列 它可以是一组列名列表 星号 表达式 变量(包括局部变
量和全局变量)等构成
选择所有列
例如 下面语句显示testtable表中所有列的数据
SELECT *FROM testtable
选择部分列并指定它们的显示次序
查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同
例如
SELECT nickname emailFROM testtable
更改列标题
在选择列表中 可重新指定列标题 定义格式为
列标题=列名
列名 列标题
如果指定的列标题不是标准的标识符格式时 应使用引号定界符 例如 下列语句使用汉字显示列
标题
SELECT 昵称=nickname 电子邮件=email
FROM testtable
删除重复行
SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行 默认
为ALL 使用DISTINCT选项时 对于所有重复的数据行在SELECT返回的结果集合中只保留一行
限制返回的行数
使用TOP n [PERCENT]选项限制返回的数据行数 TOP n说明返回n行 而TOP n PERCENT时 说明n是
表示一百分数 指定返回的行数等于总行数的百分之几
例如
SELECT TOP *FROM testtableSELECT TOP PERCENT *FROM testtable
(二) FROM子句
FROM子句指定SELECT语句查询及与查询相关的表或视图 在FROM子句中最多可指定 个表或视图
册知它们之间用逗号分隔
在FROM子句同时指定多个表或视图时 如果选择列表中存在同名列 这时应使用对象名限定这些列
所属的表或视图 例如在usertable和citytable表中同时存在cityid列 在查询两个表中的cityid时应
使用下面语句格式加以限定
SELECT username citytable cityidFROM usertable citytableWHERE usertable cityid=citytable cityid
在FROM子州搏消句中可用以下两种格式为表或视图指定别名
表名 as 别名
表名 别名
例如上面语句可用表的别名格式表示为
SELECT username b cityidFROM usertable a citytable bWHERE a cityid=b cityid
SELECT不仅能从表或视图中检索数据 它还能够从其它查询语句所返回的结果集合中查询数据
例如
SELECT a au_fname+a au_lnameFROM authors a titleauthor ta(SELECT title_id titleFROM titlesWHERE ytd_sales> ) AS tWHERE a au_id=ta au_idAND ta title_id=t title_id
此例中 将SELECT返回的结果集合给予一别名t 然后再从中检索数据
(三) 使用WHERE子句设置查询条件
WHERE子句设置查询条件 过滤掉不需要的数据行 例如下面语句查询年龄大于 的数据
SELECT *FROM usertableWHERE age>
WHERE子句可包括各种条件运算符
比较运算符(大小比较) > >= = < <= <> !> !<
范围运算符(表达式值是否在指定的范围) BEEEN…AND…
NOT BEEEN…AND…
列表运算符(判断表达式是否为列表中的指定项) IN (项 项 ……)
NOT IN (项 项 ……)
lishixin/Article/program/SQL/201311/16245
E. 数据库表查询进阶（1）
in 操作符允许我们在 WHERE 子句中规定多个值。
where 字段名 in (值1,值2,值3....)
示例: where age in (20,21,25);
age 字段的值 只要明衡森是 20或者21或者25 的数据 都满足条件
where not in (值1,值2,值3....)</pre>
示例: where age in (20,21,25);
age 字段的值 只要 不是 20或者21或者25 的数据 都满足条拦旅件
select *
from sc
where score in (70,80,90);
select *
from sc
where score not in (70,80,90);
select *
from student
where birthday in (-01-01',-12-21',-12-20',-06-06');</pre>
select *
from student
where birthday not in (-01-01',-12-21',-12-20',-06-06');
MySQL中的分页查询, limit 通常放在sql 语句的最末尾
limit 4
查询前4条数据
limit 4,3
从第4条数据之后开始,查询3条数据
也就是 第5,6,7条的数据
limit 4 offset 10;
offset后面的数字是指记录数
从第10条 之后的数据开始 查询4条数据
也就是 第 11,12,13,14条数据
select * from student limit 5;
select * from student limit 9,6;
select * from student limit 6 offset 9;
查询成绩表中 前7条的数据；
select * from sc limit 7;
查询成绩表中 第 5到10 条的数据
select * from sc limit 4,6;
查询成绩表中 第 3到8 条的数据;
select * from sc limit 2 , 6;
如果字段没有要求非空,则字段值 可以为空,就是null值。如果要查询某个 字段 为 null的数据，不能使用 字段名=null，要用 字段名 is null；
where 字段名 is null；
字段名 为空的符合条件
where 字段名 is not null；
字段名 不为空的符合条件
1.查询teacher表，给tid 起别名为 教师编号，tname 起别名为 教师姓名
select tid as 教师编号，tname as 教师姓名 from teacher
2.查询 sc 表， 给 cid 起别名为 课程编号，sid 起别名为 学生编号， score起别名为分数；
select cid 课程编号 ,sid 学生编号,score 分数 from sc
3.查询 course 表，给表起别名为 c，使用 c.字段查询 表数据
select c.* from course as c ;
起别名是为了后续学习多表联查 做铺垫，语法激亩十分简单
比如查询 student 表的 学生姓名 和 生日 的sql语句：
select sname,birthday from student;
给 sname，birthday字段 起别名 ，改为中文
select sname as 姓名,birthday as 生日 from student;
起了别名后，查出来的字段会以别名展示。
as 关键字也可以省略，字段名 和 别名之间 用空格隔开即可
select sname 姓名, birthday 生日 from student;
例子：
select a.sname, a.birthday from student as a;
给表 起别名 同样是 使用 as关键字（可以使用空格隔开代替as关键字）， a 就代表 student表，查询字段时，可以 使用 a.sname 代表 student的sname字段。
给表起别名 通常用在 多表查询，本次使用演示，先熟悉语法。
在某些复杂的场景下，可能需要多条sql 才能查询出想要的数据，把多条sql拼接起来 就是嵌套查询
句式1
select XXX from A表 where xx =/in （select XXX from B表）
句式2
select XXX from （select XXX from XXXX）b
句式3
select XXX from A表 a
join （select XXX from B表）b on a.xx = b.xx
select * from sc where sid = (select sid from student where sname = '周梅');
select * from sc where sid in (select sid from student where sex='女');
select a.sname,b.score,c.score from student a
inner join (select sid,score from sc where cid =1) b on a.sid = b.sid
inner join (select sid,score from sc where cid = 2) c on a.sid = c.sid
where b.score › c.score;
聚合函数对一组值执行计算，并返回单个值，也被称为组函数。
就是sql提供的一种查询方法，比如查询 最大值，最小值，总数，平均等等。
聚合函数不能写在where的查询条件里面
查询 最小值
select min(字段) from 表；
查询 最大值
select max(字段) from 表；
select max(tid) from teacher;
select min(tid) from teacher;
select max(a.score) ,b.cname from sc a
inner join course b on a.cid=b.cid
where a.sid =(select sid from student where sname='周梅') group by b.cname limit 1 ;
查询 该字段的 总数
select sum(字段) from 表
查询 该字段的 平均数
select avg(字段) from 表
select avg(score) from sc;
select avg(s.score) from student a inner join sc s on a.sid = s.sid
where a.sex ='女'
查询 数据 总条数
count( ) 是查询总共有多少条数据
count(字段) 是查询该字段 不为 null的 总共有多少条数据
例：
select count( ) from 表
去重，查询数据时，相同的数据只展示 一条
语法：
select distinct 字段 from 表;
例：去重查询学生表中的生日字段
select distinct birthday from student;
F. sql 语句的问题 进阶
2.我想得到用户表中所有重复email的 用户ID。
select userid from user where email in
(
select email from user group by email having count(email)>1
)
1.我想多显示出一列 userid 用户ID。
因为有group by email 分组，所以你不能直接找出userid
select userid,email,count(email) from user group by userid, email having count(email)>1
估计就不是你想要的知肆结果了，搭燃轿这是显示userid和email都重复的纪录
select userid，email from user where email in
(
select email from user group by email having count(email)>1
)
看看是段唯不是你要的，不然就再详细描述下想得到什么样的结果
G. 数据库进阶：数据库管理员日常工作中必备的sql列表[3]
者森 查看用户的回滚段的信息
select s username rn name from v$session s v$transaction t v$rollstat r v$rollname rnwhere s saddr = t ses_addr and t xisn = r usn and r usn = rn usn
生成枣培执行计划
explain plan set statement_id= a for & ;
查看执行计划
首岩亩select lpad( *(level ))||operation operation options OBJECT_NAME position from plan_tablestart with id= and statement_id= a connect by prior id=parent_id and statement_id= a
查看内存中存的使用
select decode(greatest(class ) decode(class Data Sort Header to_char(class)) Rollback ) Class sum(decode(bitand(flag ) )) Not Dirty sum(decode(bitand(flag ) )) Dirty sum(dirty_queue) On Dirty count(*) Total from x$bh group by decode(greatest(class ) decode(class Data Sort Header to_char(class)) Rollback );
查看表空间状态
select tablespace_name extent_management segment_space_management from dba_tablespaces;select table_name freelists freelist_groups from user_tables;
查看系统请求情况
SELECT DECODE (name summed dirty write queue length value)/DECODE (name write requests value) Write Request Length FROM v$sysstat WHERE name IN ( summed dirty queue length write requests ) and value> ;
计算data buffer命中率
select a value + b value logical_reads c value phys_reads round( * ((a value+b value) c value) / (a value+b value)) BUFFER HIT RATIO from v$sysstat a v$sysstat b v$sysstat cwhere a statistic# = and b statistic# = and c statistic# = ;SELECT name ( (physical_reads/(db_block_gets+consistent_gets)))* H_RATIO FROM v$buffer_pool_statistics;
查看内存使用情况
select least(max(b value)/( * ) sum(a bytes)/( * )) shared_pool_used max(b value)/( * ) shared_pool_size greatest(max(b value)/( * ) sum(a bytes)/( * )) (sum(a bytes)/( * )) shared_pool_avail ((sum(a bytes)/( * ))/(max(b value)/( * )))* avail_pool_pctfrom v$sgastat a v$parameter b where (a pool= shared pool and a name not in ( free memory )) and b name= shared_pool_size ;
查看用户使用内存情况
select username sum(sharable_mem) sum(persistent_mem) sum(runtime_mem)from sys v_$sqlarea a dba_users bwhere a parsing_user_id = b user_id group by username;
查看对象的缓存情况
select OWNER NAMESPACE TYPE NAME SHARABLE_MEM LOADS EXECUTIONS LOCKS PINS KEPTfrom v$db_object_cache where type not in ( NOT LOADED NON EXISTENT VIEW TABLE SEQUENCE )and executions> and loads> and kept= NO order by owner namespace type executions desc;select type count(*) from v$db_object_cache group by type;
查看库缓存命中率
select namespace gets gethitratio* gethitratio pins pinhitratio* pinhitratio RELOADS INVALIDATIONS from v$librarycache
查看某些用户的hash
select a username count(b hash_value) total_hash count(b hash_value) count(unique(b hash_value)) same_hash (count(unique(b hash_value))/count(b hash_value))* u_hash_ratiofrom dba_users a v$sqlarea b where a user_id=b parsing_user_id group by a username;
查看字典命中率
select (sum(getmisses)/sum(gets)) ratio from v$rowcache;
查看undo段的使用情况
SELECT d segment_name extents optsize shrinks aveshrink aveactive d statusFROM v$rollname n v$rollstat s dba_rollback_segs dWHERE d segment_id=n usn(+) and d segment_id=s usn(+);
无效的对象
select owner object_type object_name from dba_objects where status= INVALID ;select constraint_name table_name from dba_constraints where status= INVALID ;
求出某个进程 并对它进行跟踪
select s sid s serial# from v$session s v$process p where s paddr=p addr and p spid=& ;exec dbms_system SET_SQL_TRACE_IN_SESSION(& & true);exec dbms_system SET_SQL_TRACE_IN_SESSION(& & false);
求出锁定的对象
select do object_name session_id process locked_modefrom v$locked_object lo dba_objects do where lo object_id=do object_id;
求当前session的跟踪文件
SELECT p value || / || p value || _ora_ || p spid || ora filenameFROM v$process p v$session s v$parameter p v$parameter p WHERE p name = user_mp_dest AND p name = instance_name AND p addr = s paddr AND s audsid = USERENV( SESSIONID ) AND p background is null AND instr(p program CJQ ) = ;
求对象所在的文件及块号
select segment_name header_file header_blockfrom dba_segments where segment_name like & ;
求对象发生事务时回退段及块号
select a segment_name a header_file a header_blockfrom dba_segments a dba_rollback_segs bwhere a segment_name=b segment_name and b segment_id= &
i的在线重定义表
/*如果在线重定义的表没有主键需要创建主键*/exec dbms_redefinition can_redef_table( cybercafe announcement );create table anno as select * from announcementexec dbms_redefinition start_redef_table( cybercafe announcement anno );exec dbms_redefinition sync_interim_table( cybercafe announcement anno );exec dbms_redefinition finish_redef_table( cybercafe announcement anno );drop table anno exec dbms_redefinition abort_redef_table( cybercafe announcement anno );
常用的logmnr脚本(cybercafe)
exec sys dbms_logmnr_d build(dictionary_filename => esal dictionary_location => /home/oracle/logmnr );exec sys dbms_logmnr add_logfile(logfilename=> /home/oracle/oradata/esal/archive/ _ dbf ptions=>sys dbms_logmnr new);exec sys dbms_logmnr add_logfile(logfilename=> /home/oracle/oradata/esal/archive/ _ dbf ptions=>sys dbms_logmnr addfile);exec sys dbms_logmnr add_logfile(logfilename=> /home/oracle/oradata/esal/archive/ _ dbf ptions=>sys dbms_logmnr addfile);exec sys dbms_logmnr add_logfile(logfilename=> /home/oracle/oradata/esal/archive/ _ dbf ptions=>sys dbms_logmnr addfile);exec sys dbms_logmnr start_logmnr(dictfilename=> /home/oracle/logmnr/esal ora );create table logmnr as select * from v$logmnr_contents;
与权限相关的字典
ALL_COL_PRIVS表示列上的授权 用户和PUBLIC是被授予者ALL_COL_PRIVS_MADE表示列上的授权 用户是属主和被授予者ALL_COL_RECD表示列上的授权 用户和PUBLIC是被授予者ALL_TAB_PRIVS表示对象上的授权 用户是PUBLIC或被授予者或用户是属主ALL_TAB_PRIVS_MADE表示对象上的权限 用户是属主或授予者ALL_TAB_PRIVS_RECD表示对象上的权限 用户是PUBLIC或被授予者DBA_COL_PRIVS数据库列上的所有授权DBA_ROLE_PRIVS显示已授予用户或其他角色的角色DBA_SYS_PRIVS已授予用户或角色的系统权限DBA_TAB_PRIVS数据库对象上的所有权限ROLE_ROLE_PRIVS显示已授予用户的角色ROLE_SYS_PRIVS显示通过角色授予用户的系统权限ROLE_TAB_PRIVS显示通过角色授予用户的对象权限SESSION_PRIVS显示用户现在可利用的所有系统权限USER_COL_PRIVS显示列上的权限 用户是属主 授予者或被授予者USER_COL_PRIVS_MADE显示列上已授予的权限 用户是属主或授予者USER_COL_PRIVS_RECD显示列上已授予的权限 用户是属主或被授予者USER_ROLE_PRIVS显示已授予给用户的所有角色USER_SYS_PRIVS显示已授予给用户的所有系统权限USER_TAB_PRIVS显示已授予给用户的所有对象权限USER_TAB_PRIVS_MADE显示已授予给其他用户的对象权限 用户是属主USER_TAB_PRIVS_RECD显示已授予给其他用户的对象权限 用户是被授予者
如何用dbms_stats分析表及模式？
exec dbms_stats gather_schema_stats(ownname=> &USER_NAME estimate_percent=>dbms_stats auto_sample_size method_opt => for all columns size auto degree=> DBMS_STATS DEFAULT_DEGREE);exec dbms_stats gather_schema_stats(ownname=> &USER_NAME estimate_percent=>dbms_stats auto_sample_size cascade=>true);/*FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]FOR COLUMNS [size clause] column|attribute [size_clause] [ column|attribute [size_clause] ] where size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}integer Number of histogram buckets Must be in the range [ ] REPEAT Collects histograms only on the columns that already have histograms AUTO Oracle determines the columns to collect histograms based on data distribution and the workload of the columns SKEWONLY Oracle determines the columns to collect histograms based on the data distribution of the columns*/
lishixin/Article/program/SQL/201311/16148
H. 怎么进阶自己的sql
多练自然熟
I. 数据库进阶：SQL Server数据库多种方式查找重复记录
SQL Server数据库多种方式查找重复记录:
示例 表stuinfo 有三个字段recno(自增) stuid stuname
建该表的Sql语句如下
CREATE TABLE [StuInfo] ([recno] [int] IDENTITY ( ) NOT NULL [stuid] [varchar] ( ) COLLATE Chinese_PRC_CI_AS NOT NULL [stuname] [varchar] ( ) COLLATE Chinese_PRC_CI_AS NOT NULL) ON [PRIMARY]GO
查某一列(或多列)的重复值(只可以查出重复记录的值 不能查出整个记录的信息)
例如:查找stuid stuname重复的记录
select stuid stuname from stuinfogroup by stuid stunamehaving(count(*))>
查某一列有重复宴辩值的记局侍录(此方法查出的是所有重复的记录 如果有两条记录重复的 就查出两条)
例如:查找stuid重复的记录
select * from stuinfowhere stuid in (select stuid from stuinfogroup by stuidhaving(count(*))> 桐祥吵)
查某一列有重复值的记录(只显示多余的记录 也就是说如果有三条记录重复的 就显示两条)
前提 需有一个不重复的列 此示例为recno
例如:查找stuid重复的记录
lishixin/Article/program/SQLServer/201311/22365
J. JAVA进阶：提高SQL性能的几种方法
从 INSERT 返回 IDENTITY
SELECT @@IDENTITY
内嵌视图与临时表
临时表 在 tempdb 中的临时表会导致查询进行大量 I/O 操作和磁盘访问 临时表会消耗大量资源
内嵌视图 使用内嵌视图取代临时表 内嵌视图只是一个可以联接到 FROM 子句中的查询 如果只需要将数据联接到其他查询 则可以试试使用内嵌视图 以节省资源
避免 LEFT JOIN 和 NULL
LEFT JOIN 消耗的资源非常之多 因为它们包含与 NULL(不存在)数据匹配的数据 在某些情况下 这是不可避免的 但是代价可能非常高 LEFT JOIN 比 INNER JOIN 消耗资源更多 所以如果您可以重新编写查询以使得该查询不使用任何 LEFT JOIN 则会得到非常可观的回报
加快使用 LEFT JOIN 的查询速度的一项技术涉及创建一个 TABLE 数据类型态哗 插入第一个表(LEFT JOIN 左侧的表)中的所有行 然后使用第二个表中的值更新 TABLE 数据类型 此技术是一个两步的过运肆程 但与标准的 LEFT JOIN 相比 可以节省大量时间 一个很好的规则是尝试各种不同的技术并记录每种技术所需的时间 直到获得用于您的应用程序的执行性能最佳的查询
DECLARE @tblMonths TABLE (sMonth VARCHAR( ))
灵活使用笛卡尔乘积
对于此技巧 我将进行非常详细的介绍 并提倡在某些情况下使用笛卡尔乘积 出于某些原旁闭轿因 笛卡尔乘积 (CROSS JOIN) 遭到了很多谴责 开发人员通常会被警告根本就不要使用它们 在许多情况下 它们消耗的资源太多 从而无法高效使用 但是像 SQL 中的任何工具一样 如果正确使用 它们也会很有价值
其中一段示例代码 值得效仿
笛卡尔乘积则可以返回所有月份的所有客户 笛卡尔乘积基本上是将第一个表与第二个表相乘 生成一个行集合 其中包含第一个表中的行数与第二个表中的行数相乘的结果 因此 笛卡尔乘积会向表 @tblFinal 返回 (所有月份)* (所有客户)= 行 最后的步骤是使用此日期范围内每个客户的月销售额总计更新 @tblFinal 表 以及选择最终的行集
DECLARE @tblMonths TABLE (sMonth VARCHAR( ))
DECLARE @tblCustomers TABLE ( CustomerID CHAR( )
CompanyName VARCHAR( )
ContactName VARCHAR( ))
DECLARE @tblFinal TABLE ( sMonth VARCHAR( )
CustomerID CHAR( )
CompanyName VARCHAR( )
ContactName VARCHAR( )
mSales MONEY)
DECLARE @dtStartDate DATETIME
@dtEndDate DATETIME
@dtDate DATETIME
@i INTEGER
SET @dtEndDate = / /
SET @dtEndDate = DATEADD(DD CAST(CAST((MONTH(@dtEndDate) + ) AS
VARCHAR( )) + / / + CAST(YEAR(@dtEndDate) AS VARCHAR( )) + : : AS DATETIME))
SET @dtStartDate = DATEADD(MM * @dtEndDate)
Get all months into the first table
SET @i =
WHILE (@i < )
BEGIN
SET @dtDate = DATEADD(mm * @i @dtEndDate)
INSERT INTO @tblMonths SELECT CAST(YEAR(@dtDate) AS VARCHAR( )) + +
CASE
WHEN MONTH(@dtDate) <
THEN + CAST(MONTH(@dtDate) AS VARCHAR( ))
ELSE CAST(MONTH(@dtDate) AS VARCHAR( ))
END AS sMonth
SET @i = @i +
END
Get all clients who had sales ring that period into the y table
INSERT INTO @tblCustomers
SELECT DISTINCT
c CustomerID
c CompanyName
c ContactName
FROM Customers c
INNER JOIN Orders o ON c CustomerID = o CustomerID
WHERE o OrderDate BEEEN @dtStartDate AND @dtEndDate
INSERT INTO @tblFinal
SELECT m sMonth
c CustomerID
c CompanyName
c ContactName
FROM @tblMonths m CROSS JOIN @tblCustomers c
UPDATE @tblFinal SET
mSales = mydata mSales
FROM @tblFinal f INNER JOIN
(
SELECT c CustomerID
CAST(YEAR(o OrderDate) AS VARCHAR( )) + +
CASE WHEN MONTH(o OrderDate) <
THEN + CAST(MONTH(o OrderDate) AS VARCHAR( ))
ELSE CAST(MONTH(o OrderDate) AS VARCHAR( ))
END AS sMonth
SUM(od Quantity * od UnitPrice) AS mSales
FROM Customers c
INNER JOIN Orders o ON c CustomerID = o CustomerID
INNER JOIN [Order Details] od ON o OrderID = od OrderID
WHERE o OrderDate BEEEN @dtStartDate AND @dtEndDate
GROUP BY
c CustomerID
CAST(YEAR(o OrderDate) AS VARCHAR( )) + +
CASE WHEN MONTH(o OrderDate) <
THEN + CAST(MONTH(o OrderDate) AS VARCHAR( ))
ELSE CAST(MONTH(o OrderDate) AS VARCHAR( ))
END
) mydata on f CustomerID = mydata CustomerID AND f sMonth =
mydata sMonth
SELECT f sMonth
f CustomerID
f CompanyName
f ContactName
f mSales
FROM @tblFinal f
ORDER BY
f CompanyName
f sMonth
lishixin/Article/program/Java/Javascript/201311/25338