|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
select
*
from
t
;
with
cte
as
(
select
TICKET_ID,CHARINDEX(
';'
,emp_id+
';'
)
as
station,
SUBSTRING
(EMP_ID,1,CHARINDEX(
';'
,emp_id+
';'
)-1)
as
emp_id
from
t
union
all
select
a.ticket_id,
CHARINDEX(
';'
,a.emp_id+
';'
,b.station+1)
,
SUBSTRING
(a.EMP_ID,b.station+1,CHARINDEX(
';'
,a.emp_id+
';'
,b.station+1)-b.station-1)
from
t
as
a
join
cte
as
b
on
a.TICKET_ID=b.ticket_id
where
CHARINDEX(
';'
,a.emp_id+
';'
,b.station+1)>0)
select
*
from
cte
--结果
TICKET_ID EMP_ID
---------- --------------------------------------------------
PO14100001 000001;000002;000003
PO14100002 000001;000004
PO14100003 000003;000004
PO14100004 000001;000002
PO14100005 000001;000002;000003
PO14100006 000001
(6 行受影响)
TICKET_ID station emp_id
---------- ----------- --------------------------------------------------
PO14100001 7 000001
PO14100002 7 000001
PO14100003 7 000003
PO14100004 7 000001
PO14100005 7 000001
PO14100006 7 000001
PO14100005 14 000002
PO14100005 21 000003
PO14100004 14 000002
PO14100003 14 000004
PO14100002 14 000004
PO14100001 14 000002
PO14100001 21 000003
(13 行受影响)
|