進階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