java - MYSQL SUBSTRING_INDEX/CONVERT/IF 子句

Question

我有一张 table ，上面有这样的东西。

abc-cde-0001222
abc-cde-0001223
abc-ced-0001224
cde-efg-0003001
ced-efg-0003002
ced-efg-0003008

我希望选择abc-cde-0001222、abc-ced-0001224

        also cde-efg-0003001 , cde-efg-0003002

             ced-efg-0003008  , ""

看，我需要获取某个范围的第一个和最后一个值。如果特定值只是一个项目(不在范围内)，我希望它选择作为第一个值和空值。

到目前为止，我只有一个使用IF和增量运算符的想法(如果可能的话)。我可以使用

进行格式化并获取 Int 值

SELECT field, CONVERT(SUBSTRING_INDEX(field,'-',-1), UNSIGNED INTEGER) AS num
FROM table
ORDER BY num;

请你帮帮我...非常感谢

Answer 1

这是 MySQL 中的一个难题。但是，这是可能的。逻辑是找到序列的开始位置。您可以使用相关子查询和一些算术来完成此操作。然后您需要启动的累积总和。这比较困难，因为它需要对相关子查询执行相关子查询。

首先，设置 NullIfStart 变量:

select t.*,
       (select 0
        from t t2
        where left(t2.field, 8) = left(t.field, 8) and
              t2.field < t.field and
              right(t2.field, 7) + 0 = right(t.field, 7) + 0 - 1
       ) as NullIfStart
from t;

这里获取累积和，可以用作分组字段:

select t.*
       (select sum(NullIfStart is null)
        from (select t1.*,
                     (select 0
                      from t t2
                      where left(t2.field, 8) = left(t1.field, 8) and
                            t2.field < t1.field and
                            right(t2.field, 7) + 0 = right(t1.field, 7) + 0 - 1
                     ) as NullIfStart
               from t t1
              ) tnis
         where right(tnis.field, 7) = right(t.field, 7) and
               tnis.field <= t.field
        ) grp
from t;

最终的解决方案是进行聚合:

select min(field),
       (case when max(field <> min(field) then max(field) end)
from (select t.*
             (select sum(NullIfStart is null)
              from (select t1.*,
                           (select 0
                            from t t2
                            where left(t2.field, 8) = left(t1.field, 8) and
                                  t2.field < t1.field and
                                  right(t2.field, 7) + 0 = right(t1.field, 7) + 0 - 1
                           ) as NullIfStart
                     from t t1
                    ) tnis
               where left(tnis.field, 8) = left(t.field, 8) and
                     tnis.field <= t.field
              ) grp
      from t
     ) s
group by left(s.field, 8), grp;