表结构:
ColNo Time SerialNo
1(p-key) 020111 030201 030301 AxCSAaa SceCC GYAZZ
需要:
根据SerialNo中不同的值(用空格分开)来获取Time中对应位置的值
Time格式是6个一组
ColNo Time SerialNo
1(p-key) 020111 030201 030301 AxCSAaa SceCC GYAZZ
需要:
根据SerialNo中不同的值(用空格分开)来获取Time中对应位置的值
Time格式是6个一组
select distinct
substr(a.Time,SpaceCount*7+1,6) as "时间",
a.SerialNo as "序列号"
from
(select (case when SpaceCount is null then 0 else SpaceCount end) as SpaceCount,calsign from (
select ColNo,
(length(replace(SerialNo,substr(SerialNo,instr(SerialNo,'GYA',1,1)),''))-
length(replace(replace(SerialNo,substr(SerialNo,instr(SerialNo,'GYA',1,1)),''),' ','')))/length(' ') as SpaceCount
from TableName
where SerialNo like '%GYA%')) b,
TableName a
where a.ColNo= b.ColNo and a.SerialNo like '%GYA%';