//排序函數：ROW_NUMBER()、RANK() 與 DENSE_RANK()
遇到相同的價格時會依其他的依據來決定排名, 因此名次均不會相同 1 2 3 4 5 6 7
SELECT 書籍編號, 書籍名稱, 價格, 出版公司,ROW_NUMBER() OVER(ORDER BY 價格) AS 價格排名 FROM 書籍
RANK() 函數遇到相同的數值會給相同的排名, 其後的排名則會跳過 1 2 2 2 4 5 5
SELECT 書籍編號, 書籍名稱, 價格, 出版公司,RANK() OVER(ORDER BY 價格) AS 價格排名 FROM 書籍
若不想如上例自動跳過後面排名, 想呈現 1 2 2 2 3 4 4
SELECT 書籍編號, 書籍名稱, 價格, 出版公司,DENSE_RANK() OVER(ORDER BY 價格) AS 價格排名 FROM 書籍
------------------------------------------------------------------------
//ROW_NUMBER
WITH result AS (SELECT *, ROW_NUMBER() OVER (ORDER BY seq_no) AS rownum FROM company)
SELECT * FROM result WHERE rownum > 0 AND rownum < 6
------------------------------------------------------------------------
//PIVOT
SELECT VendorID, [164] AS EmpNo1 , [198] AS EmpNo2, [223] AS EmpNo3
FROM (SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p PIVOT (COUNT(PurchaseOrderID)
FOR EmployeeID IN ([164], [198], [223])) AS pvt
ORDER BY VendorID
------------------------------------------------------------------------
//中文的排序
筆劃: ORDER BY [FieldName] COLLATE Chinese_PRC_Stroke_ci_as
注音: ORDER BY [FieldName] COLLATE Chinese_Taiwan_bopomofo_CI_AS
------------------------------------------------------------------------
//自訂欄位序號
select identity(int,1,1) as mid,* into #temptable2 from cost_control
select * from #temptable2
------------------------------------------------------------------------
//存入' 及 使用 = null(原為is null)
set quoted_identifier off ;
insert into company(short_name) values("'測試") ;
set quoted_identifier off ;
select * from company where short_name = "'測試" ;
set ansi_nulls off ;
select * from company where [name] = null ;
------------------------------------------------------------------------
//iif
SELECT sum(iif(會員資料.性別 = '男', 1,0)) as 男生 ,
sum(iif(會員資料.性別 = '女', 1,0)) as 女生,
count(*) as 合計
FROM 會員資料
------------------------------------------------------------------------
BULK INSERT TABLE1
FROM 'C:\DataProc\Output\RESD.txt'
WITH
(
BATCHSIZE = 1000, --每一千筆commit一次
FIELDTERMINATOR = '\t', --欄位的間隔符號
ROWTERMINATOR = '\n', --列的間隔符號
TABLOCK
)
------------------------------------------------------------------------
假設我每星期完整備份,一個月備份集會有W1,W2,W3,W4,W5
每天會有差異備份,所以會有D1,D2,D3............D31.
每小時交易備份,所以會有H0101,H0102...........H3124.
我要還原至28號,下午三時,W4+D28+H2801,H2802,H2803..........H2815.
------------------------------------------------------------------------
檢核sql server 資料庫狀態的方式
select * from sys.dm_exec_connections;
select * from sys.dm_tran_locks;
select * from sys.dm_exec_sessions;
select * from sys.dm_exec_requests;
------------------------------------------------------------------------
//刪除資料庫中重複的資料 way1
DELETE FROM [dbo].[MyTable] WHERE 主索引鍵 NOT IN
(SELECT MAX(主索引鍵) From [dbo].[MyTable] GROUP BY 欄位1, 欄位2, 欄位3)
1.一定要有個數值類型的主索引鍵，例如：int
在上述語法中就是名為 ID 這個欄位
2.決定何謂重複資料
若判斷重複資料的依據是看其中 3 個欄位，那就將這些欄位全部列入 GROUP BY 子句中
------------------------------------------------------------------------
刪除資料庫中重複的資料 way2 (SQL2005以上版本)
WITH TmpOrderdTable
AS
(
SELECT
GroupID = ROW_NUMBER() OVER (PARTITION BY 欄位1, 欄位2, 欄位3 ORDER BY 主索引鍵)
FROM
[dbo].[MyTable]
)
DELETE FROM TmpOrderdTable WHERE GroupID > 1
------------------------------------------------------------------------
取得重複資料的第一筆
WITH SearchResult
AS
(
SELECT GroupID = ROW_NUMBER() OVER(PARTITION BY Address ORDER BY SerialNo),*
FROM YearlyCeremony
)
------------------------------------------------------------------------
select * FROM SearchResult WHERE GroupID = 1
//取得 SQL Server 資料庫正在執行的 T-SQL 指令與詳細資訊
SELECT r.scheduler_id as 排程器識別碼,
status as 要求的狀態,
r.session_id as SPID,
r.blocking_session_id as BlkBy,
substring(
ltrim(q.text),
r.statement_start_offset/2+1,
(CASE
WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2)
AS [正在執行的 T-SQL 命令],
r.cpu_time as [CPU Time(ms)],
r.start_time as [開始時間],
r.total_elapsed_time as [執行總時間],
r.reads as [讀取數],
r.writes as [寫入數],
r.logical_reads as [邏輯讀取數],
-- q.text, /* 完整的 T-SQL 指令碼 */
d.name as [資料庫名稱]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time desc
------------------------------------------------------------------------
--累加欄位
select 日期, 生管排程數量 , 百分比 ,成品數量 = case
when (select count(*) from TEST T2 where T2.日期 < T1.日期) = 0 then 0
else (select SUM(成品數量) from TEST T2 where T2.日期 < T1.日期)
end + 成品數量
from TEST T1
---------------------------------------
日期(datetime) 生管排程數量(int) 百分比(decimal(4,2)) 成品數量(int)
2011-01-05 0 0.00 7669
2011-01-04 0 0.00 3956
2011-01-01 0 0.00 2837
------------------------------------------------------------------------
遇到相同的價格時會依其他的依據來決定排名, 因此名次均不會相同 1 2 3 4 5 6 7
SELECT 書籍編號, 書籍名稱, 價格, 出版公司,ROW_NUMBER() OVER(ORDER BY 價格) AS 價格排名 FROM 書籍
RANK() 函數遇到相同的數值會給相同的排名, 其後的排名則會跳過 1 2 2 2 4 5 5
SELECT 書籍編號, 書籍名稱, 價格, 出版公司,RANK() OVER(ORDER BY 價格) AS 價格排名 FROM 書籍
若不想如上例自動跳過後面排名, 想呈現 1 2 2 2 3 4 4
SELECT 書籍編號, 書籍名稱, 價格, 出版公司,DENSE_RANK() OVER(ORDER BY 價格) AS 價格排名 FROM 書籍
------------------------------------------------------------------------
//ROW_NUMBER
WITH result AS (SELECT *, ROW_NUMBER() OVER (ORDER BY seq_no) AS rownum FROM company)
SELECT * FROM result WHERE rownum > 0 AND rownum < 6
------------------------------------------------------------------------
//PIVOT
SELECT VendorID, [164] AS EmpNo1 , [198] AS EmpNo2, [223] AS EmpNo3
FROM (SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p PIVOT (COUNT(PurchaseOrderID)
FOR EmployeeID IN ([164], [198], [223])) AS pvt
ORDER BY VendorID
------------------------------------------------------------------------
//中文的排序
筆劃: ORDER BY [FieldName] COLLATE Chinese_PRC_Stroke_ci_as
注音: ORDER BY [FieldName] COLLATE Chinese_Taiwan_bopomofo_CI_AS
------------------------------------------------------------------------
//自訂欄位序號
select identity(int,1,1) as mid,* into #temptable2 from cost_control
select * from #temptable2
------------------------------------------------------------------------
//存入' 及 使用 = null(原為is null)
set quoted_identifier off ;
insert into company(short_name) values("'測試") ;
set quoted_identifier off ;
select * from company where short_name = "'測試" ;
set ansi_nulls off ;
select * from company where [name] = null ;
------------------------------------------------------------------------
//iif
SELECT sum(iif(會員資料.性別 = '男', 1,0)) as 男生 ,
sum(iif(會員資料.性別 = '女', 1,0)) as 女生,
count(*) as 合計
FROM 會員資料
------------------------------------------------------------------------
BULK INSERT TABLE1
FROM 'C:\DataProc\Output\RESD.txt'
WITH
(
BATCHSIZE = 1000, --每一千筆commit一次
FIELDTERMINATOR = '\t', --欄位的間隔符號
ROWTERMINATOR = '\n', --列的間隔符號
TABLOCK
)
------------------------------------------------------------------------
假設我每星期完整備份,一個月備份集會有W1,W2,W3,W4,W5
每天會有差異備份,所以會有D1,D2,D3............D31.
每小時交易備份,所以會有H0101,H0102...........H3124.
我要還原至28號,下午三時,W4+D28+H2801,H2802,H2803..........H2815.
------------------------------------------------------------------------
檢核sql server 資料庫狀態的方式
select * from sys.dm_exec_connections;
select * from sys.dm_tran_locks;
select * from sys.dm_exec_sessions;
select * from sys.dm_exec_requests;
------------------------------------------------------------------------
//刪除資料庫中重複的資料 way1
DELETE FROM [dbo].[MyTable] WHERE 主索引鍵 NOT IN
(SELECT MAX(主索引鍵) From [dbo].[MyTable] GROUP BY 欄位1, 欄位2, 欄位3)
1.一定要有個數值類型的主索引鍵，例如：int
在上述語法中就是名為 ID 這個欄位
2.決定何謂重複資料
若判斷重複資料的依據是看其中 3 個欄位，那就將這些欄位全部列入 GROUP BY 子句中
------------------------------------------------------------------------
刪除資料庫中重複的資料 way2 (SQL2005以上版本)
WITH TmpOrderdTable
AS
(
SELECT
GroupID = ROW_NUMBER() OVER (PARTITION BY 欄位1, 欄位2, 欄位3 ORDER BY 主索引鍵)
FROM
[dbo].[MyTable]
)
DELETE FROM TmpOrderdTable WHERE GroupID > 1
------------------------------------------------------------------------
取得重複資料的第一筆
WITH SearchResult
AS
(
SELECT GroupID = ROW_NUMBER() OVER(PARTITION BY Address ORDER BY SerialNo),*
FROM YearlyCeremony
)
------------------------------------------------------------------------
select * FROM SearchResult WHERE GroupID = 1
//取得 SQL Server 資料庫正在執行的 T-SQL 指令與詳細資訊
SELECT r.scheduler_id as 排程器識別碼,
status as 要求的狀態,
r.session_id as SPID,
r.blocking_session_id as BlkBy,
substring(
ltrim(q.text),
r.statement_start_offset/2+1,
(CASE
WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2)
AS [正在執行的 T-SQL 命令],
r.cpu_time as [CPU Time(ms)],
r.start_time as [開始時間],
r.total_elapsed_time as [執行總時間],
r.reads as [讀取數],
r.writes as [寫入數],
r.logical_reads as [邏輯讀取數],
-- q.text, /* 完整的 T-SQL 指令碼 */
d.name as [資料庫名稱]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time desc
------------------------------------------------------------------------
--累加欄位
select 日期, 生管排程數量 , 百分比 ,成品數量 = case
when (select count(*) from TEST T2 where T2.日期 < T1.日期) = 0 then 0
else (select SUM(成品數量) from TEST T2 where T2.日期 < T1.日期)
end + 成品數量
from TEST T1
---------------------------------------
日期(datetime) 生管排程數量(int) 百分比(decimal(4,2)) 成品數量(int)
2011-01-05 0 0.00 7669
2011-01-04 0 0.00 3956
2011-01-01 0 0.00 2837
------------------------------------------------------------------------