存储过程执行报错：多次为 'a' 指定了列 '列名'

Db86 2020-08-23 11:42:29

以下是存储过程，麻烦大神给分析下哪里的原因
--------------------------------------------------
/****** Object: StoredProcedure [dbo].[P_JD_JGLDZ2] Script Date: 08/23/2020 11:32:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

---CREATE PROCEDURE [dbo].[P_JD_JGLDZ2] --创建
ALTER PROCEDURE [dbo].[P_JD_JGLDZ2] --更新
@FStartDate varchar(50)='20200601' ,
@FEndDate varchar(50)='20200701' ,
@kh varchar(50)='大荣'

AS
BEGIN

DECLARE @sql VARCHAR(8000)
DECLARE @COLUMN VARCHAR(8000)

SELECT
@sql= ISNULL(@sql+',','')+ '['+名称+']' ,
@COLUMN= ISNULL(@COLUMN,'')+', '+名称

FROM
(SELECT ' 期初余额' 日期,'' 摘要,t4.F_105 名称,CAST((SUM(case when T2.FTranType=137 then -1*t1.FAuxQty else t1.FAuxQty end)) as DECIMAL(13,2)) 数量
FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1.FInterID=T2.FInterID
INNER JOIN t_item t3 ON t1.FItemID=t3.FItemID
INNER JOIN t_ICItem T4 ON t1.FItemID=T4.FItemID
WHERE (t1.FDCStockID='27893' OR t1.FSCStockID='27893')
and t1.FBatchNo like '%'+@kh+'%' and t2.FDate < @FStartDate ----/*期初数据*/
GROUP BY t4.F_105
UNION all
SELECT CONVERT(varchar(100), t2.FDate, 23) 日期,T1.FNote 摘要,t4.F_105 名称,CAST((SUM(case when T2.FTranType=137 then -1*t1.FAuxQty else t1.FAuxQty end)) as DECIMAL(13,2)) 数量
FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1.FInterID=T2.FInterID
INNER JOIN t_item t3 ON t1.FItemID=t3.FItemID
INNER JOIN t_ICItem T4 ON t1.FItemID=T4.FItemID
WHERE (t1.FDCStockID='27893' OR t1.FSCStockID='27893')
and t1.FBatchNo like '%'+@kh+'%' and t2.FDate between @FStartDate and @FEndDate --/*发生数据*/
GROUP BY t4.F_105,t2.FDate,T1.FNote
UNION all
SELECT '期末余额' 日期,'' 摘要,t4.F_105 名称, CAST((SUM(case when T2.FTranType=137 then -1*t1.FAuxQty else t1.FAuxQty end)) as DECIMAL(13,2)) 数量
FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1.FInterID=T2.FInterID
INNER JOIN t_item t3 ON t1.FItemID=t3.FItemID
INNER JOIN t_ICItem T4 ON t1.FItemID=T4.FItemID
WHERE (t1.FDCStockID='27893' OR t1.FSCStockID='27893')
and t1.FBatchNo like '%'+@kh+'%' and t2.FDate <= @FEndDate
GROUP BY T4.F_105)

as cc
GROUP BY 名称,日期,摘要,数量
ORDER BY 名称

SET @sql= 'select * from
(SELECT '' 期初余额'' 日期,'''' 摘要,t4.F_105 名称,CAST((SUM(case when T2.FTranType=137 then -1*t1.FAuxQty else t1.FAuxQty end)) as DECIMAL(13,2)) 数量
FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1.FInterID=T2.FInterID
INNER JOIN t_item t3 ON t1.FItemID=t3.FItemID
INNER JOIN t_ICItem T4 ON t1.FItemID=T4.FItemID
WHERE (t1.FDCStockID=''27893'' OR t1.FSCStockID=''27893'')
and t1.FBatchNo like ''%'+@kh+'%'' and t2.FDate < '''+@FStartDate+''' ----/*期初数据*/
GROUP BY t4.F_105
UNION all
SELECT CONVERT(varchar(100), t2.FDate, 23) 日期,T1.FNote 摘要,t4.F_105 名称,CAST((SUM(case when T2.FTranType=137 then -1*t1.FAuxQty else t1.FAuxQty end)) as DECIMAL(13,2)) 数量
FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1.FInterID=T2.FInterID
INNER JOIN t_item t3 ON t1.FItemID=t3.FItemID
INNER JOIN t_ICItem T4 ON t1.FItemID=T4.FItemID
WHERE (t1.FDCStockID=''27893'' OR t1.FSCStockID=''27893'')
and t1.FBatchNo like ''%'+@kh+'%'' and t2.FDate between '''+@FStartDate+''' and '''+@FEndDate+''' --/*发生数据*/
GROUP BY t4.F_105,t2.FDate,T1.FNote
UNION all
SELECT ''期末余额'' 日期,'''' 摘要,t4.F_105 名称, CAST((SUM(case when T2.FTranType=137 then -1*t1.FAuxQty else t1.FAuxQty end)) as DECIMAL(13,2)) 数量
FROM ICSTJGBillEntry t1
INNER JOIN ICSTJGBill T2 ON T1.FInterID=T2.FInterID
INNER JOIN t_item t3 ON t1.FItemID=t3.FItemID
INNER JOIN t_ICItem T4 ON t1.FItemID=T4.FItemID
WHERE (t1.FDCStockID=''27893'' OR t1.FSCStockID=''27893'')
and t1.FBatchNo like ''%'+@kh+'%'' and t2.FDate <= '''+@FEndDate+'''
GROUP BY T4.F_105)
as dddd
pivot (max(数量) for 名称 in ('+@sql+')) a'

exec (@sql)
END

...全文