我有一个这样的表。
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
和increment operator
的想法(如果可能的话)。我可以使用进行格式化并获得Int值
SELECT field, CONVERT(SUBSTRING_INDEX(field,'-',-1), UNSIGNED INTEGER) AS num
FROM table
ORDER BY num;
请你帮我一把。。。大量赞赏
这是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;