SQLSERVER解决多对多查询的解决方法

最近领导要求查询车间员工实际工序工时与标准工艺工时的对照，经查看数据，实际生产工艺不一定会和标准工艺一样，经多轮测试，记录一下，以备查

最初的语句

select
a.fbillno,
a.jcbh ,–机床编号,
a.date ,–日期,
a.type ,–加工类型,
a.fnumber,-- 零件代码,
a.ljmc,-- 零件名称,
cast(a.findex as varchar) +‘‘+a.gxmc scgx ,-- 工序名称,
a.zgxm ,–职工姓名,
a.time,-- 加工工时,
a.sl ,–数量,
a.time/a.sl djgs,–单件工时
cast(b.FEntryID as varchar) +’’+ cast(b.fopername as varchar) bzgx,–标准工序
b.ftimerun --标准工时
from vw_chr_jjcjgs a
right JOIN vw_icrouting b on a.fnumber=b.fitemnumber and b.fdefault=‘是’ --and a.findex=b.FEntryID
and a.type=‘新制’
where 1=1 and a.date>=‘2022/07/01’ and a.date<‘2022/08/10’ and a.FNumber=‘2.01.36.214083’ and a.jcbh='A2286 ’

可以看到结果是产生笛卡尔积

一、 使用ROW_NUMBER() OVER ( PARTITION BY XXX ORDER BY GETDATE()) row

select
*
–jcbh,date,type,fnumber,ljmc,scgx,zgxm,time,sl,djgs,bzgx,FTimeRun
from (
SELECT CASE WHEN row = 1 THEN jcbh
ELSE ‘’ END jcbh,
CASE WHEN row = 1 THEN date
ELSE ‘’ END date,
CASE WHEN row = 1 THEN type
ELSE ‘’ END type,
CASE WHEN row = 1 THEN fnumber
ELSE ‘’ END fnumber,
CASE WHEN row = 1 THEN ljmc
ELSE ‘’ END ljmc,
CASE WHEN row = 1 THEN scgx
ELSE ‘’ END scgx,
CASE WHEN row = 1 THEN zgxm
ELSE ‘’ END zgxm,
CASE WHEN row = 1 THEN time
ELSE null END time,
CASE WHEN row = 1 THEN sl
ELSE null END sl,
CASE WHEN row = 1 THEN djgs
ELSE null END djgs,
bzgx, ftimerun
FROM

(
SELECT *,ROW_NUMBER() OVER ( PARTITION BY
fbillno,
jcbh ,–机床编号,
date ,–日期,
type ,–加工类型,
fnumber,-- 零件代码,
ljmc,-- 零件名称,
scgx,
zgxm,
time,
sl,
djgs

ORDER BY GETDATE()
) row
FROM
(
select
a.fbillno,
a.jcbh ,–机床编号,
a.date ,–日期,
a.type ,–加工类型,
a.fnumber,-- 零件代码,
a.ljmc,-- 零件名称,
cast(a.findex as varchar) +‘‘+a.gxmc scgx ,-- 工序名称,
a.zgxm ,–职工姓名,
a.time,-- 加工工时,
a.sl ,–数量,
a.time/a.sl djgs,–单件工时
cast(b.FEntryID as varchar) +’’+ cast(b.fopername as varchar) bzgx,–标准工序
b.ftimerun --标准工时
from vw_chr_jjcjgs a
right JOIN vw_icrouting b on a.fnumber=b.fitemnumber and b.fdefault=‘是’ --and a.findex=b.FEntryID
and a.type=‘新制’
where 1=1 and a.date>=‘2022/07/01’ and a.date<‘2022/08/10’ and a.FNumber=‘2.01.36.214083’ and a.jcbh='A2286 ’

)T
)TT
)TTT

结果不是想要的

二、 加DISTINCT

这样也不对

三、 使用UNION

select jcbh,date,type,fnumber,ljmc,scgx,zgxm,time,sl,djgs,bzgx,ftimerun from
(
select
a.fbillno,
a.jcbh ,–机床编号,
a.date ,–日期,
a.type ,–加工类型,
a.fnumber,-- 零件代码,
a.ljmc,-- 零件名称,
cast(a.findex as varchar) +‘_’+a.gxmc scgx ,-- 工序名称,
a.zgxm ,–职工姓名,
a.time,-- 加工工时,
a.sl ,–数量,
a.time/a.sl djgs–单件工时
,‘’ bzgx,null ftimerun
from vw_chr_jjcjgs a
where 1=1 and a.date>=‘2022/07/01’ and a.date<‘2022/08/10’ and a.FNumber=‘2.01.36.214083’ and a.jcbh='A2286 ’

union all

select fbillno,‘’,‘’,‘’,‘’,‘’,‘’,‘’,null,null,null,bzgx,ftimerun from
(
select fbillno,bzgx,ftimerun from
(
select
a.fbillno,
a.jcbh ,–机床编号,
a.date ,–日期,
a.type ,–加工类型,
a.fnumber,-- 零件代码,
a.ljmc,-- 零件名称,
cast(a.findex as varchar) +‘‘+a.gxmc scgx ,-- 工序名称,
a.zgxm ,–职工姓名,
a.time,-- 加工工时,
a.sl ,–数量,
a.time/a.sl djgs,–单件工时
cast(b.FEntryID as varchar) +’’+ cast(b.fopername as varchar) bzgx,–标准工序
b.ftimerun --标准工时
from vw_chr_jjcjgs a
right JOIN vw_icrouting b on a.fnumber=b.fitemnumber and b.fdefault=‘是’ --and a.findex=b.FEntryID
and a.type=‘新制’
where 1=1 and a.date>=‘2022/07/01’ and a.date<‘2022/08/10’ and a.FNumber=‘2.01.36.214083’ and a.jcbh='A2286 ’
)T group by fbillno,bzgx,ftimerun) b
)TT

这样更不对

四、 用临时表法

基本思路是先根据实际的单据编号（vw_chr_jjcjgs中的FBILLNO），关联零件代码先建一个标准工序临时表，每行附序号（FENTRYID）；然后关联FBILLNO和FENERYID，将实际工时表（vw_chr_jjcjgs）的数据挺进去；还有实际加工比标准多的数据，取每个FBILLNO最大的FENTRYID，然后关联vw_chr_jjcjgs中比大于FENTRYID的记录，再插入临时表

—建临时表

create table ##GSDXH_BZGS
(
fbillno varchar(15),
jcbh varchar(15),–机床编号,
date date,–日期,
type varchar(15),–加工类型,
fnumber varchar(20),-- 零件代码,
ljmc varchar(100),-- 零件名称,
FINDEX INT,
scgx varchar(15),-- 工序名称,
zgxm varchar(15),–职工姓名,
time dec,-- 加工工时,
sl dec,–数量,
djgs dec,–单件工时
FEntryID int,
bzgx varchar(15),–标准工序
ftimerun dec–标准工时
)

—插入数据，工时表中零件有标准工艺的数据
insert into ##GSDXH_BZGS (
fbillno ,
FEntryID ,
bzgx ,–标准工序
ftimerun --标准工时
)
select fbillno,FEntryID,bzgx,ftimerun from
(
select
a.fbillno,
a.jcbh ,–机床编号,
a.date ,–日期,
a.type ,–加工类型,
a.fnumber,-- 零件代码,
a.ljmc,-- 零件名称,
cast(a.findex as varchar) +‘‘+a.gxmc scgx ,-- 工序名称,
a.zgxm ,–职工姓名,
a.time,-- 加工工时,
a.sl ,–数量,
a.time/a.sl djgs,–单件工时
b.FEntryID,
cast(b.FEntryID as varchar) +’’+ cast(b.fopername as varchar) bzgx,–标准工序
b.ftimerun --标准工时
from vw_chr_jjcjgs a
right JOIN vw_icrouting b on a.fnumber=b.fitemnumber and b.fdefault=‘是’ --and a.findex=b.FEntryID
and a.type=‘新制’
where 1=1 and a.date>=‘2022/07/01’ and a.date<‘2022/07/30’
)T group by fbillno,FEntryID,bzgx,ftimerun

—将工时表中的数据，更新进临时表
UPDATE ##GSDXH_BZGS SET
jcbh =a.jcbh ,–机床编号,
date = a.date,–日期,
type =a.type ,–加工类型,
fnumber = a.fnumber,-- 零件代码,
ljmc =a.ljmc,-- 零件名称,
findex =a.findex,
scgx = cast(a.findex as varchar) +‘_’+a.gxmc ,-- 工序名称,
zgxm =a.zgxm,–职工姓名,
time =a.time,-- 加工工时,
sl =a.sl,–数量,
djgs =a.time/a.sl
from vw_chr_jjcjgs a
INNER JOIN ##GSDXH_BZGS B ON A.fbillno=B.FBILLNO AND A.FIndex=B.FENTRYID

—插入实际工时记录比标准多的记录
insert into ##GSDXH_BZGS
(
fbillno ,
jcbh ,–机床编号,
date ,–日期,
type ,–加工类型,
fnumber ,-- 零件代码,
ljmc ,-- 零件名称,
FINDEX ,
scgx ,-- 工序名称,
zgxm ,–职工姓名,
time ,-- 加工工时,
sl ,–数量,
djgs --单件工时
)
select
a.fbillno,
a.jcbh ,–机床编号,
a.date,–日期,
a.type ,–加工类型,
a.fnumber,-- 零件代码,
a.ljmc,-- 零件名称,
a.findex,
cast(a.findex as varchar) +‘_’+a.gxmc scgx,-- 工序名称,
a.zgxm,–职工姓名,
a.time,-- 加工工时,
a.sl,–数量,
a.time/a.sl djgs
from vw_chr_jjcjgs a
inner join (select fbillno, max(fentryid) max_linenum from ##GSDXH_BZGS group by fbillno)b
on a.fbillno=b.fbillno and a.findex>b.max_linenum

Select * from ##GSDXH_BZGS order by fbillno

这样就可以了