1. MERGE用法：關聯(lián)兩表，有則改，無則加

SQL語句：

create table #AAA(id int,A int,AA int,AAA int,B int)create table #BBB(A int,B int)insert into #AAA select 1,1,1,1,null union select 2,2,2,2,null union select 3,3,3,3,null union select 4,4,4,4,nullinsert into #BBB select 1,10 union select 2,20 union select 3,30 union select 6,60merge into #AAA as t
using (select * from #BBB where A<30 )as son s.A=t.Awhen matched then update set t.B=s.Bwhen not matched by target then insert values(0,s.A,0,0,s.B)when not matched by source then update set t.B=0output $action as [Action],
    Inserted.id as InsertId,
    Inserted.B as InsertB,
    Deleted.id as DeletedId,
    Deleted.B as DeletedB;

詳細說明和更多用法參見：https://msdn.microsoft.com/zh-cn/library/bb510625.aspx

2. ROW_NUMBER用法：分組取第一行

SQL語句:

create table #AAA(id int,A int,B int,C int,Flag int)insert into #AAA values(1,1,1,1,0),(2,1,2,2,0),(3,2,3,3,1),(4,2,4,4,0)select * from
    (select A,B,rn=ROW_NUMBER() over (partition by A order by C) 
    from #AAA    where Flag=0) t0where rn=1drop table #AAA

擴展用法：

1. 刪除重復數據，思路：按照一定的排序保留第一條，刪除rn>1的數據。

2. row_Number的Over語句中，如果不想做排序操作，可以輸入order by(select null)

詳細說明和更多用法參見：https://msdn.microsoft.com/zh-cn/library/ms186734.aspx

3. READPAST大用

說明：READPAST是一個table hints，實際應用場景可以是多線程處理一批任務，Update/Delete任務時用ReadPast可以跳過行鎖，提高效率。

SQL語句：

DELETE a OUTPUT deleted.* FROM dbo.Test a WITH (UPDLOCK, READPAST)

詳細說明和更多用法參見：https://msdn.microsoft.com/zh-cn/library/ms187373.aspx

4. CTE(公用表表達式)：優(yōu)雅清晰的代碼

SQL語句：

create table #AAA(orderId varchar(20), packId varchar(20), skuId varchar(20), resentSign bit, resent int)create table #BBB(orderId varchar(20), skuId varchar(20), resent int)insert into #AAA values
     ('S01','P01','A',null,null)
    ,('S03','P01','C',1,20)
    ,('S01','P02','A',null,null)
    ,('S01','P01','B',null,null)
    ,('S02','P01','A',null,null)
    ,('S02','P03','B',null,null)insert into #BBB values
    ('S01','A',10)

;with cteTest as(    select t3.*,t2.resent as newResent from 
    (select t1.packId,t1.SkuId,t0.resent from
        (select orderId,skuId,resent from #BBB) t0        left join (select orderId,packId,SkuId from #AAA)t1        on t1.orderId=t0.orderId and t1.SkuId=t0.skuId) t2    left join (select * from #AAA where resentSign is null) t3 
    on t3.packId=t2.packId and t3.SkuId=t2.SkuId
)update cteTest set ResentSign=1, resent=newResent

提示：

1. with前如果有SQL語句，必須以;結尾，否則報錯，因此可以習慣在With前加;的寫法。

2. with加上merge的寫法，更加優(yōu)雅。但是值得注意的是，merge的表對象可以用with過濾查找，但MS官方不推薦這么做，有失敗的風險。

詳細說明和更多用法參見：https://msdn.microsoft.com/zh-cn/library/ms175972.aspx

5. 探究SQL中的null和空字符

SQL語句：

declare @testOne nvarchar(30)set @testOne='   'select @testOne as Content
    ,case when @testOne = ' ' then ' = empty' else '= empty false' end as EmptyTest
    ,case when @testOne != ' ' then '!= empty' else '!= empty false' end as NotEmptyTest
    ,case when @testOne = null then '= Null' else '= Null false' end as NotEmptyTest
    ,case when @testOne != null then '!= Null' else '!= Null false' end as NotNullTest

6. STUFF：查詢group并串聯(lián)String

SQL語句：

create table #AAA(id int, Col1 varchar(10))insert into #AAA values (3,'吃飯'),(3,'運動'),(2,'打球'),(1,'跳舞'),(1,'看電影')Select distinct ST2.id, 
stuff((Select ','+Col1-- as [text()]--無列名
    From #AAA ST1    Where ST1.id = ST2.id    For XML PATH ('')
    ),1,1,'') Col1s From #AAA ST2drop table #AAA

詳細說明和更多用法參見：https://msdn.microsoft.com/zh-cn/library/ms190922.aspx

7. OUTPUT用法：增刪改的同時OUTPUT數據

SQL語句：

create table #OldData(id int, A varchar(30), B varchar(30))create table #IdMap(OldId int,[NewId] uniqueidentifier)create table #NewData(id uniqueidentifier, A varchar(30), B varchar(30), oldId int)insert into #OldData values (1,'A','B'),(2,'Ads','Bwe'),(3,'frA','erB'),(4,'erA','Bty')--寫入新數據同時寫到Id映射表insert into #NewData
output inserted.OldId,inserted.id AS [NewId] into #IdMapselect newid() as newGuid,A,B,id from #OldDatadrop table #OldDatadrop table #IdMapdrop table #NewData

詳細說明和更多用法參見：https://msdn.microsoft.com/zh-cn/library/ms177564.aspx

8. CTE遞歸一

如上如：A表為一個樹形結構：

目標：將結構打散成二級，結果數據為：節(jié)點，父節(jié)點，父子深度。

SQL語句：

create table #AAA(id int, pid int,v int)insert into #AAA values (0,null,0),(1,0,1),(2,0,2),(3,0,3),(4,1,4),(5,1,5),(6,2,6),(7,4,7),(8,2,8),(9,6,9)SELECT * FROM #AAA

;with cte as(    select Id,Pid,0 as lvl,Id as flag from #AAA    union all
    select d.Id,d.Pid,lvl+1,c.flag from cte c inner join #AAA d    on d.Id = c.Pid      where c.lvl<10--這里加2表示只取2次遞歸的結果。)select flag AS subId,Id AS dadId, LvlFROM cteORDER BY cte.flag,cte.lvl descdrop table #AAA

詳細說明和更多用法參見：https://msdn.microsoft.com/zh-cn/library/ms175972.aspx

9. CTE遞歸二

目標：將數據分組并向上累加。V(0,0)=1，V(0,1)=3，V(0,2)=8處理成V(0,0)=1，V(0,1)=3+1=4，V(0,2)=8+3+1=12

SQL語句：

create table #AAA(gpid int, rn int, v int)insert into #AAA values (0,0,1),(0,1,3),(0,2,8),(1,0,5),(1,1,1),(2,0,1),(2,1,10),(2,2,3),(2,3,-1),(3,0,6)

;with cte as(    select gpid,rn,v from #AAA WHERE rn=0

    union all
    select d.gpid,d.rn,d.v+c.v AS v FROM cte c inner join #AAA d    on d.gpid = c.gpid AND d.rn =c.rn+1)SELECT * FROM cte ORDER BY gpid,cte.rnDROP table #AAA

詳細說明和更多用法參見：https://msdn.microsoft.com/zh-cn/library/ms175972.aspx

10. CTE遞歸三

SQL語句：

DECLARE @tmp TABLE(id INT, value VARCHAR(30))INSERT INTO @tmp VALUES (1,'a'), (2,'a'), (3,'a'), (4,'b'), (5,'b'), (6,'a'), (7,'a'), (8,'c')

;WITH cte AS(    SELECT *,[key]=id, ct = 1 FROM @tmp
    UNION ALL
    SELECT d.id, d.value,c.[key], c.ct+1 AS ct 
    FROM cte c INNER JOIN @tmp d ON d.id=c.id+1 AND d.value = c.value
)SELECT rn = ROW_NUMBER() OVER (ORDER BY b1.[key]) ,b1.value,COUNT(1) AS ctFROM(    SELECT *,rn=ROW_NUMBER() OVER(PARTITION BY id ORDER BY cte.[key]) 
    FROM cte 
) b1 WHERE b1.rn=1 GROUP BY b1.[key],b1.value

11.解析xml子數據并join到父數據

目標：將父數據中的xml子數據解讀并對每條子數據生成一條包含父數據信息的數據行

SQL語句：

---- 創(chuàng)建函數解析xml成table--alter FUNCTION [dbo].[F_GetDetails]--(--    @detailxml nvarchar(4000)           --)--RETURNS @t TABLE(id int, amount DECIMAL(12,4))--AS--BEGIN--        --解析xml--    declare @xml xml --    set @xml=cast(@detailxml as xml)
    --    INSERT INTO @t--    select T.c.value('@id','int') as Id, --        T.c.value('@amount','decimal(12,4)') as Amount--    from @xml.nodes('As/A') as T(c)--    RETURN;--ENDDECLARE @tmp TABLE(id INT,name NVARCHAR(30),xmlDetail NVARCHAR(1000))INSERT INTO @tmp VALUES  
 (1,    'A',N'<?xml version="1.0" encoding="utf-16"?>
<As>
  <A id="1" amount="1.3900" />
  <A id="2" amount="19.0000" />
  <A id="3" amount="2.2200" />
</As>')
,(2,    'B',N'<?xml version="1.0" encoding="utf-16"?>
<As>
  <A id="4" amount="9.3600" />
  <A id="5" amount="10.5000" />
  <A id="6" amount="2.1500" />
</As>')SELECT * FROM @tmp a 
CROSS apply dbo.[F_GetDetails](a.xmlDetail) b

詳細說明和更多用法參見： https://msdn.microsoft.com/zh-cn/library/ms177634.aspx

未完待續(xù)。。。。

轉載請注明出處：http://www.cnblogs.com/icyj

Top

推薦

收藏

關注

評論

分類: 開發(fā) - SQL

好文要頂 關注我 收藏該文  

icyjiang
關注 - 26
粉絲 - 88

+加關注

2

? 上一篇：mysql筆記一——安裝和設置root密碼
? 下一篇：RabbitMQ內存爆出

posted @ 2017-04-07 17:36 icyjiang 閱讀(369) 評論(0) 編輯 收藏

刷新評論刷新頁面返回頂部

注冊用戶登錄后才能發(fā)表評論，請 登錄 或 注冊，訪問網站首頁。

【推薦】50萬行VC++源碼: 大型組態(tài)工控、電力仿真CAD與GIS源碼庫
【免費】從零開始學編程，開發(fā)者專屬實驗平臺免費實踐！

最新IT新聞:
· 樂視問題超預期？四個交易日融創(chuàng)股價下跌超11%
· 比特幣帶火顯卡 漲價四成仍難求
· Pippin Barr帶你重溫Windows 95年代的PC工作環(huán)境
· 歐盟開發(fā)機器人“獾”，用于地下挖掘
· 樂視危機加劇 供應商仁寶電腦面臨2300萬美元壞債
? 更多新聞...

最新知識庫文章:

· 小printf的故事：什么是真正的程序員？
· 程序員的工作、學習與績效
· 軟件開發(fā)為什么很難
· 唱吧DevOps的落地，微服務CI/CD的范本技術解讀
· 程序員，如何從平庸走向理想？

? 更多知識庫文章...

歷史上的今天:
2015-04-07 MVC的JavaScriptResult使用

歡迎光臨我的網站： 
www.viyip.com 
www.ycaiy.com

昵稱：icyjiang
園齡：5年8個月
粉絲：88
關注：26

+加關注

隨筆分類

• javascript/JQuery(4)

• MySQL(1)

• 不積跬步無以至千里(17)

• 電腦硬件(2)

• 解決問題(3)

• 開發(fā) - .NET/C#(56)

• 開發(fā) - Android(3)

• 開發(fā) - F#(1)

• 開發(fā) - Java(1)

• 開發(fā) - Linq(4)

• 開發(fā) - MVC/Asp.net(9)

• 開發(fā) - ReportView(3)

• 開發(fā) - SQL(16)

• 開發(fā) - WCF(6)

• 開發(fā) - 云(1)

• 思路(2)

• 網站開發(fā)(10)

• 心態(tài)(5)

• 雜記(8)

• 資源(3)

友情鏈接

• [淘寶]韓都衣舍

• [天貓]3-10歲兒童玩具DIY小屋

• [天貓]芭比-遙控飛機-木質玩具

• [天貓]男童服裝專賣

• [天貓]女童服裝專賣

積分與排名

• 積分 - 144736

• 排名 - 1591

最新評論

• 1. Re:c# 通過.net自帶的chart控件繪制餅圖pie chart

• 很詳細，mark一下。試試Spread Studio也不錯

• --zenmshuo

• 2. Re:15.04.14-登錄之后刷新AntiForgeryToken

• 不錯 學習了

• --寒山石

• 3. Re:dotfuscator使用方法

• 其實混淆并不能保護DLL，說穿了只是增加了閱讀的難度而已，其實是防止不了別人調用你的DLL或直接反射的下面有個新思路可以保護DLL...

• --qwsf01115

• 4. Re:c# 實現(xiàn)ComboBox自動模糊匹配

• mark

• --彈殼

• 5. Re:webbrowser取頁面驗證碼

• 怎么解析驗證碼？聽你說好像很簡單哦，等分享一下嗎

• --簡單幸福

閱讀排行榜

• 1. c# 讀取excel數據的兩種方法(38592)

• 2. IIS 7上部署PHP(22030)

• 3. c#執(zhí)行并行任務之Parallel與TaskFactory(13843)

• 4. c# DataSource和BindingSource(11293)

• 5. c# 通過.net自帶的chart控件繪制餅圖pie chart(10234)

評論排行榜

• 1. 【每天進步一點】毒藥和老鼠的研究(33)

• 2. C# 不借助第三個變量實現(xiàn)兩整數交換(32)

• 3. c# 測試篇之Linq性能測試(13)

• 4. EF code First數據遷移學習筆記(12)

• 5. 自動關閉MessageBox(原創(chuàng))(9)

推薦排行榜

• 1. 【每天進步一點】毒藥和老鼠的研究(26)

• 2. C# 不借助第三個變量實現(xiàn)兩整數交換(10)

• 3. 自動關閉MessageBox(原創(chuàng))(8)

• 4. T-SQL_常用內置函數和操作(6)

• 5. c# 連等算式都在做什么(6)

Copyright ?2017 icyjiang

分享

http://www.cnblogs.com/icyJ/p/SQL_Statement.html

延伸閱讀

• LINQ TO SQL和Entity Framework 的關系 你了解多少？ 2017-07-26
• SQL Server事務的隔離級別和鎖 2017-07-21
• MyBatis-sql映射文件 2017-07-21
• SQL數據庫操作(CURD) 2017-07-13
• SQL Server AlwaysOn配置兩個節(jié)點加共享文件夾仲裁見證 2017-07-13
• SQL-結構化查詢語言(1) 2017-07-12
• SQL執(zhí)行過程中的性能負載點 2017-07-10
• hive集成sentry的sql使用語法 2017-07-04
• SQL Server--獲取磁盤空間使用情況 2017-06-27
• SQL 基礎知識梳理（七）- 集合運算 2017-06-27

SQL Server事務的隔離級別和鎖