以下是存盤程序，麻煩大神給分析下哪里的原因
--------------------------------------------------
/****** 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
uj5u.com熱心網友回復：
SELECT@sql= ISNULL(@sql+',','')+ '['+名稱+']' ,估計是這里的'名稱'有重復了。
你用‘GROUP BY 名稱,日期,摘要,數量’這樣分組來獲得名稱，名稱難免會有重復，為啥不直接用GROUP BY 名稱。
uj5u.com熱心網友回復：
感謝大神指點，確實是這里出了問題
uj5u.com熱心網友回復：
在列前面加一個表別名呢？轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/11396.html
標籤：應用實例
上一篇：spss是啥