SQL窗体函數一例

需求:

MSSQL,列出服務實例中全部數據庫的例如以下信息: 數據庫ID、數據庫名、創建日期、數據文件類型、數據文件大小、數據庫總大小、文件所在路徑。

寫法(後面的百分比為所花時間占比):

-- 连接子查询  (47%)
WITH cte_TotalSize AS
(
	SELECT database_id
	      ,CAST(SUM(size) AS FLOAT)/128 AS [TotalSize(MB)]
	FROM sys.master_files
	GROUP BY database_id
)
SELECT a.database_id AS [DB_ID]
      ,b.name AS [DB_Name]
      ,b.create_date
      ,CASE a.type WHEN 1 THEN 'Log' ELSE 'Data' END AS File_Type
      ,CAST(a.size AS FLOAT)/128 AS [Size(MB)]  -- Size以页(8 KB)为单位
      ,c.[TotalSize(MB)]
      ,a.physical_Name AS File_Path
FROM sys.master_files a
INNER JOIN sys.databases b ON a.database_id = b.database_id
INNER JOIN cte_TotalSize c ON a.database_id = c.database_id

-- 标量子查询  (34%)
SELECT a.database_id AS [DB_ID]
      ,b.name AS [DB_Name]
      ,b.create_date
      ,CASE a.type WHEN 1 THEN 'Log' ELSE 'Data' END AS File_Type
      ,CAST(a.size AS FLOAT)/128 AS [Size(MB)]  -- Size以页(8 KB)为单位
      ,(SELECT CAST(SUM(size) AS FLOAT)/128 FROM sys.master_files WHERE database_id = a.database_id) AS [TotalSize(MB)]
      ,a.physical_Name AS File_Path
FROM sys.master_files a
INNER JOIN sys.databases b ON a.database_id = b.database_id

-- 窗体函数  (19%)
SELECT a.database_id AS [DB_ID]
      ,b.name AS [DB_Name]
      ,b.create_date
      ,CASE a.type WHEN 1 THEN 'Log' ELSE 'Data' END AS File_Type
      ,CAST(a.size AS FLOAT)/128 AS [Size(MB)]  -- Size以页(8 KB)为单位
      ,CAST(SUM(size) OVER(PARTITION BY a.database_id) AS FLOAT)/128 AS [TotalSize(MB)]
      ,a.physical_Name AS File_Path
FROM sys.master_files a
INNER JOIN sys.databases b ON a.database_id = b.database_id

結果:

均得出正確結果的上面三種方法,代碼越來越少,性能卻越來越好。。

当中第三種是使用了窗体函數,相關文檔:http://msdn.microsoft.com/zh-cn/library/ms189461.aspx

时间: 10-08

SQL窗体函數一例的相关文章

SQL窗口函數一例

需求: MSSQL,列出服務實例中所有數據庫的如下信息: 數據庫ID.數據庫名.創建日期.數據文件類型.數據文件大小.數據庫總大小.文件所在路徑. 寫法(後面的百分比為所花時間占比): -- 连接子查询 (47%) WITH cte_TotalSize AS ( SELECT database_id ,CAST(SUM(size) AS FLOAT)/128 AS [TotalSize(MB)] FROM sys.master_files GROUP BY database_id ) SELEC

學習日記:函數和對象

2016-2-21 1. Living without an aim is like sailing without a compass. 生活沒有目標,猶如航海沒有羅盤. 2. 無論是現實世界還是計算機世界,可讀性都是相當重要的,因為這涉及到人們的意識或者是認識效率,一般文字比數字的可理解性和可讀性要好,圖片的可讀性最強. a) 一般數學是比較抽象的,因為其中充滿著各種阿拉伯數字和已經不能再簡化的希臘字符. b) 數學家的得意之作就是覺得自己是在世界科學界的最巔峰. c) 我們能用數學工具處理

對比:莫比烏斯反演與歐拉函數

最近題讓我非常困惑,貌似我現在已經完全分不清楚哪些題用莫比烏斯反演,哪些用歐拉函數. 下面簡單總結一下,莫比烏斯反演處理的是 求segma(gcd(x,y)) 1<=x<=n,1<=y<=m (見<能量項鍊>) gcd(x,y) = k   1<=x<=n 1<=y<=m  求x,y對數 (見<bzoj 2301  problem b>) 莫比烏斯反演原來是解決以上問題2的,大體思路是 設F(a,b,k)表示1<=x<=a

ORACLE字符拆分函數,返回結果集

ORACLE不能像MSSQL那樣支持直接返回表類型,所以要先創建一種自定義類型.這裏用到的是嵌套表(Nested Table). -- Nested Table CREATE OR REPLACE TYPE split_str IS TABLE OF VARCHAR(100); / -- Function CREATE OR REPLACE FUNCTION fn_Split ( p_Str VARCHAR2, p_Delimiter VARCHAR2 ) RETURN split_str PI

bzoj 2705: [SDOI2012]Longge的问题 歐拉函數

2705: [SDOI2012]Longge的问题 Time Limit: 3 Sec  Memory Limit: 128 MBSubmit: 1035  Solved: 669[Submit][Status] Description Longge的数学成绩非常好,并且他非常乐于挑战高难度的数学问题.现在问题来了:给定一个整数N,你需要求出∑gcd(i, N)(1<=i <=N). Input 一个整数,为N. Output 一个整数,为所求的答案. Sample Input 6 Sampl

bzoj 2818: Gcd 歐拉函數

2818: Gcd Time Limit: 10 Sec  Memory Limit: 256 MBSubmit: 1633  Solved: 724[Submit][Status] Description 给定整数N,求1<=x,y<=N且Gcd(x,y)为素数的数对(x,y)有多少对. Input 一个整数N Output 如题 Sample Input 4 Sample Output 4 HINT hint 对于样例(2,2),(2,4),(3,3),(4,2) 1<=N<=

Oracle数据库合并行记录,WMSYS.WM_CONCAT 函數的用法

Sql代码 select t.rank, t.Name from t_menu_item t; 10 CLARK    10 KING    10 MILLER    20 ADAMS    20 FORD    20 JONES    20 SCOTT    20 SMITH    30 ALLEN    30 BLAKE    30 JAMES    30 MARTIN    30 TURNER    30 WARD -------------------------------- 我们通过

二分查找函數

找到,返回下標: int binary_search(const int* a, int low_position, int high_position, const int key) { high_position--; while(low_position < high_position) { int mid_position = (low_position+high_position) >> 1; if(a[mid_position] == key) return mid_posi

swap函數 进阶探讨与实现

相信以下這個C程序非常多人都見過啦.當時自己看 美少女战士谭浩强 写的那本书上的解释.反正我当时是没看太懂详细是什么意思.谱架啊~~~ #include <stdio.h> void swap(int x, int y) { int temp = x; x = y; y = temp; } int main() { int n, m; while(~scanf("%d %d", &n, &m)) { swap(n, m); printf("%d &