SQL窗口函數一例，SQL窗口函數

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
)
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

Insert into Table2(field1,field2,...) select value1,value2,... from Table1
再select语句里面就可以sum了

Insert into 系(系名,系主任,系学生总人数) select (@系名,@系主任,sum(专业学生人数)form 系-专业 a where a.系名 = @系名)

有问题hi我
 

自己定义函数

CREATE function ABC（@tablename varchar(100))
returns varchar（2000）
as

select name from tbl_Person
循环 将 name 拼成字符串
returns 字符串

循环就不写了 自己写个游标