发一个笨方法:select t.FORGANGUID substr(t.total, 0, instr(t.total, ';', 2, 1) - 1), substr(t.fstatetype, 0, instr(t.fstatetype, ';', 2, 1) - 1) from table t where t.total like '%;%' union select t.FORGANGUID substr(t.total, instr(t.total, ';', 1, 1) + 1, instr(t.total, ';', -1, 1)), substr(t.fstatetype, instr(t.fstatetype, ';', 1, 1) + 1, instr(t.fstatetype, ';', -1, 1)) from table t where t.total like '%;%' union select * from table t where t.total not like '%;%'
少了个逗号,select t.FORGANGUID, substr(t.total, 0, instr(t.total, ';', 2, 1) - 1), substr(t.fstatetype, 0, instr(t.fstatetype, ';', 2, 1) - 1) from table t where t.total like '%;%' union select t.FORGANGUID, substr(t.total, instr(t.total, ';', 1, 1) + 1, instr(t.total, ';', -1, 1)), substr(t.fstatetype, instr(t.fstatetype, ';', 1, 1) + 1, instr(t.fstatetype, ';', -1, 1)) from table t where t.total like '%;%' union select * from table t where t.total not like '%;%'
1 0412105DA35645EA9A32AE160EEA25BF 1;1 estIllnessRest;estRetire
2 05AF08C3195B4075847685E96711457D 1 estRegular
3 10EDE82520F24289B70143EB4284AE84 6 estRegular
4 11EBAFE5E74343F39F0F111E571617F0 2 estRegular
5 15C892FDE6BA447C81D185E7072CC3E2 1 estRegular
6 253B4B7DD3A74C58B8A2409E6A83C130 1 estRegular
7 4665FDFA73454F36877F4F896812EDA1 1 estRegular
8 4A8864E4E8E94AEAA4396B06FBE689E1 1 estContractExpire
9 61E79C7A3D2F45B0A6F20C991BB5A52B 1 estRegular
10 A84CA33C205A4DFF8245034BD2C8E85D 1 estRegular
11 BAC2FFE592234644950D9DC9BDDC1412 1;1 estEarlyRetire;estRegular
12 BCC8B558F8984076BDC7E63B9FB010C6 1 estRegular
13 DD296336CA5E4032BC0211D3EEAFFAA8 5 estRegular
14 E27014B03B6A4997B41285F83EF7A736 1 estRegular
15 E639269F8F9D4190B45680E15B2BA89F 1 estRegular
16 4 estNewEmployee
分不够还可以加.....希望各位能尽快帮帮我!谢谢
1 0412105DA35645EA9A32AE160EEA25BF 1;1 estIllnessRest;estRetire
2 05AF08C3195B4075847685E96711457D 1 estRegular
3 10EDE82520F24289B70143EB4284AE84 6 estRegular
4 11EBAFE5E74343F39F0F111E571617F0 2 estRegular
5 15C892FDE6BA447C81D185E7072CC3E2 1 estRegular
6 253B4B7DD3A74C58B8A2409E6A83C130 1 estRegular
7 4665FDFA73454F36877F4F896812EDA1 1 estRegular
8 4A8864E4E8E94AEAA4396B06FBE689E1 1 estContractExpire
9 61E79C7A3D2F45B0A6F20C991BB5A52B 1 estRegular
10 A84CA33C205A4DFF8245034BD2C8E85D 1 estRegular
11 BAC2FFE592234644950D9DC9BDDC1412 1;1 estEarlyRetire;estRegular
12 BCC8B558F8984076BDC7E63B9FB010C6 1 estRegular
13 DD296336CA5E4032BC0211D3EEAFFAA8 5 estRegular
14 E27014B03B6A4997B41285F83EF7A736 1 estRegular
15 E639269F8F9D4190B45680E15B2BA89F 1 estRegular
16 4 estNewEmployee 需求: 将字段内值符号处理,并将原先提条数据拆分比如:
11 BAC2FFE592234644950D9DC9BDDC1412 1;1 estEarlyRetire;estRegular
拆分为
11 BAC2FFE592234644950D9DC9BDDC1412 1 estEarlyRetire
11 BAC2FFE592234644950D9DC9BDDC1412 1 estRegular
substr(t.fstatetype, 0, instr(t.fstatetype, ';', 2, 1) - 1)
from table t
where t.total like '%;%'
union
select t.FORGANGUID substr(t.total, instr(t.total, ';', 1, 1) + 1, instr(t.total, ';', -1, 1)),
substr(t.fstatetype,
instr(t.fstatetype, ';', 1, 1) + 1,
instr(t.fstatetype, ';', -1, 1))
from table t
where t.total like '%;%'
union
select * from table t where t.total not like '%;%'
substr(t.total, 0, instr(t.total, ';', 2, 1) - 1),
substr(t.fstatetype, 0, instr(t.fstatetype, ';', 2, 1) - 1)
from table t
where t.total like '%;%'
union
select t.FORGANGUID,
substr(t.total,
instr(t.total, ';', 1, 1) + 1,
instr(t.total, ';', -1, 1)),
substr(t.fstatetype,
instr(t.fstatetype, ';', 1, 1) + 1,
instr(t.fstatetype, ';', -1, 1))
from table t
where t.total like '%;%'
union
select * from table t where t.total not like '%;%'