34,838
社区成员




declare
@CustomerPromotionVGUID VARCHAR(max),
@Promotion varchar(max),
@PolicyCode varchar(max)
set @CustomerPromotionVGUID='93E47105-4DAB-4665-ADBA-F964C98BCAE4,5F40A629-BEAD-47BF-84E4-DB6F984F39DA'
set @Promotion='P201601210003,P201601220001:P201601210004'
set @PolicyCode='R201601210009,R201601220001:R201601210028,R201601210029'
/*
select @CustomerPromotionVGUID union all
select @Promotion union all
select @PolicyCode
*/
DECLARE @a TABLE (id int, s varchar(max))
DECLARE @b TABLE (id int, s varchar(max))
DECLARE @c TABLE (id int, s varchar(max))
-- @CustomerPromotionVGUID
;WITH a1 AS (
SELECT 0 pos, 0 i
UNION ALL
SELECT number pos,
ROW_NUMBER() OVER(ORDER BY number) i
FROM master..spt_values
WHERE type = 'p'
AND SUBSTRING(@CustomerPromotionVGUID+',',number,1) = ','
)
,a2 AS (
SELECT t1.i,
SUBSTRING(@CustomerPromotionVGUID, t1.pos+1, t2.pos-t1.pos-1) s
FROM a1 t1
JOIN a1 t2
ON t1.i + 1 = t2.i
)
INSERT INTO @a
SELECT * FROM a2
-- @Promotion
;WITH b1 AS (
SELECT 0 pos, 0 i
UNION ALL
SELECT number pos,
ROW_NUMBER() OVER(ORDER BY number) i
FROM master..spt_values
WHERE type = 'p'
AND SUBSTRING(@Promotion+',',number,1) = ','
)
,b2 AS (
SELECT t1.i,
SUBSTRING(@Promotion, t1.pos+1, t2.pos-t1.pos-1) s
FROM b1 t1
JOIN b1 t2
ON t1.i + 1 = t2.i
)
INSERT INTO @b
SELECT * FROM b2
;WITH b3 AS (
SELECT *
FROM @b b
WHERE CHARINDEX(':',s) <> 0
)
,b4 AS (
SELECT *, 0 pos, 0 i
FROM b3
UNION ALL
SELECT b3.*,
n.number pos,
ROW_NUMBER() OVER(ORDER BY n.number) i
FROM master..spt_values n,
b3
WHERE n.type = 'p'
AND SUBSTRING(b3.s+':',n.number,1) = ':'
)
,b5 AS (
SELECT t1.id,
SUBSTRING(t1.s, t1.pos+1, t2.pos-t1.pos-1) s
FROM b4 t1
JOIN b4 t2
ON t1.i + 1 = t2.i
)
INSERT INTO @b
SELECT * FROM b5
DELETE b
FROM @b b
WHERE CHARINDEX(':',s) <> 0
-- @PolicyCode
/* 这部分照搬 @Promotion,你自己写 */
-- 结果
SELECT a.s CustomerPromotionVGUID,
b.s Promotion --,
-- c.s PolicyCode
FROM @a a
JOIN @b b ON a.id = b.id
--JOIN @c c ON a.id = c.id
CustomerPromotionVGUID Promotion
------------------------------------ -------------
93E47105-4DAB-4665-ADBA-F964C98BCAE4 P201601210003
5F40A629-BEAD-47BF-84E4-DB6F984F39DA P201601220001
5F40A629-BEAD-47BF-84E4-DB6F984F39DA P201601210004
declare
@CustomerPromotionVGUID VARCHAR(max),
@Promotion varchar(max),
@PolicyCode varchar(max)
set @CustomerPromotionVGUID='93E47105-4DAB-4665-ADBA-F964C98BCAE4,5F40A629-BEAD-47BF-84E4-DB6F984F39DA'
set @Promotion='P201601210003,P201601220001:P201601210004'
set @PolicyCode='R201601210009,R201601220001:R201601210028,R201601210029'
select @CustomerPromotionVGUID union all
select @Promotion union all
select @PolicyCode
想得到的结果
select '93E47105-4DAB-4665-ADBA-F964C98BCAE4','P201601210003','R201601210009' union all
select '5F40A629-BEAD-47BF-84E4-DB6F984F39DA','P201601220001','R201601220001'union all
select '5F40A629-BEAD-47BF-84E4-DB6F984F39DA','P201601210001','R201601210028' union all
select '5F40A629-BEAD-47BF-84E4-DB6F984F39DA','P201601220004','R201601210029'
--已逗号与分号 拆分