SQL視窗函數一例,SQL視窗函數
需求:
MSSQL,列出服務實例中所有數據庫的如下資訊: 數據庫ID、數據庫名、創建日期、數據檔案類型、數據檔案大小、數據庫總大小、檔案所在路徑。
寫法(後面的百分比為所花時間佔比):
-- 串連子查詢 (47%)WITH cte_TotalSize AS(SELECT database_id ,CAST(SUM(size) AS FLOAT)/128 AS [TotalSize(MB)]FROM sys.master_filesGROUP 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_PathFROM sys.master_files aINNER JOIN sys.databases b ON a.database_id = b.database_idINNER 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 aINNER 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 aINNER JOIN sys.databases b ON a.database_id = b.database_id
結果:
均得出正確結果的上面三種方法,代碼越來越少,效能卻越來越好。。
其中第三種是使用了視窗函數,相關文檔:http://msdn.microsoft.com/zh-cn/library/ms189461.aspx
SQL中,怎在已知表中插入一列,並且新插入的這列的值是由函數得的列?
Insert into Table2(field1,field2,...) select value1,value2,... from Table1
再select語句裡面就可以sum了
Insert into 系(系名,系主任,系學生總人數) select (@系名,@系主任,sum(專業學生人數)form 系-專業 a where a.系名 = @系名)
有問題hi我
SQL 函數 將一列拼接字串
自己定義函數
CREATE function ABC(@tablename varchar(100))
returns varchar(2000)
as
select name from tbl_Person
迴圈 將 name 拼成字串
returns 字串
迴圈就不寫了 自己寫個遊標