基础数据如下create table tbA(id int,txt varchar(4000),sa varchar(100))
insert into tbA
select 1,'A.xxxx|B.Fjs.fsja|C.fsakfiwo','A|B' union all
select 2,'A.rwr431|B.24.xzc|C.wqhtrzf','A'
目标数据需要时这样的,
1 xxxx A true
1 Fjs.fsja B true
1 fsakfiwo C false
2 rwr431 A true
2 24.xzc B false
2 wqhtrzf C false
场景大概就是要帮客户导入在线测试的基础数据,tbA的txt字段是题目,sa是标准答案,目标数据的最后一列是标记该选项是否为标准答案的
insert into tbA
select 1,'A.xxxx|B.Fjs.fsja|C.fsakfiwo','A|B' union all
select 2,'A.rwr431|B.24.xzc|C.wqhtrzf','A'
目标数据需要时这样的,
1 xxxx A true
1 Fjs.fsja B true
1 fsakfiwo C false
2 rwr431 A true
2 24.xzc B false
2 wqhtrzf C false
场景大概就是要帮客户导入在线测试的基础数据,tbA的txt字段是题目,sa是标准答案,目标数据的最后一列是标记该选项是否为标准答案的
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html?23514
create table tbA(id int,txt varchar(4000),sa varchar(100))
insert into tbA
select 1,'A.xxxx|B.Fjs.fsja|C.fsakfiwo','A|B' union all
select 2,'A.rwr431|B.24.xzc|C.wqhtrzf','A'select id,txt,type=(case when CHARINDEX(left(txt,1),sa)>0 then 'true' else 'false' end)
from(
select id,txt=replace(PARSENAME(REPLACE(replace(txt,'.','*'),'|','.'),3),'*','.'),sa from tbA
union all
select id,txt=replace(PARSENAME(REPLACE(replace(txt,'.','*'),'|','.'),2),'*','.'),sa from tbA
union all
select id,txt=replace(PARSENAME(REPLACE(replace(txt,'.','*'),'|','.'),1),'*','.'),sa from tbA) a
order by id/*
id txt type
----------- ----------- -----
1 A.xxxx true
1 B.Fjs.fsja true
1 C.fsakfiwo false
2 C.wqhtrzf false
2 B.24.xzc false
2 A.rwr431 true(6 行受影响)
create table t1
(id int,txt varchar(4000),sa varchar(100))
insert into t1
select 1,'A.xxxx|B.Fjs.fsja|C.fsakfiwo','A|B' union all
select 2,'A.rwr431|B.24.xzc|C.wqhtrzf','A';with aaa as
(select a.*,b.number
from dbo.t1 as a inner join master..spt_values as b on b.number<=DATALENGTH(a.txt)+1
where b.type='P' and SUBSTRING('|'+a.txt,number,1)='|')
,bbb as
(select id,sa,SUBSTRING(txt,number,CHARINDEX('|',txt+'|',number)-number) as val from aaa)
select id,RIGHT(val,LEN(bbb.val)-2) as val,left(val,1) as sa,
case when CHARINDEX(LEFT(val,1),sa)>0 then 'true' else 'false' end as panduan from bbb
drop table tbA
create table tbA(id int,txt varchar(4000),sa varchar(100))
insert into tbA
select 1,'|A.xxxx|B.Fjs.fsja|C.fsakfiwo|','A|B' union all
select 2,'|A.rwr431|B.24.xzc|C.wqhtrzf|','A'
select * from tbA
select substring(txt, number, charindex('|',txt, number)-number) A, [sa] into #t
from tba, master..spt_values where type='p' and substring('|'+txt,number,1)='|' and charindex('|',txt, number)-number>0select [id], substring(A, 1, charindex('.', A)-1) A, substring(A, charindex('.', A)+1, len(A)-charindex('.', A)) B
,case when CHARINDEX('|'+substring(A, 1, charindex('.', A)-1)+'|', '|'+[SA]+'|') > 0 then 'True' else 'False' end
from (select substring(txt, number, charindex('|',txt, number)-number) A, [sa], [id]
from tba, master..spt_values where type='p' and substring('|'+txt,number,1)='|' and charindex('|',txt, number)-number>0) Aid A B D
----------- ------- -------- ----------
1 A xxxx True
1 B Fjs.fsja True
1 C fsakfiwo False
2 A rwr431 True
2 B 24.xzc False
2 C wqhtrzf False
from tba, master..spt_values where type='p' and substring('|'+txt,number,1)='|' and charindex('|',txt, number)-number>0 --这个不要
create table tbA(id int,txt varchar(4000),sa varchar(100))
insert into tbA
select 1,'A.xxxx|B.Fjs.fsja|C.fsakfiwo','A|B' union all
select 2,'A.rwr431|B.24.xzc|C.wqhtrzf','A'
select * from tbAselect [id], substring(A, 1, charindex('.', A)-1) A, substring(A, charindex('.', A)+1, len(A)-charindex('.', A)) B
,case when CHARINDEX('|'+substring(A, 1, charindex('.', A)-1)+'|', '|'+[SA]+'|') > 0 then 'True' else 'False' end
from (
select substring('|'+txt+'|', number, charindex('|','|'+txt+'|', number)-number) A, [sa], [id]
from tba, master..spt_values where type='p' and substring('||'+txt+'|',number,1)='|' and charindex('|','|'+txt+'|', number)-number>0) A
select a.*,b.number
from dbo.t1 as a inner join master..spt_values as b on b.number<=DATALENGTH(a.txt)+1
where b.type='P' and SUBSTRING('|'+a.txt,number,1)='|'
各位牛人们,这句sql该怎么理解呢,type字段是啥呢,number是啥字段呢,系统表spt_values是存啥东西的呢。