SQL Server特殊用法笔记

1. MERGE用法：关联两表，有则改，无则加

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(公用表表达式)：优雅清晰的代码

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的写法，更加优雅。但是值得注意的是，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并串联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表为一个树形结构：

目标：将结构打散成二级，结果数据为：节点，父节点，父子深度。

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语句：

---- 创建函数解析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

未完待续。。。。

转载请注明出处：http://www.cnblogs.com/icyj

Top

推荐

收藏

关注

评论

分类: 开发 - SQL

好文要顶 关注我 收藏该文  

icyjiang
关注 - 26
粉丝 - 88

+加关注

2

? 上一篇：mysql笔记一——安装和设置root密码
? 下一篇：RabbitMQ内存爆出

posted @ 2017-04-07 17:36 icyjiang 阅读(369) 评论(0) 编辑 收藏

刷新评论刷新页面返回顶部

注册用户登录后才能发表评论，请 登录 或 注册，访问网站首页。

【推荐】50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
【免费】从零开始学编程，开发者专属实验平台免费实践！

最新IT新闻:
· 乐视问题超预期？四个交易日融创股价下跌超11%
· 比特币带火显卡 涨价四成仍难求
· Pippin Barr带你重温Windows 95年代的PC工作环境
· 欧盟开发机器人“獾”，用于地下挖掘
· 乐视危机加剧 供应商仁宝电脑面临2300万美元坏债
? 更多新闻...

最新知识库文章:

· 小printf的故事：什么是真正的程序员？
· 程序员的工作、学习与绩效
· 软件开发为什么很难
· 唱吧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)

• 开发 - .NET/C#(56)

• 开发 - Android(3)

• 开发 - F#(1)

• 开发 - Java(1)

• 开发 - Linq(4)

• 开发 - MVC/Asp.net(9)

• 开发 - ReportView(3)

• 开发 - SQL(16)

• 开发 - WCF(6)

• 开发 - 云(1)

• 思路(2)

• 网站开发(10)

• 心态(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# 实现ComboBox自动模糊匹配

• mark

• --弹壳

• 5. Re:webbrowser取页面验证码

• 怎么解析验证码？听你说好像很简单哦，等分享一下吗

• --简单幸福

阅读排行榜

• 1. c# 读取excel数据的两种方法(38592)

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

• 3. c#执行并行任务之Parallel与TaskFactory(13843)

• 4. c# DataSource和BindingSource(11293)

• 5. c# 通过.net自带的chart控件绘制饼图pie chart(10234)

评论排行榜

• 1. 【每天进步一点】毒药和老鼠的研究(33)

• 2. C# 不借助第三个变量实现两整数交换(32)

• 3. c# 测试篇之Linq性能测试(13)

• 4. EF code First数据迁移学习笔记(12)

• 5. 自动关闭MessageBox(原创)(9)

推荐排行榜

• 1. 【每天进步一点】毒药和老鼠的研究(26)

• 2. C# 不借助第三个变量实现两整数交换(10)

• 3. 自动关闭MessageBox(原创)(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 16:19:56.88
• SQL Server事务的隔离级别和锁 2017-07-21 16:32:01.93
• MyBatis-sql映射文件 2017-07-21 15:43:32.14
• SQL数据库操作(CURD) 2017-07-13 16:47:20.503
• SQL Server AlwaysOn配置两个节点加共享文件夹仲裁见证 2017-07-13 10:26:59.473
• SQL-结构化查询语言(1) 2017-07-12 16:47:40.067
• SQL执行过程中的性能负载点 2017-07-10 14:52:37.027
• hive集成sentry的sql使用语法 2017-07-04 15:50:39.427
• SQL Server--获取磁盘空间使用情况 2017-06-27 15:39:18.86
• SQL 基础知识梳理（七）- 集合运算 2017-06-27 14:47:21.177

SQL Server事务的隔离级别和锁