sql 针对多个id或名称的分割和组合

开发中经常遇到把多个id拼接在一起符号隔开传入数据库，那拆分呢就是个大事

SELECT nPushID
    INTO   #temp
    FROM  Table1
    WHERE  CHARINDEX( ',' + CONVERT(VARCHAR(10), nCoulmn) + ',', ',' + '3305,3311,3314' + ',') > 0
SELECT * FROM #temp

这里需要有表，并且是有存在数据与传的字符串相关。列针对的是int 型，所以要转换。

DECLARE 
    @a decimal(18, 2) = 360, --返券的金额
    @b decimal(18, 2) = 5, --券张
    @c VARCHAR(50)= '40,30,10,10,10',
    @cResult    DECIMAL(18,2) = 0,
    @cRemainder    DECIMAL(18,2) = 0, --余额
    @ignore DECIMAL(18,2) = 70, --余额标准，不足标准将余额合并到最后一张分券
    @str varchar(8000) 
    if object_id(N'tempdb..#temp1',N'U') is not null
    begin
        drop table #temp1
        print 'aaa'
    end
    create  table #temp1  (
        id int,
        dmPrice DECIMAL(18,2)
    )
set @str =  'select  dmPrice='+replace(@c,',',''+' union all select ')+'' 

--set @str =  'select  dmPrice='''+replace(@c,',',''''+' union all select ''')+''''  --字符串列

set @str='select id=identity(int,1,1),dmPrice  into #temp from ('+@str+') a ;
 insert into #temp1 select * from #temp;'
exec(@str)    

--可针对临时表做操作                           
INSERT INTO Table_a SELECT '1','1',10,1,dmPrice,GETDATE(),10,10 FROM #temp1

DECLARE
@c VARCHAR(50)= '40,30,10,10,10',
@str VARCHAR(8000)=''
set @str = 'select name='''+replace(@c,',',''''+' union all select ''')+''''

set @str='select id=identity(int,1,1),name into #temp from ('+@str+') a '
exec(@str)

--这种只能用作显示，且无法针对临时表做操作

 这个就不需要真实的表和数据来做分割。网上有很多循环的例子,但效率不如这个

 

针对订单那种订单主从表，从表多个的名称拼接在一起的

--实例1 分割符在前
select isnull ((select '@'+ CommodityName from OrderInfo where OrderInfo.orderid =OrderMain.OrderID for xml path('')),'') from OrderMain
where  orderid ='20120302121548'

--针对分隔符在前，可以用stuff很方便
select isnull (stuff((select '@'+ CommodityName from OrderInfo where OrderInfo.orderid =OrderMain.OrderID for xml path('')),1,1,''),'') from OrderMain
where  orderid ='20120302121548'

--实例2 同表，

select stuff((select ','+CONVERT(VARCHAR(20),A.nID) from Table_a A WHERE A.nId= @nId  FOR xml PATH('')), 1, 1, '') as nID

 

--分隔符在后，对于拼接名称的，个人觉得用substring、left还麻烦点，不如在后台或者前台去处理

 

 

sql 2016 将支持OpenJson

declare @vcLoginIdJson nvarchar(max)
set @vcLoginIdJson = '{"admin":"","hyh":""}'
select [key] as vcLoginId FROM openjson(@vcLoginIdJson)

详细参考地址

https://docs.microsoft.com/zh-cn/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server

转载于:https://www.cnblogs.com/ghelement/p/5630874.html