拆分问题

Pact_Alice 2016-01-26 07:51:03
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
--以逗号为主,CustomerPromotionVGUID,PromotionVGUID,PolicyCode是等级关系
--CustomerPromotionVGUID '93E47105-4DAB-4665-ADBA-F964C98BCAE4','5F40A629-BEAD-47BF-84E4-DB6F984F39DA'

--CustomerPromotionVGUID='93E47105-4DAB-4665-ADBA-F964C98BCAE4'下Promotion有P201601210003 PolicyCode 有R201601210009

--CustomerPromotionVGUID='5F40A629-BEAD-47BF-84E4-DB6F984F39DA'下Promotion有P201601220001:P201601210004
--P201601220001下PolicyCode 有R201601220001,R201601210028
--P201601210004下PolicyCode 有R201601210029
--最后拆分的结果形式就是

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'

--以下是我拆的,单没有完成
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'

-------------------------------------------------------------------------------------------
;WITH cte1 AS
(
SELECT @CustomerPromotionVGUID+',' as CustomerPromotionVGUID,@Promotion+','as PromotionVGUID,@PolicyCode+','as PolicyCode
), cte as(
SELECT
convert(varchar(1024),left(CustomerPromotionVGUID,CHARINDEX(',',CustomerPromotionVGUID)-1)) as value,
convert(varchar(1024),RIGHT(CustomerPromotionVGUID,LEN(CustomerPromotionVGUID)-charindex(',',CustomerPromotionVGUID))) as result,

convert(varchar(1024),left(PromotionVGUID,CHARINDEX(',',PromotionVGUID)-1)) as value1,
convert(varchar(1024),RIGHT(PromotionVGUID,LEN(PromotionVGUID)-charindex(',',PromotionVGUID))) as result1,

convert(varchar(1024),left(PolicyCode,CHARINDEX(',',PolicyCode)-1)) as value2,
convert(varchar(1024),RIGHT(PolicyCode,LEN(PolicyCode)-charindex(',',PolicyCode))) as result2
from cte1
union all
select
convert(varchar(1024),left(result,CHARINDEX(',',result)-1)) as value,
convert(varchar(1024),RIGHT(result,LEN(result)-charindex(',',result))) as result,

convert(varchar(1024),left(result1,CHARINDEX(',',result1)-1)) as value1,
convert(varchar(1024),RIGHT(result1,LEN(result1)-charindex(',',result1))) as result1,

convert(varchar(1024),left(result2,CHARINDEX(',',result2)-1)) as value2,
convert(varchar(1024),RIGHT(result2,LEN(result2)-charindex(',',result2))) as result2
from cte
where LEN(result)>1
)--,cte2 as (
select
ltrim(cte.value) as CustomerPromotionVGUID,
ltrim(cte.value1)as PromotionVGUID,
ltrim(cte.value2)as PolicyCode
FROM cte
...全文
177 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
Pact_Alice 2016-01-27
  • 打赏
  • 举报
回复
引用 4 楼 Tiger_Zhao 的回复:
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
我换别的拼法了
Tiger_Zhao 2016-01-27
  • 打赏
  • 举报
回复
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
Pact_Alice 2016-01-27
  • 打赏
  • 举报
回复
为什么没有人处理呢
Pact_Alice 2016-01-27
  • 打赏
  • 举报
回复
那位能帮我提供方法啊。在线等待中
Pact_Alice 2016-01-26
  • 打赏
  • 举报
回复

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' 
--已逗号与分号 拆分

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧