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,null

insert into #BBB select 1,10 union select 2,20 union select 3,30 union select 6,60

merge into #AAA as t

using (select * from #BBB where A<30 )as s

on s.A=t.A

when matched then update set t.B=s.B

when not matched by target then insert values(0,s.A,0,0,s.B)

when not matched by source then update set t.B=0

output $action as [Action],

    Inserted.id as InsertId,

    Inserted.B as InsertB,

    Deleted.id as DeletedId,

    Deleted.B as DeletedB;

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

where rn=1

drop table #AAA

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

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

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

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 ST2

drop table #AAA

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 #IdMap

select newid() as newGuid,A,B,id from #OldData

drop table #OldData

drop table #IdMap

drop table #NewData

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, Lvl

FROM cte

ORDER BY cte.flag,cte.lvl desc

drop table #AAA

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.rn

DROP table #AAA

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 ct

FROM(

    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

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

--END

DECLARE @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

;WITH product(id,name)

AS

(

    select 10,N'你' union all

    select 11,N'我' union all

    select 13,N'他' union all

    select 17,N'她' union all

    select 20,N'它'

)

SELECT

*

FROM (SELECT id,LAG(id,1,0)OVER(ORDER BY id) AS 前一行ID,LEAD(id,1,0)OVER(ORDER BY id) AS 后一行ID   FROM Product ) AS t

WHERE Id=10