sql:用虚拟对象填充有序字符串序列中的缺失

ifmq2ha2  于 2021-06-24  发布在  Mysql
关注(0)|答案(3)|浏览(314)

我有带序列的字符串。理想的字符串是01-02-03-04,但在一些记录中我遗漏了:

---------
| seq0  |
| ----- |
| 01-04 |
| 02-03 |
| 02-04 |
| 01-04 |
| 02    |
---------

这些都是例子。任何组合都是可能的。为了更好地识别丢失的内容,我想为丢失的内容插入xx,以获得以下信息:

-----------------------
| seq0  | seq1        | 
| ----- | ----------- | 
| 01-04 | 01-xx-xx-04 | 
| 02-03 | xx-02-03-xx | 
| 02-04 | xx-02-xx-04 | 
| 01-04 | 01-xx-xx-04 | 
| 02    | xx-02-xx-xx | 
-----------------------

我有一个使用regexp和replace的解决方案(见下文)。但是如果序列较长(例如:01-…-12),那么编写代码将很麻烦。我想知道有没有别的办法。我可以使用短过程(mysql),但也许有一种优雅的方法可以用(几乎)纯sql来实现这一点。
生成表的代码:

DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
  seq0       VARCHAR(100)
, seq1      VARCHAR(100)
);

INSERT INTO t0 (seq0) VALUES 
 ('01-04')
,('02-03')
,('02-04') 
,('01-04')
,('02')
;

SELECT * FROM t0;

UPDATE t0
SET seq1 = seq0
    -- insert xx       
    , seq1 = CASE WHEN seq1 REGEXP '01-03' THEN REPLACE(seq1,'01-03','01-xx-03') ELSE seq1 END
    , seq1 = CASE WHEN seq1 REGEXP '01-04' THEN REPLACE(seq1,'01-04','01-xx-xx-04') ELSE seq1 END   

    , seq1 = CASE WHEN seq1 REGEXP '02-04' THEN REPLACE(seq1,'02-04','02-xx-04') ELSE seq1 END   

    -- right pad xx
    , seq1 = CASE WHEN seq1 REGEXP '01$' THEN REPLACE(seq1,'01','01-xx-xx-xx') ELSE seq1 END   
    , seq1 = CASE WHEN seq1 REGEXP '02$' THEN REPLACE(seq1,'02','02-xx-xx') ELSE seq1 END   
    , seq1 = CASE WHEN seq1 REGEXP '03$' THEN REPLACE(seq1,'03','03-xx') ELSE seq1 END   

    -- left pad xx
    , seq1 = CASE WHEN seq1 REGEXP '^02' THEN REPLACE(seq1,'02','xx-02') ELSE seq1 END   
    , seq1 = CASE WHEN seq1 REGEXP '^03' THEN REPLACE(seq1,'03','xx-xx-03') ELSE seq1 END   
    , seq1 = CASE WHEN seq1 REGEXP '^04' THEN REPLACE(seq1,'04','xx-xx-xx-04') ELSE seq1 END   
    ;
SELECT * FROM t0;
myss37ts

myss37ts1#

一种方法是使用 REPLACE :

SELECT seq0,
      REPLACE(REPLACE(REPLACE(
      REPLACE('01-02-03-04',
         CASE WHEN INSTR(seq0, '01') > 0 THEN 'u' ELSE '01' END, 'xx')
        ,CASE WHEN INSTR(seq0, '02') > 0 THEN 'u' ELSE '02' END, 'xx')
        ,CASE WHEN INSTR(seq0, '03') > 0 THEN 'u' ELSE '03' END, 'xx')
        ,CASE WHEN INSTR(seq0, '04') > 0 THEN 'u' ELSE '04' END, 'xx') AS result
FROM t0;

dbfiddle演示
输出:

┌───────┬─────────────┐
│ seq0  │   result    │
├───────┼─────────────┤
│ 01-04 │ 01-xx-xx-04 │
│ 02-03 │ xx-02-03-xx │
│ 02-04 │ xx-02-xx-04 │
│ 01-04 │ 01-xx-xx-04 │
│ 02    │ xx-02-xx-xx │
└───────┴─────────────┘

编辑

更高级的形式(如果您不喜欢嵌套多个替换):

SELECT seq0, result
FROM (
    SELECT seq0
        ,@u:= REPLACE(IF(@prev_value=seq0, @u,@start_string),
                      IF(INSTR(seq0, sub.c) > 0, 'u', sub.c), 'xx') AS result
        ,@cnt:=IF(@prev_value=seq0,@cnt-1,@l) AS c
        ,@prev_value := seq0
    FROM ( SELECT DISTINCT *
        FROM t0 ,(SELECT '01' AS c UNION SELECT '02' 
                  UNION SELECT '03' UNION SELECT '04') num)sub
    ,(SELECT @u := ''
        ,@prev_value := ''
        ,@start_string := '01-02-03-04'
        ,@l := length(@start_string)-length(replace(@start_string,'-',''))
        ,@cnt := 0
        ) z
    ORDER BY sub.seq0, sub.c
) q
WHERE q.c = 0;

D小提琴演示2
输出:

┌───────┬─────────────┐
│ seq0  │   result    │
├───────┼─────────────┤
│ 01-04 │ 01-xx-xx-04 │
│ 02    │ xx-02-xx-xx │
│ 02-03 │ xx-02-03-xx │
│ 02-04 │ xx-02-xx-04 │
└───────┴─────────────┘
eit6fx6z

eit6fx6z2#

虽然我上面的答案是好的,但lukasz szozda有一个更简单的方法。如果你只有几个条目,帮你自己一个忙,保持简单,像这样!
如果您需要一个更健壮的选项,可以扩展到更大的序列,那么这里有另一个使用stuff(…)的选项。如果序列变大并且必须对大量记录执行此操作,则它很可能比其他选项更有效。

CREATE FUNCTION F_GetPattern
(
    @Sequence VARCHAR(100)
)
RETURNS
VARCHAR(1000)
AS
BEGIN
    DECLARE @T1 VARCHAR(10)
    DECLARE @T2 VARCHAR(10)
    DECLARE @I1 INT 
    DECLARE @I2 INT = 0
    DECLARE @Result VARCHAR(100) = 'xx-xx-xx-xx'

    SET @T1 = SUBSTRING(@Sequence, 1, 2)
    SET @T2 = SUBSTRING(@Sequence, 4, 2)

    SET @I1 = CAST(@T1 AS INT)
    IF (LEN(@Sequence ) > 3)
        SET @I2 = CAST(@T2 AS INT)

    SET @Result = STUFF(@Result, @I1 * 3 -2, 2, @T1) 
    IF @I2 > 0
        SET @Result = STUFF(@Result, @I2 * 3 -2, 2, @T2)
    return @Result
END
GO
SELECT seq0, DBO.F_GetPattern(seq0) FROM t0
GO
kuarbcqp

kuarbcqp3#

我不会使用正则表达式。如果使用c或其他编程语言,速度会快得多,但这应该是可行的。
注意:这不会处理具有错误数据的seq0。

ALTER FUNCTION F_GetPattern
(
    @Sequence VARCHAR(100)
)
RETURNS
VARCHAR(1000)
AS
BEGIN
    DECLARE @T1 VARCHAR(10)
    DECLARE @T2 VARCHAR(10)
    DECLARE @I1 INT 
    DECLARE @I2 INT = 0
    DECLARE @Size INT = 4
    DECLARE @Index INT = 0
    DECLARE @Result VARCHAR(100) = ''

    SET @T1 = SUBSTRING(@Sequence, 1, 2)
    SET @T2 = SUBSTRING(@Sequence, 4, 2)

    SET @I1 = CAST(@T1 AS INT)
    IF (LEN(@Sequence ) > 3)
        SET @I2 = CAST(@T2 AS INT)

    WHILE @Index < @Size
    BEGIN
        IF @Index > 0 AND @Index < @Size
            SET @Result = @Result + '-'
        IF @Index + 1 = @I1 
            SET @Result = @Result + @T1
        ELSE
        BEGIN
            IF @Index + 1 = @I2
                SET @Result = @Result + @T2
            ELSE
                SET @Result = @Result + 'xx'
        END
        SET @Index = @Index + 1
    END

    return @Result
END
GO
SELECT seq0, DBO.F_GetPattern(seq0) FROM t0
GO

相关问题