SQL帆软数据库查询，要用leftjoin连接case得出的结果，请问怎么写程序？

select

case when substr(t2.SFAA010,1,13) is NULL

then t0.SFAA010

ElSE substr(t2.SFAA010,1,13) END

as 母工单料号2

from DSDATA.SFAA_T t0

left join DSDATA.SFDB_T t3 on t3.SFDB001 = t0. 母工单料号2

请问应该如何写程序？

FineReport yzmECx4S7415548 发布于 2022-12-12 16:27

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共2回答

最佳回答

RiveryLv5中级互助
发布于2022-12-12 16:35（编辑于 2022-12-12 16:36）

这样再嵌套一层？你这个SQL不对，我大概改了一下，就是这个意思

select *

from (select case when substr(SFAA010,1,13) is NULL then SFAA010

ElSE substr(SFAA010,1,13) END as 母工单料号2

from DSDATA.SFAA_T

) t0

left join DSDATA.SFDB_T t3 on t3.SFDB001 = t0. 母工单料号2

举报收起评论(4)

yzmECx4S7415548(提问者) -- t0 = DSDATA.SFAA_T 工单数据库 -- t1 = DSDATA.IMAAL_T 物料信息数据库与生产料号关联，用于处理品名、规格和图号（取规格前13位） -- t2 = DSDATA.SFAA_T t2 工单数据库，用于处理工单、母工单、工单号、母工单号数据 -- t3 = DSDATA.SFDB_T t3 发料单明细，用于处理工单发料数量 -- t4 = 报工数据 select t0.SFAADOCNO as 工单号, t0.SFAA006 as 来源单号, t0.SFAA010 as 生产料号, t0.SFAA012 as 订单数, t0.SFAA049 as 已发料套数, t0.SFAA019 as 预计开工日期, t0.SFAA020 as 预计完工日期, t0.SFAA049 as 已发料套数, t0.SFAA050 as 已入库合格量, substr(t1.IMAAL004,1,13) as 工单图号, case when t0.SFAA003 = \'1\' then \'一般工单\' when t0.SFAA003 = \'2\' then \'返修工单\' else \'其他\' end as 工单类型, case when t0.SFAA057 = \'1\' then \'厂内\' when t0.SFAA057 = \'2\' then \'委外\' else \'其他\' end as 委外类型, case when t0.SFAASTUS = \'A\' then \'已核准\' when t0.SFAASTUS = \'C\' then \'结案\' when t0.SFAASTUS = \'D\' then \'抽单\' when t0.SFAASTUS = \'F\' then \'已发出\' when t0.SFAASTUS = \'M\' then \'成本结案\' when t0.SFAASTUS = \'N\' then \'未审核\' when t0.SFAASTUS = \'R\' then \'已拒绝\' when t0.SFAASTUS = \'W\' then \'送签中\' when t0.SFAASTUS = \'X\' then \'作废\' when t0.SFAASTUS = \'Y\' then \'已审核\' else \'其他\' end as 工单状态, case when t0.SFAA021 is NULL then t0.SFAADOCNO ELSE t0.SFAA021 END as 母工单号, case when substr(t2.SFAA010,1,13) is NULL then t0.SFAA010 ElSE substr(t2.SFAA010,1,13) END as 母工单料号2, t3.SFDB007 as 工单发料套数, t4.SFFB017 as 报工数量 from DSDATA.SFAA_T t0 left join DSDATA.IMAAL_T t1 on t1.IMAAL001 = t0.SFAA010 left join DSDATA.SFAA_T t2 on t2.SFAADOCNO = t0.SFAA021 left join DSDATA.SFDB_T t3 on t3.SFDB001 = t0.SFAADOCNO left join DSDATA.SFFB_T t4 on t4.SFFB005 = t0.SFAADOCNO and t4.SFFB017 not in 0 where t0.SFAASITE in \'ZZDJ\' 完整程序是这样，我想学习怎么让case的结果可以作为一列数据参与计算。我之前主要是用EXCEL，这种辅助列在EXCEL里面新建一列就行了，但是SQL怎么操作我就不知道了。

2022-12-12 17:05

Rivery 回复 yzmECx4S7415548(提问者) 你就case when的select外面嵌套一层select 作为一张新表就好啦

2022-12-12 17:07

yzmECx4S7415548(提问者) 我在网上找到的教程是： Select A from ( select AA from BB) 我需要达到的效果是 Select A from (select BB from CC)，B from（select CC from DD) 这个我就没找到相似的教程了。

2022-12-12 17:21

Rivery 回复 yzmECx4S7415548(提问者) 就是我写的哇 select * from (select case when。。。) t0

2022-12-12 17:46

Z4u3z1Lv6专家互助
发布于2022-12-12 16:28（编辑于 2022-12-12 17:28）

T2呢？

--------

SELECT T.*,T.母工单号||T.工单状态 FROM (

select t0.SFAADOCNO as 工单号, t0.SFAA006 as 来源单号, t0.SFAA010 as 生产料号, t0.SFAA012 as 订单数, t0.SFAA049 as 已发料套数, t0.SFAA019 as 预计开工日期, t0.SFAA020 as 预计完工日期, t0.SFAA049 as 已发料套数, t0.SFAA050 as 已入库合格量, substr(t1.IMAAL004,1,13) as 工单图号,

case when t0.SFAA003 = '1' then '一般工单' when t0.SFAA003 = '2' then '返修工单' else '其他' end as 工单类型,

case when t0.SFAA057 = '1' then '厂内' when t0.SFAA057 = '2' then '委外' else '其他' end as 委外类型,

case when t0.SFAASTUS = 'A' then '已核准' when t0.SFAASTUS = 'C' then '结案' when t0.SFAASTUS = 'D' then '抽单' when t0.SFAASTUS = 'F' then '已发出' when t0.SFAASTUS = 'M' then '成本结案' when t0.SFAASTUS = 'N' then '未审核' when t0.SFAASTUS = 'R' then '已拒绝' when t0.SFAASTUS = 'W' then '送签中' when t0.SFAASTUS = 'X' then '作废' when t0.SFAASTUS = 'Y' then '已审核' else '其他' end as 工单状态,

case when t0.SFAA021 is NULL then t0.SFAADOCNO ELSE t0.SFAA021 END as 母工单号,

case when substr(t2.SFAA010,1,13) is NULL then t0.SFAA010 ElSE substr(t2.SFAA010,1,13) END as 母工单料号2,

t3.SFDB007 as 工单发料套数, t4.SFFB017 as 报工数量 from DSDATA.SFAA_T t0

left join DSDATA.IMAAL_T t1 on t1.IMAAL001 = t0.SFAA010

left join DSDATA.SFAA_T t2 on t2.SFAADOCNO = t0.SFAA021

left join DSDATA.SFDB_T t3 on t3.SFDB001 = t0.SFAADOCNO

left join DSDATA.SFFB_T t4 on t4.SFFB005 = t0.SFAADOCNO and t4.SFFB017 not in 0

where t0.SFAASITE in 'ZZDJ'

) T