sql字段存储多字段需分割连表查询

一.创建表

1.原料表 记录批号 物料代码 等物料信息

 if object_id('[tb_i259b]') is not null drop table tb_i259b
go
create table tb_i259b(tb_i259b_id int ,tb_i259b_r596j varchar(8000),tb_i259b_i231a varchar(8000),tb_i259b_c24u varchar(8000),tb_i259b_num varchar(8000) )
insert tb_i259b
select '915','粉剂原料','2.04.014','乳糖','1028661006' union all
select '946','粉剂原料','2.04.014','乳糖','1108431006'
go

2.使用某原料多少 （会存在一条使用多条原料，此时存储为 批号，批号 ）

if object_id('[tb_p525h]') is not null drop table tb_p525h
go
create table tb_p525h(tb_p525h_id int ,tb_p525h_rtph varchar(8000),tb_p525h_rtpht varchar(8000) )
insert tb_p525h
select '17753','1028661006,1108431006','50,50'
go

二.写sql

1.获取 每个原料使用了多少

 SELECT
        b.tb_i259b_r596j,
        b.tb_i259b_i231a,
        b.tb_i259b_c24u,
        b.tb_i259b_num ,
        a.tb_p525h_rtph,
        cast(a.tb_p525h_rtpht AS varchar(100))        
FROM tb_p525h a
LEFT JOIN tb_i259b b
ON CHARINDEX(','+LTRIM(b.tb_i259b_num)+',',','+a.tb_p525h_rtph+',')>0
WHERE a.tb_p525h_id = '17753'
 AND b.tb_i259b_i231a IN ('2.04.014')

sql 翻译
a表 关联 b表 ，关联条件为 ：CHARINDEX(’,’+LTRIM(b.tb_i259b_num)+’,’,’,’+a.tb_p525h_rtph+’,’)>0
意思为：b表的 1108431006 在 a表的 1028661006,1108431006 中可以找到（>0 就是可以找到）
因为数字防止包含，找错，包一层，
查找 b表的 ,1108431006, 在 a表的 ,1028661006,1108431006, 中是否可以找到

关键词使用
1.cast(a.tb_p525h_rtpht AS varchar(100))
sql server cast()函数
转换字段类型
详情可参考： https://blog.csdn.net/lanxingbudui/article/details/81907054
2. CHARINDEX(’,’+LTRIM(b.tb_i259b_num)+’,’,’,’+a.tb_p525h_rtph+’,’)>0
sql server CHARINDEX()
查找字符（char）的位置（index）
详情可参考: https://www.cnblogs.com/qianxingdewoniu/p/6858580.html

2.此时sql 解决了批号的问题，但重量还需要到对应的该条

拆分批号，并且知道是第几个

select  tb_p525h_id,
 tb_p525h_rtph=substring(tb_p525h_rtph,b.number,charindex(',',tb_p525h_rtph+',',b.number)-b.number),
 b.number,
row_number() over(order by b.number) as row_number
from tb_p525h 
join master..spt_values b on b.type='P'
where charindex(',',','+tb_p525h_rtph,b.number)=b.number
 and tb_p525h_id= 17753

关键词使用
1.master…spt_values b on b.type=‘P’，这个的用法，在这里认为是1,2,3,4，这样的数字表，即可

2.Sqlserver根据某字段分隔符将一条记录拆分为多行记录
详情参考 https://blog.csdn.net/qq_28009401/article/details/53468197
参考语句：

if object_id('[aaa]') is not null drop table [aaa]
go
create table [aaa]([地区] varchar(4),[内容] varchar(45))
insert [aaa]
select '中国','021sp.html|管材|4355;028sp.html|建筑材料|3209' union all
select '中国','023sp.html|材|4356;025sp.html|建|9209'
go

select  a.地区,
 内容=substring(a.内容,b.number,charindex(';',a.内容+';',b.number)-b.number)
from [aaa] a
join master..spt_values b on b.type='P'
where charindex(';',';'+a.内容,b.number)=b.number

2.row_number() over(order by b.number) as row_number
使用row_number（）函数进行编号
详情参考 :https://blog.csdn.net/qq_25221835/article/details/82762416

3.关联批号和重量

第一个批号=第一个重量 a.row_number = b.row_number

 select  a.tb_p525h_id,a.tb_p525h_rtph,tb_p525h_rtpht from 
(select  tb_p525h_id,
 tb_p525h_rtph=substring(tb_p525h_rtph,b.number,charindex(',',tb_p525h_rtph+',',b.number)-b.number),
 b.number,
row_number() over(order by b.number) as row_number
from tb_p525h 
join master..spt_values b on b.type='P'
where charindex(',',','+tb_p525h_rtph,b.number)=b.number
 and tb_p525h_id= 17753
  ) a
  
  
 left join (
 select  tb_p525h_id,
  tb_p525h_rtpht=substring(tb_p525h_rtpht,b.number,charindex(',',tb_p525h_rtpht+',',b.number)-b.number),
  b.number,
row_number() over(order by b.number) as row_number
from tb_p525h 
join master..spt_values b on b.type='P'
where charindex(',',','+tb_p525h_rtpht,b.number)=b.number
 and tb_p525h_id= 17753
) b 
 on a.tb_p525h_id = b.tb_p525h_ID
 and a.row_number = b.row_number

4.合并sql1 和sql2

 select   b.tb_i259b_r596j,
        b.tb_i259b_i231a,
        b.tb_i259b_c24u,
        b.tb_i259b_num ,
        a.tb_p525h_rtph,
        cast(a.tb_p525h_rtpht AS varchar(100)) zl  from 
 (
 select  a.tb_p525h_id,a.tb_p525h_rtph,tb_p525h_rtpht from 
(select  tb_p525h_id,
 tb_p525h_rtph=substring(tb_p525h_rtph,b.number,charindex(',',tb_p525h_rtph+',',b.number)-b.number),
 b.number,
row_number() over(order by b.number) as row_number
from tb_p525h 
join master..spt_values b on b.type='P'
where charindex(',',','+tb_p525h_rtph,b.number)=b.number
 and tb_p525h_id= 17753
  ) a
 left join (
 select  tb_p525h_id,
  tb_p525h_rtpht=substring(tb_p525h_rtpht,b.number,charindex(',',tb_p525h_rtpht+',',b.number)-b.number),
  b.number,
row_number() over(order by b.number) as row_number
from tb_p525h 
join master..spt_values b on b.type='P'
where charindex(',',','+tb_p525h_rtpht,b.number)=b.number
 and tb_p525h_id= 17753
) b 
 on a.tb_p525h_id = b.tb_p525h_ID
 and a.row_number = b.row_number) a
 left join tb_i259b b on a.tb_p525h_rtph = b.tb_i259b_num