双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

sql 查询工序

2010-03-24 05:09:58 312 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

weasea 墨衍会员 · AI 创作全网分发关注 + 加入社区

SAP工艺路线表关联查询实战：从MAPL到PLPO，一条SQL搞定工序清单本文详细解析了SAP工艺路线表关联查询的实战技巧，从MAPL到PLPO的高效SQL实现。通过四表联查（MAPL、PLKO、PLPO、PLAS）获取有效工序清单，避免常见陷阱如忽略删除标记。文章还提供了性能优化原则和高级应用场景，帮助SAP顾问提升工艺路线查询效率。阅读详情

tblSNProcess
indexID SN ProcessID status date
1 200101 1 1 2009-1-1
2 200101 2 -1 2009-1-2
3 200101 3 0 2009-1-3

tblProcess
ProcessID ProcessName
1 工序1
2 工序2
3 工序3

我想查的是，最新status是0的工序名。但如果上道工序是-1，则应该是维修（不在tblprocess里)
结果应该是维修

...全文

SAP工艺路线表关联查询实战：从MAPL到PLPO，一条SQL搞定工序清单本文深入解析SAP系统中工艺路线表（MAPL、PLKO、PLPO等）的关联关系，提供高效SQL查询模板，帮助技术顾问快速获取工序清单。通过实战案例展示从基础查询到性能优化的完整解决方案，提升制造业SAP系统数据查询效率。阅读详情

weasea

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

microsofttyc 2010-03-25

打赏
举报

没明白楼主的意思

ChinaJiaBing 2010-03-25

打赏
举报



---try



declare @tbisnprocess table (indexid int,sn int,processid int,status int,date datetime)

insert into @tbisnprocess select 1,'200101',1,1,'2009-1-1'

                union all select 2,'200101',2,-1,'2009-1-2'

                union all select 3,'200101',3,0,'2009-1-3'

                union all select 4,'200101',0,0,'2009-1-3'

                union all select 1,'200102',1,1,'2009-1-1'

                union all select 2,'200102',2,1,'2009-1-2'

                union all select 3,'200102',3,1,'2009-1-3'

                union all select 4,'200102',0,0,'2009-1-3'

                union all select 1,'200103',1,1,'2009-1-1'

                union all select 2,'200103',2,1,'2009-1-2'

                union all select 3,'200103',3,0,'2009-1-3'

                union all select 4,'200103',0,0,'2009-1-3'

declare @tblProcess table (processid int,processName nvarchar(100))

insert into @tblProcess select 1,'工序1'

              union all select 2,'工序2'

              union all select 3,'工序3'



select sn,max(processName) from 

       (select b.processid,sn,processName= case when status=0 and exists(select processid from @tbisnprocess where a.sn=sn 

        and a.indexid=indexid+1 and processid='3')  then '完工' when exists(select status from @tbisnprocess where a.sn=sn 

        and a.indexid=indexid+1 and status=-1)

        then '维修' when exists(select status from @tbisnprocess where a.sn=sn 

        and a.indexid=indexid+1 and status=1) then 

        '工序3' end

        from @tbisnprocess a join @tblprocess b  on a.processid=b.processid) tb group by sn

    





(3 行受影响)

sn          

----------- -----

200101      维修

200102      工序3

200103      工序3

zsforever 2010-03-25

打赏
举报

if (object_id('tempdb..#tblSNProcess') > 0)
drop table #tblSNProcess
create table #tblSNProcess(indexID varchar(10), SN int,ProcessID varchar(10),status int,date varchar(20))
insert #tblSNProcess
select '1','200101','1',1,'2009-1-1' union
select '2','200101','2',-1,'2009-1-2' union
select '3','200101','3',0,'2009-1-3' union
select '4','200101','0',0,'2009-1-3' union
select '1','200102','1',1,'2009-1-1' union
select '2','200102','2',1,'2009-1-2' union
select '3','200102','3',1,'2009-1-3' union
select '4','200102','0',0,'2009-1-3' union
select '1','200103','1',1,'2009-1-1' union
select '2','200103','2',1,'2009-1-2' union
select '3','200103','3',0,'2009-1-3' union
select '4','200103','0',0,'2009-1-3'

if (object_id('tempdb..#tblProcess') > 0)
drop table #tblProcess
create table #tblProcess(ProcessID varchar(10),ProcessName nvarchar(20))
insert #tblProcess
select '1',N'工序1' union
select '2',N'工序2' union
select '3',N'工序3'

select a.SN,下道工序=case a.status when -1 then N'维修' when 0 then b.ProcessName when 1 then N'完工' end
from #tblSNProcess a inner join
#tblProcess b on a.ProcessID=b.ProcessID
where a.indexID=(select top 1 indexID from #tblSNProcess x inner join #tblProcess y on x.ProcessID=y.ProcessID
where x.SN=a.SN order by x.status,x.indexID desc)

owen_0075 2010-03-25

打赏
举报



不是很明白楼主的意思：



if (object_id('tempdb..#tblSNProcess') > 0)

  drop table #tblSNProcess

create table #tblSNProcess(indexID  varchar(10), SN  int,ProcessID  varchar(10),status int,date varchar(20))

insert #tblSNProcess 

select '1','200101','1',1,'2009-1-1' union

select '2','200101','2',-1,'2009-1-2' union

select '3','200101','3',0,'2009-1-3' union

select '4','200101','0',0,'2009-1-3' union

select '1','200102','1',1,'2009-1-1' union

select '2','200102','2',1,'2009-1-2' union

select '3','200102','3',1,'2009-1-3' union

select '4','200102','0',0,'2009-1-3' union

select '1','200103','1',1,'2009-1-1' union 

select '2','200103','2',1,'2009-1-2' union

select '3','200103','3',0,'2009-1-3' union

select '4','200103','0',0,'2009-1-3'



if (object_id('tempdb..#tblProcess') > 0)

  drop table #tblProcess

create table #tblProcess(ProcessID varchar(10),ProcessName varchar(20))

insert #tblProcess 

select '1','工序1' union

select '2','工序2' union

select '3','工序3'



select T1.SN,case sum(T1.status) when 0 then '維修' 

                                 when 3 then '完工'

                                 when 2 then (select distinct T3.Processname from #tblProcess T3 

                                                join #tblSNProcess T4 on(T4.ProcessID = T3.ProcessID and T4.ProcessID =3)) end '下道工序'

from #tblSNProcess T1

left join #tblProcess T2 on (T1.ProcessID = T2.ProcessID)

group by T1.SN

order by T1.SN



SN          下道工序

----------- --------------------

200101      維修

200102      完工

200103      工序3

weasea 2010-03-25

打赏
举报

我本来用函数完成了这个需求
queryNextProc(@SN)

但是如果是查询几天内的情况
@SN我就不会了
比如 2009-1-1 到2009-1-2的所有序列号的下道工序，函数好像就不会用了。

weasea 2010-03-25

打赏
举报

对，一定有这个。
数据库老外建立的。查询语句害死人啊。
都是交叉表。

dawugui 2010-03-25

打赏
举报

[Quote=引用 11 楼 weasea 的回复:]
1 200102 1 1 2009-1-1
2 200102 2 1 2009-1-2
3 200102 3 1 2009-1-3
4 200102 0 0 2009-1-3

因为process_ID =3 代表完成了，所以没有下道工序
所以
200102 的下道工序是完成。
[/Quote]
每个都有:process_ID =3?

好难理解你的需求,帮顶.

weasea 2010-03-25

打赏
举报

1 200102 1 1 2009-1-1
2 200102 2 1 2009-1-2
3 200102 3 1 2009-1-3
4 200102 0 0 2009-1-3

因为process_ID =3 代表完成了，所以没有下道工序
所以
200102 的下道工序是完成。

东那个升 2010-03-25

打赏
举报

没理解，帮顶。

dawugui 2010-03-25

打赏
举报

2 200101 2 -1 2009-1-2 维修
2 200102 2 1 2009-1-2 下道工序名称。
2 200103 2 1 2009-1-2 下道工序名称。

和你的结果有点出入?

weasea 2010-03-25

打赏
举报

up。。。。。。

weasea 2010-03-25

打赏
举报

对，processid是定了的。条码生成的时候就生成了。

ws_hgo 2010-03-25

打赏
举报

还是没有看懂你的
每个sn中都有ProcessID=3的啊

weasea 2010-03-25

打赏
举报

都不是我想要的哦。。可能我没表达清楚，我把数据写多点吧
tblSNProcess
indexID SN ProcessID status date
1 200101 1 1 2009-1-1
2 200101 2 -1 2009-1-2
3 200101 3 0 2009-1-3
4 200101 0 0 2009-1-3

1 200102 1 1 2009-1-1
2 200102 2 1 2009-1-2
3 200102 3 1 2009-1-3
4 200102 0 0 2009-1-3

1 200103 1 1 2009-1-1
2 200103 2 1 2009-1-2
3 200103 3 0 2009-1-3
4 200103 0 0 2009-1-3

tblProcess
ProcessID ProcessName
1 工序1
2 工序2
3 工序3
我想查的是根据indexID 查下道工序的名称，
如果上道工序processid=3 那就是完工，不用查下道工序。如果上道工序status=-1，那就是维修，如果上道工序 status=1，那就是下道工序名称。

查
sn 下道工序
200101 维修
200102 完工
200103 工序3

weasea 2010-03-25

打赏
举报

up。。。。。。

weasea 2010-03-25

打赏
举报

to：
ChinaJiaBing
不对啊

declare @tbisnprocess table (indexid int,sn int,processid int,status int,date datetime)
insert into @tbisnprocess select 1,'200101',1,1,'2009-1-1'
union all select 2,'200101',2,0,'2009-1-2'
union all select 3,'200101',3,0,'2009-1-3'
union all select 4,'200101',0,0,'2009-1-3'
union all select 5,'200101',0,0,'2009-1-3'
union all select 1,'200102',1,1,'2009-1-1'
union all select 2,'200102',2,1,'2009-1-2'
union all select 3,'200102',3,1,'2009-1-3'
union all select 4,'200102',0,0,'2009-1-3'
union all select 5,'200102',0,0,'2009-1-3'
union all select 1,'200103',1,1,'2009-1-1'
union all select 2,'200103',2,0,'2009-1-2'
union all select 3,'200103',3,0,'2009-1-3'
union all select 4,'200103',0,0,'2009-1-3'
union all select 5,'200103',0,0,'2009-1-3'
declare @tblProcess table (processid int,processName nvarchar(100))
insert into @tblProcess select 4,'script'
union all select 1,'工序1'
union all select 2,'工序2'
union all select 3,'工序3'

结论应该是
----------- -----
200101 工序2
200102 完成
200103 工序2

GUOCHENGJUN 2010-03-24

打赏
举报



create table tblSNProcess(indexID int, SN int,ProcessID int,status int,date datetime)

insert tblSNProcess select 1, 200101, 1, 1, '2009-1-1'

insert tblSNProcess select 2, 200101, 2, -1, '2009-1-2'

insert tblSNProcess select 3, 200101, 3, 0 ,'2009-1-3'



create table tblProcess(ProcessID int,ProcessName varchar(10))

insert tblProcess select 1 ,'工序1'

insert tblProcess select 2 ,'工序2'

insert tblProcess select 3 ,'工序3'



select b.ProcessName,A.SN,

(select case when c.status=-1 then '维修'

 else rtrim(c.status) end 'status' 

from tblSNProcess c where C.ProcessID=A.ProcessID-1) AS DD

from tblSNProcess a left join tblProcess b on

a.ProcessID=b.ProcessID

WHERE A.STATUS=0

东那个升 2010-03-24

打赏
举报



select case when exists(select 1 from #tblSNProcess b where a.SN=b.SN and a.ProcessID=b.ProcessID+1 and b.status=-1) then '维修'

		else (select ProcessName from #tblProcess where a.ProcessID=ProcessID) end as 工序

from #tblSNProcess a 

where status=0 

	and not exists(select 1 from #tblSNProcess b where a.status=b.status and a.date<b.date) 



工序

----------

维修



(1 行受影响)

cngothic 2010-03-24

打赏
举报



select B.ProcessID from tblSNProcess A join tblProcess B on A.ProcessID = B.ProcessID where A.status = '0'

东那个升 2010-03-24

打赏
举报

create table #tblSNProcess(indexID int, SN int,ProcessID int,status int,date datetime)

insert #tblSNProcess select 1, 200101, 1, 1, '2009-1-1'

insert #tblSNProcess select 2, 200101, 2, -1, '2009-1-2'

insert #tblSNProcess select 3, 200101, 3, 0 ,'2009-1-3'



create table #tblProcess(ProcessID int,ProcessName varchar(10))

insert #tblProcess select 1 ,'工序1'

insert #tblProcess select 2 ,'工序2'

insert #tblProcess select 3 ,'工序3'







select case when exists(select 1 from #tblSNProcess b where a.SN=b.SN and a.ProcessID=b.ProcessID+1 and b.status=-1) then '维修'

		else (select ProcessName from #tblProcess where a.ProcessID=ProcessID) end as 工序

 from (

select *,row_number() over(partition by status order by  date desc) as rn from #tblSNProcess 

) a where status=0 and rn=1



工序

----------

维修



(1 行受影响)

✦双11狂欢大促✦

sql 查询工序

相关推荐

双11狂欢大促