求帮忙改一句SQL

select c.ITEM供应商合并, c.ITEM, c.物料说明, c.SQE, c.供应商, c.PO,c.time

from (select *

from dbo.活动区间ITEM a

where convert(varchar(10), time, 120) >= '${p3}'

and convert(varchar(10), time, 120) <= '${p4}'

and a.ITEM in (select ITEM

from dbo.活动区间ITEM

where convert(varchar(10), time, 120) >= '${p1}'

and convert(varchar(10), time, 120) <= '${p2}'

${if(p5="全部","",if(p5="其他","and left(ITEM,2) NOT IN ('AA' ,'AB', 'AC' ,'AD','AE')","and left(ITEM,2)='"+p5+"'"))})

${if(p5="全部","",if(p5="其他","and left(ITEM,2) NOT IN ('AA' ,'AB', 'AC' ,'AD','AE')","and left(ITEM,2)='"+p5+"'"))}${if(SQE="全部","","and SQE ='"+SQE+"'")}) c

where c.ITEM供应商合并 not in (select 活动区间ITEM.ITEM供应商合并

from dbo.活动区间ITEM

where convert(varchar(10), time, 120) >= '${p1}'

and convert(varchar(10), time, 120) <= '${p2}')

group by c.ITEM供应商合并, c.ITEM, c.物料说明, c.SQE, c.供应商, c.PO,c.time

需要新增一列优选供应商从dbo.活动区间ITEM里查询原来的意思是区分这个ITEM之前存在的现在供应商不同了现在同时需要查到原来的供应商条件是convert(varchar(10), time, 120) = '2021-12-31'

sqlserver服务器请问怎么修改

callie 发布于 2022-5-15 13:23 （编辑于 2022-5-15 18:50）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：5 F币 4人赏过› + 添加悬赏

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

共3回答

最佳回答

callieLv4见习互助
发布于2022-5-16 10:20

select aa.*, bb.供应商 as 优选供应商

from (select c.ITEM供应商合并

, c.ITEM

, c.物料说明

, c.SQE

, c.供应商

, c.PO

, c.time

from (select *

from dbo.活动区间ITEM a

where convert(varchar(10), time, 120) >= '2022-01-01'

and convert(varchar(10), time, 120) <= '2022-05-16'

and a.ITEM in (select ITEM

from dbo.活动区间ITEM

where convert(varchar(10), time, 120) >= '2021-01-01'

and convert(varchar(10), time, 120) <= '2021-12-31'

${if(p5="全部","",if(p5="其他","and left(ITEM,2) NOT IN ('AA' ,'AB', 'AC' ,'AD','AE')","and left(ITEM,2)='"+p5+"'"))}

)

${if(p5="全部","",if(p5="其他","and left(ITEM,2) NOT IN ('AA' ,'AB', 'AC' ,'AD','AE')","and left(ITEM,2)='"+p5+"'"))}${if(SQE="全部","","and SQE ='"+SQE+"'")}

) c

where c.ITEM供应商合并 not in (select 活动区间ITEM.ITEM供应商合并

from dbo.活动区间ITEM

where convert(varchar(10), time, 120) >= '2021-01-01'

and convert(varchar(10), time, 120) <= '2021-12-31')) aa

left join (select ITEM, a.供应商

from dbo.活动区间ITEM a

where convert(varchar(10), time, 120) >= '2021-01-01'

and convert(varchar(10), time, 120) <= '2021-12-31') bb on aa.ITEM = bb.ITEM;

用户a8068717Lv4初级互助
发布于2022-5-15 13:35（编辑于 2022-5-15 13:38）

with a (

select c.ITEM供应商合并, c.ITEM, c.物料说明, c.SQE, c.供应商, c.PO,c.time

,case when convert(varchar(10), c.time, 120) = '2021-12-31' then c.供应商 end as 优选供应商

from (select *

from dbo.活动区间ITEM a

where convert(varchar(10), time, 120) >= '${p3}'

and convert(varchar(10), time, 120) <= '${p4}'

and a.ITEM in (select ITEM

from dbo.活动区间ITEM

where convert(varchar(10), time, 120) >= '${p1}'

and convert(varchar(10), time, 120) <= '${p2}'

${if(p5="全部","",if(p5="其他","and left(ITEM,2) NOT IN ('AA' ,'AB', 'AC' ,'AD','AE')","and left(ITEM,2)='"+p5+"'"))})

${if(p5="全部","",if(p5="其他","and left(ITEM,2) NOT IN ('AA' ,'AB', 'AC' ,'AD','AE')","and left(ITEM,2)='"+p5+"'"))}${if(SQE="全部","","and SQE ='"+SQE+"'")}) c

where c.ITEM供应商合并 not in (select 活动区间ITEM.ITEM供应商合并

from dbo.活动区间ITEM

where convert(varchar(10), time, 120) >= '${p1}'

and convert(varchar(10), time, 120) <= '${p2}')

)

select ITEM供应商合并, ITEM, 物料说明, SQE, 供应商, PO,time,优选供应商

from a

group by ITEM供应商合并, ITEM, 物料说明, SQE, 供应商, PO,time,优选供应商