问题遇到的现象和发生背景
问题相关代码，请勿粘贴截图
DECLARE
@OperatorPointCode NVARCHAR(32) = 'JCFT' ,
@OperatorCode NVARCHAR(32) = 'JCFT' ,
@PointCode NVARCHAR(32) = 'JCFT' ,
@BeginDT DATETIME='2022-3-1',
@EndDT DATETIME=NULL,
@SearchFlag INT = 0
--WITH ENCRYPTION
--AS
BEGIN
IF @PointCode IS NULL OR @PointCode = ''
SET @PointCode = @OperatorPointCode
DECLARE @PayPrice DECIMAL(15,2)
IF @BeginDT IS NULL
SET @BeginDT = '1900-01-01'
SET @BeginDT = CONVERT(NCHAR(11),@BeginDT ,120) + '00:00:00'
IF @EndDT IS NULL
SET @EndDT = GETDATE()
SET @EndDT = CONVERT(NCHAR(11),@EndDT ,120) + ' 23:59:59:998'
SELECT
ROW_NUMBER() OVER(ORDER BY TT.[收款单号]) AS 序号,
TT.[网点号] AS 网点号,
TT.[收款单号] AS 收款单号,
TT.[业务单号] AS 业务单号,
TT.[网点名称] AS 网点名称,
TT.[业务类型] AS 业务类型,
TT.[收款日期] AS 收款日期,
TT.[收款金额] AS 收款金额,
TT.[支付方式] AS 支付方式,
TT.[支付金额] AS 支付金额
INTO #PayDetail
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY T.[收款单号]) AS 序号,
T.[网点号] AS 网点号,
T.[收款单号] AS 收款单号,
T.[业务单号] AS 业务单号,
T.[网点名称] AS 网点名称,
T.[业务类型] AS 业务类型,
T.[收款日期] AS 收款日期,
T.[收款金额] AS 收款金额,
T.[支付方式] AS 支付方式,
T.[支付金额] AS 支付金额
FROM
(
--售后维修
SELECT
T1.PointCode AS 网点号,
B.PayBillCode AS 收款单号,
T1.RepairBillCode AS 业务单号,
T_PointInfo.PointName AS 网点名称,
'售后维修' 业务类型,
CONVERT(NVARCHAR(10),ISNULL(B.BusinessDT,T2.BalanceDateTime),120) AS 收款日期,
round(ISNULL(T1.ActualMoney,0),2) AS 收款金额,
CASE WHEN ISNULL(T_TY_PayMentDetail.PayWay,'') = '' THEN '其他' ELSE ISNULL(T_TY_PayMentDetail.PayWay,'') END AS 支付方式,
CAST (ISNULL(T_TY_PayMentDetail.PayBalance,0) AS NUMERIC(15,5)) AS 支付金额
FROM T_RepairCharge T1 WITH (NOLOCK)
LEFT JOIN T_RepairBill T2 WITH(NOLOCK) ON (T1.PointCode = T2.PointCode AND T1.RepairBillCode = T2.RepairBillCode)
LEFT JOIN T_PointInfo WITH(NOLOCK) ON (T_PointInfo.PointCode = T1.PointCode)
LEFT JOIN (SELECT * FROM T_TY_PayMent WITH (NOLOCK) WHERE BillType='RepairBillPay') B ON T1.PointCode=B.PointCode AND T1.ChargeBillCode= B.BillCode
LEFT JOIN T_TY_PayMentDetail WITH (NOLOCK) ON B.PointCode=T_TY_PayMentDetail.PointCode AND B.BillCode=T_TY_PayMentDetail.BillCode AND B.BillType = T_TY_PayMentDetail.BillType
WHERE (T1.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection(@PointCode, 1, 1, -1 * ISNULL(@SearchFlag, 0), 1)))
AND (ISNULL(B.BusinessDT,T2.BalanceDateTime) BETWEEN @BeginDT AND @EndDT)
UNION
--备件用品
SELECT
--ROW_NUMBER() OVER(ORDER BY S1.SerialNo) 序号,
S1.PointCode AS 网点号,
B.PayBillCode AS 收款单号,
S1.SerialNo AS 业务单号,
T_PointInfo.PointName AS 网点名称,
'备件/用品销售' 业务类型,
CONVERT(NVARCHAR(10),ISNULL(B.BusinessDT,S1.PayDT),120) AS 收款日期,
CAST (ISNULL(S1.PayBalance,B.PayBalance) AS NUMERIC(15,5))AS 收款金额,
CASE WHEN ISNULL(T_TY_PayMentDetail.PayWay,'') = '' THEN '其他' ELSE ISNULL(T_TY_PayMentDetail.PayWay,'') END AS 支付方式,
CAST (ISNULL(T_TY_PayMentDetail.PayBalance,0) AS NUMERIC(15,5)) AS 支付金额
FROM T_SparepartsPaymentDetail S1 WITH (NOLOCK)
LEFT JOIN T_PointInfo WITH(NOLOCK) ON (T_PointInfo.PointCode = S1.PointCode)
LEFT JOIN (SELECT * FROM T_TY_PayMent WITH (NOLOCK) WHERE BillType='SparepartsPay') B ON S1.PointCode=B.PointCode AND S1.SerialNo= B.BillCode
LEFT JOIN T_TY_PayMentDetail WITH (NOLOCK) ON B.PointCode=T_TY_PayMentDetail.PointCode AND B.BillCode=T_TY_PayMentDetail.BillCode AND B.BillType = T_TY_PayMentDetail.BillType
WHERE (S1.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection(@PointCode, 1, 1, -1 * ISNULL(@SearchFlag, 0), 1)))
AND (ISNULL(B.BusinessDT,S1.PayDT) BETWEEN @BeginDT AND @EndDT)
UNION
--新车订单
SELECT
--ROW_NUMBER() OVER(ORDER BY C1.DestineBillCode) 序号,
C1.PointCode AS 网点号,
B.PayBillCode AS 收款单号,
C1.DestineBillCode AS 业务单号,
T_PointInfo.PointName AS 网点名称,
'新车订单' 业务类型,
CONVERT(NVARCHAR(10),ISNULL(Bdmin.BusinessDT,C1.BusinessDT),120) AS 收款日期,
CAST (ISNULL(C1.PayedeDestineMoney,Bdmin.PayBalance) AS NUMERIC(15,5))AS 收款金额,
CASE WHEN ISNULL(T_TY_PayMentDetail.PayWay,'') = '' THEN '其他' ELSE ISNULL(T_TY_PayMentDetail.PayWay,'') END AS 支付方式,
CAST (ISNULL(Bdmin.PayBalance,0) AS NUMERIC(15,5)) AS 支付金额
FROM T_CS_CarSaleDestine C1 WITH (NOLOCK)
LEFT JOIN T_PointInfo WITH(NOLOCK) ON (T_PointInfo.PointCode = C1.PointCode)
LEFT JOIN
(SELECT PointCode,BillCode,BillType, MIN(PayBillCode) AS PayBillCode
FROM T_TY_PayMent
WITH (NOLOCK) WHERE BillType='CarDestine'
GROUP BY PointCode,BillCode,BillType
) B ON C1.PointCode=B.PointCode AND C1.DestineBillCode= B.BillCode
LEFT JOIN T_TY_PayMent Bdmin ON Bdmin.PointCode=B.PointCode AND Bdmin.BillCode=B.BillCode AND Bdmin.PayBillCode=B.PayBillCode
LEFT JOIN T_TY_PayMentDetail WITH (NOLOCK) ON B.PointCode=T_TY_PayMentDetail.PointCode AND B.BillCode=T_TY_PayMentDetail.BillCode AND B.BillType = T_TY_PayMentDetail.BillType
WHERE (C1.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection(@PointCode, 1, 1, -1 * ISNULL(@SearchFlag, 0), 1)))
AND (ISNULL(Bdmin.BusinessDT,C1.BusinessDT) BETWEEN @BeginDT AND @EndDT)
UNION
--整车销售
SELECT
C2.PointCode AS 网点号,
B.PayBillCode AS 收款单号,
CS_T.SaleBillCode AS 业务单号,
T_PointInfo.PointName AS 网点名称,
'整车销售' 业务类型,
CONVERT(NVARCHAR(10),ISNULL(Bmin.BusinessDT,CS_T.BalanceDT),120) AS 收款日期,
CAST (ISNULL(Bmin.PayBalance,CS_T.PayedSaleTotalSale) AS NUMERIC(15,5))AS 收款金额,
CASE WHEN ISNULL(C2.PayWay,'') = '' THEN '其他' ELSE ISNULL(C2.PayWay,'') END AS 支付方式,
CAST (ISNULL(Bmin.PayBalance,C2.PayBalance) AS NUMERIC(15,5)) AS 支付金额
FROM T_TY_PayMentDetail C2 WITH (NOLOCK)
LEFT JOIN T_PointInfo WITH(NOLOCK) ON (T_PointInfo.PointCode = C2.PointCode)
LEFT JOIN
(SELECT PointCode,BillCode,BillType, MIN(PayBillCode) AS PayBillCode
FROM T_TY_PayMent
WITH (NOLOCK) WHERE BillType='CarSales'
GROUP BY PointCode,BillCode,BillType
)AS B ON C2.PointCode=B.PointCode AND C2.BillCode= B.BillCode AND B.BillType = C2.BillType
LEFT JOIN T_TY_PayMent Bmin ON Bmin.PointCode=B.PointCode AND Bmin.BillCode=B.BillCode AND Bmin.PayBillCode=B.PayBillCode
LEFT JOIN T_CS_CarSaleBillDetail CS_T WITH (NOLOCK) ON C2.PointCode=CS_T.PointCode AND C2.BillCode=CS_T.SaleBillCode
LEFT JOIN T_CS_CarSaleBill CS_T1 WITH (NOLOCK) ON CS_T1.PointCode=CS_T.PointCode AND CS_T1.SaleBillCode=CS_T.SaleBillCode
LEFT JOIN (SELECT PointCode,BillType,BillCode,MAX(Status) Status
FROM T_TY_OperateStatusSummaryInfo WITH(NOLOCK)
WHERE BillType='CarSales'
GROUP BY PointCode,BillType,BillCode) CS_T2 ON CS_T1.PointCode=CS_T2.PointCode AND CS_T1.SaleBillCode=CS_T2.BillCode
LEFT JOIN T_TY_StatusChangeInfo CS_T3 WITH(NOLOCK) ON CS_T2.BillType=CS_T3.BillType AND CS_T2.Status=CS_T3.Status
WHERE (C2.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection(@PointCode, 1, 1, -1 * ISNULL(@SearchFlag, 0), 1)))
AND (ISNULL(CS_T1.Status,0) >= 1 AND ISNULL(CS_T3.StatusDescription,'')='财务收款' AND C2.BillType='CarSales')
AND (ISNULL(CS_T.BalanceDT,Bmin.BusinessDT) BETWEEN @BeginDT AND @EndDT)
UNION
--客户预收款
SELECT
FI.PointCode AS 网点号,
B.PayBillCode AS 收款单号,
FI.CustomerID AS 业务单号,
T_PointInfo.PointName AS 网点名称,
'客户预存款' 业务类型,
CONVERT(NVARCHAR(10),B.BusinessDT,120) AS 收款日期,
CAST (ISNULL(B.PayBalance,FI.NoInvoiceBalance) AS NUMERIC(15,5))AS 收款金额,
CASE WHEN ISNULL(T_TY_PayMentDetail.PayWay,'') = '' THEN '其他' ELSE ISNULL(T_TY_PayMentDetail.PayWay,'') END AS 支付方式,
CAST (ISNULL(B.PayBalance,0) AS NUMERIC(15,5)) AS 支付金额
FROM T_FI_PointCustomerAccountInfo FI WITH (NOLOCK)
LEFT JOIN T_PointInfo WITH(NOLOCK) ON (T_PointInfo.PointCode = FI.PointCode)
LEFT JOIN T_FinanceCashAcount TF WITH (NOLOCK) ON FI.PointCode=TF.PointCode AND FI.CustomerID=TF.CustomerID
LEFT JOIN (SELECT * FROM T_TY_PayMent WITH (NOLOCK) WHERE BillType='RelieveType') B ON TF.PointCode=B.PointCode AND TF.BillCode= B.BillCode
LEFT JOIN T_TY_PayMentDetail WITH (NOLOCK) ON B.PointCode=T_TY_PayMentDetail.PointCode AND B.BillCode=T_TY_PayMentDetail.BillCode AND B.BillType = T_TY_PayMentDetail.BillType
WHERE (FI.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection(@PointCode, 1, 1, -1 * ISNULL(@SearchFlag, 0), 1)))
AND (B.BusinessDT BETWEEN @BeginDT AND @EndDT)
UNION
--电子券
SELECT
T_Pay.PointCode AS 网点号,
T_Pay.PayBillCode AS 收款单号,
T_Coupon.VolumeListNo AS 业务单号,
T_PointInfo.PointName AS 网点名称,
'电子券收款' 业务类型,
CONVERT(NVARCHAR(10),ISNULL(T_Pay.BusinessDT,T_Coupon.PayDT),120) AS 收款日期,
CAST (ISNULL(T_Pay.PayBalance,T_Coupon.PayBalance) AS NUMERIC(15,5))AS 收款金额,
CASE WHEN ISNULL(T_TY_PayMentDetail.PayWay,'') = '' THEN '其他' ELSE ISNULL(T_TY_PayMentDetail.PayWay,'') END AS 支付方式,
CAST (ISNULL(T_Pay.PayBalance,0) AS NUMERIC(15,5)) AS 支付金额
FROM T_TY_PayMent T_Pay WITH (NOLOCK)
LEFT JOIN T_TY_PayMentDetail WITH (NOLOCK) ON T_Pay.PointCode=T_TY_PayMentDetail.PointCode AND T_Pay.BillCode=T_TY_PayMentDetail.BillCode AND T_Pay.BillType = T_TY_PayMentDetail.BillType
LEFT JOIN T_PointInfo WITH(NOLOCK) ON (T_PointInfo.PointCode = T_Pay.PointCode)
LEFT JOIN T_RepairCouponInfo T_Coupon WITH(NOLOCK) ON (T_Coupon.PointCode = T_Pay.PointCode AND T_Coupon.SerialNo=T_Pay.BillCode)
WHERE (T_Pay.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection(@PointCode, 1, 1, -1 * ISNULL(@SearchFlag, 0), 1)))
AND (T_Pay.BillType='RepairCouponPay')
AND (ISNULL(T_Pay.BusinessDT,T_Coupon.PayDT) BETWEEN @BeginDT AND @EndDT)
)AS T
GROUP BY
T.网点号,
T.[收款单号] ,
T.[业务单号] ,
T.[网点名称],
T.[业务类型] ,
T.[收款日期],
T.[收款金额] ,
T.[支付方式],
T.[支付金额]
)AS TT
SELECT
[收款金额]
INTO #SumPrice
FROM #PayDetail
GROUP BY [收款金额]
SELECT @PayPrice=SUM([收款金额]) FROM #SumPrice
SELECT *
FROM #PayDetail
UNION ALL
SELECT NULL 序号,
NULL AS 网点号,
NULL AS 收款单号,
NULL AS 业务单号,
NULL AS 网点名称,
NULL AS 业务类型,
NULL AS 收款日期,
@PayPrice AS 收款金额,
[支付方式],
SUM([支付金额])
FROM #PayDetail
GROUP BY [支付方式]
DROP TABLE #SumPrice
DROP TABLE #PayDetail
END
运行结果及报错内容
我的解答思路和尝试过的方法
使用网点号进行分组，但是 有一些列是必须的，我不知道怎么分组了。
我想要达到的结果
只显示一行数据，并且这些金额都是合计起来的,也就是只有第三行的数据。
