sql server 怎么根据部门过滤查询出所有的工序
create proc proc_test
@begTime varchar(100),
@endTime varchar(100),
@BM varchar(100)
as
select a.[FID],a.[FJHDate] as [计划时间],b.[FQty] as [周计划数量],b.[FZWMS] as [中文描述],c.[Forderinterid],
c.[Fsourceentryid],d.[FName] as 产品名称,convert(datetime, null) as [下单日期],convert(datetime, null) as [审核日期],
convert(varchar(100), null) as [销售单据编号],convert(varchar(100), null) as [装柜数量],convert(datetime, null) as [验货日期],
convert(datetime, null) as [装柜日期],convert(datetime, null) as [交货日期],SUBSTRING(e.[FNumber],0,2) as [部门],
if 部门=1
begin
max(case when a.[fdgx]=57412 then day(a.[FJHDate]) else ' ' end) as [裁剪],
max(case when a.[fdgx]=57413 then day(a.[FJHDate]) else ' ' end) as [车缝],
max(case when a.[fdgx]=57414 then day(a.[FJHDate]) else ' ' end) as [木工],
max(case when a.[fdgx]=57415 then day(a.[FJHDate]) else ' ' end) as [裁棉],
max(case when a.[fdgx]=57416 then day(a.[FJHDate]) else ' ' end) as [喷胶],
max(case when a.[fdgx]=57417 then day(a.[FJHDate]) else ' ' end) as [枪钉],
max(case when a.[fdgx]=57418 then day(a.[FJHDate]) else ' ' end) as [装配],
max(case when a.[fdgx]=57419 then day(a.[FJHDate]) else ' ' end) as [配料],
max(case when a.[fdgx]=57420 then day(a.[FJHDate]) else ' ' end) as [包装],
max(case when a.[fdgx]=57421 then day(a.[FJHDate]) else ' ' end) as [硬皮],
max(case when a.[fdgx]=57422 then day(a.[FJHDate]) else ' ' end) as [内销],
end
else if 部门=2
begin
max(case when a.[fdgx]=57424 then day(a.[FJHDate]) else ' ' end) as [车工],
max(case when a.[fdgx]=57425 then day(a.[FJHDate]) else ' ' end) as [枪工],
max(case when a.[fdgx]=57426 then day(a.[FJHDate]) else ' ' end) as [裁丝棉],
max(case when a.[fdgx]=57427 then day(a.[FJHDate]) else ' ' end) as [充棉],
max(case when a.[fdgx]=57428 then day(a.[FJHDate]) else ' ' end) as [裁棉],
max(case when a.[fdgx]=57429 then day(a.[FJHDate]) else ' ' end) as [胶棉],
max(case when a.[fdgx]=57430 then day(a.[FJHDate]) else ' ' end) as [裁皮],
max(case when a.[fdgx]=57431 then day(a.[FJHDate]) else ' ' end) as [木工],
max(case when a.[fdgx]=57432 then day(a.[FJHDate]) else ' ' end) as [组装],
max(case when a.[fdgx]=57433 then day(a.[FJHDate]) else ' ' end) as [包装],
end
else if 部门=3
begin
max(case when a.[fdgx]=57435 then day(a.[FJHDate]) else ' ' end) as [成品包装],
max(case when a.[fdgx]=57436 then day(a.[FJHDate]) else ' ' end) as [雕刻],
max(case when a.[fdgx]=57437 then day(a.[FJHDate]) else ' ' end) as [倒边],
max(case when a.[fdgx]=57438 then day(a.[FJHDate]) else ' ' end) as [打砂],
max(case when a.[fdgx]=57439 then day(a.[FJHDate]) else ' ' end) as [火焰抛光],
max(case when a.[fdgx]=57440 then day(a.[FJHDate]) else ' ' end) as [变形],
max(case when a.[fdgx]=57441 then day(a.[FJHDate]) else ' ' end) as [打孔],
max(case when a.[fdgx]=60082 then day(a.[FJHDate]) else ' ' end) as [成品抛光],
max(case when a.[fdgx]=60083 then day(a.[FJHDate]) else ' ' end) as [质检包装],
max(case when a.[fdgx]=60084 then day(a.[FJHDate]) else ' ' end) as [组装],
max(case when a.[fdgx]=60085 then day(a.[FJHDate]) else ' ' end) as [喷漆],
max(case when a.[fdgx]=60086 then day(a.[FJHDate]) else ' ' end) as [包装],
max(case when a.[fdgx]=60087 then day(a.[FJHDate]) else ' ' end) as [成品包装2],
end
else
begin
print '输入有误，请输入正确的部门[1，2，3]!'
end
into #T1
from U_JJ_ZGXJHEntry b
left join U_JJ_ZGXJH a on a.[FID] = b.[FID]
left join ICMO c on b.[FICMONo] = c.[FBillNo]
left join t_ICItemCore d on b.[FitemID] = d.[FitemID]
left join t_Item_3007 e on b.[FitemID] = e.[FitemID]
where a.[FJHDate] between @begTime and @endTime and 部门=@BM
group by a.[FID],a.[FJHDate],b.[FQty],b.[FZWMS],c.[Forderinterid],c.[Fsourceentryid],d.[FName]
GO
根据部门过滤，列出所有属于该部门的工序，要把工序列转行。求大神改造......
[解决办法]
--拼接字符串if OBJECT_ID('proc_test') is not null drop proc proc_testgocreate proc proc_test@begTime varchar(100),@endTime varchar(100),@BM varchar(100)asif (@BM<>'1') and (@BM<>'2') and (@BM<>'3') begin print('输入有误，请输入正确的部门[1，2，3]!') endelsebegindeclare @str nvarchar(max)=''set @str='select a.[FID],a.[FJHDate] as [计划时间],b.[FQty] as [周计划数量],b.[FZWMS] as [中文描述],c.[Forderinterid],'set @str+='c.[Fsourceentryid],d.[FName] as 产品名称,convert(datetime, null) as [下单日期],convert(datetime, null) as [审核日期],'set @str+='convert(varchar(100), null) as [销售单据编号],convert(varchar(100), null) as [装柜数量],convert(datetime, null) as [验货日期],'set @str+='convert(datetime, null) as [装柜日期],convert(datetime, null) as [交货日期],SUBSTRING(e.[FNumber],0,2) as [部门],'if @BM='1'beginset @str+='max(case when a.[fdgx]=57412 then day(a.[FJHDate]) else '' '' end) as [裁剪],'set @str+='max(case when a.[fdgx]=57413 then day(a.[FJHDate]) else '' '' end) as [车缝],'set @str+='max(case when a.[fdgx]=57414 then day(a.[FJHDate]) else '' '' end) as [木工],'set @str+='max(case when a.[fdgx]=57415 then day(a.[FJHDate]) else '' '' end) as [裁棉],'set @str+='max(case when a.[fdgx]=57416 then day(a.[FJHDate]) else '' '' end) as [喷胶],'set @str+='max(case when a.[fdgx]=57417 then day(a.[FJHDate]) else '' '' end) as [枪钉],'set @str+='max(case when a.[fdgx]=57418 then day(a.[FJHDate]) else '' '' end) as [装配],'set @str+='max(case when a.[fdgx]=57419 then day(a.[FJHDate]) else '' '' end) as [配料],'set @str+='max(case when a.[fdgx]=57420 then day(a.[FJHDate]) else '' '' end) as [包装],'set @str+='max(case when a.[fdgx]=57421 then day(a.[FJHDate]) else '' '' end) as [硬皮],'set @str+='max(case when a.[fdgx]=57422 then day(a.[FJHDate]) else '' '' end) as [内销]'endelse if @BM='2'beginset @str+='max(case when a.[fdgx]=57424 then day(a.[FJHDate]) else '' '' end) as [车工],'set @str+='max(case when a.[fdgx]=57425 then day(a.[FJHDate]) else '' '' end) as [枪工],'set @str+='max(case when a.[fdgx]=57426 then day(a.[FJHDate]) else '' '' end) as [裁丝棉],'set @str+='max(case when a.[fdgx]=57427 then day(a.[FJHDate]) else '' '' end) as [充棉],'set @str+='max(case when a.[fdgx]=57428 then day(a.[FJHDate]) else '' '' end) as [裁棉],'set @str+='max(case when a.[fdgx]=57429 then day(a.[FJHDate]) else '' '' end) as [胶棉],'set @str+='max(case when a.[fdgx]=57430 then day(a.[FJHDate]) else '' '' end) as [裁皮],'set @str+='max(case when a.[fdgx]=57431 then day(a.[FJHDate]) else '' '' end) as [木工],'set @str+='max(case when a.[fdgx]=57432 then day(a.[FJHDate]) else '' '' end) as [组装],'set @str+='max(case when a.[fdgx]=57433 then day(a.[FJHDate]) else '' '' end) as [包装]'endelse if @BM='3'beginset @str+='max(case when a.[fdgx]=57435 then day(a.[FJHDate]) else '' '' end) as [成品包装],'set @str+='max(case when a.[fdgx]=57436 then day(a.[FJHDate]) else '' '' end) as [雕刻],'set @str+='max(case when a.[fdgx]=57437 then day(a.[FJHDate]) else '' '' end) as [倒边],'set @str+='max(case when a.[fdgx]=57438 then day(a.[FJHDate]) else '' '' end) as [打砂],'set @str+='max(case when a.[fdgx]=57439 then day(a.[FJHDate]) else '' '' end) as [火焰抛光],'set @str+='max(case when a.[fdgx]=57440 then day(a.[FJHDate]) else '' '' end) as [变形],'set @str+='max(case when a.[fdgx]=57441 then day(a.[FJHDate]) else '' '' end) as [打孔],'set @str+='max(case when a.[fdgx]=60082 then day(a.[FJHDate]) else '' '' end) as [成品抛光],'set @str+='max(case when a.[fdgx]=60083 then day(a.[FJHDate]) else '' '' end) as [质检包装],'set @str+='max(case when a.[fdgx]=60084 then day(a.[FJHDate]) else '' '' end) as [组装],'set @str+='max(case when a.[fdgx]=60085 then day(a.[FJHDate]) else '' '' end) as [喷漆],'set @str+='max(case when a.[fdgx]=60086 then day(a.[FJHDate]) else '' '' end) as [包装],'set @str+='max(case when a.[fdgx]=60087 then day(a.[FJHDate]) else '' '' end) as [成品包装2]'endset @str+='into #T1 'set @str+='from U_JJ_ZGXJHEntry b 'set @str+='inner join U_JJ_ZGXJH a on a.[FID] = b.[FID] 'set @str+='inner join ICMO c on b.[FICMONo] = c.[FBillNo] 'set @str+='inner join t_ICItemCore d on b.[FitemID] = d.[FitemID] 'set @str+='inner join t_Item_3007 e on b.[FitemID] = e.[FitemID] 'set @str+='where a.[FJHDate] between @begTime and @endTime and 部门=@BM 'set @str+='group by a.[FID],a.[FJHDate],b.[FQty],b.[FZWMS],c.[Forderinterid],c.[Fsourceentryid],d.[FName] 'print(@str)--输出exec(@str)--执行endGO
[解决办法]
if @BM='1'
写一个语句
if @BM='2'
写一个语句
这样的你好调试
[解决办法]