一.创建表
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