SQL SERVER 存储过程查询出来的数据为一行合计不需要进行分组,如何进行调整。

问题遇到的现象和发生背景

问题相关代码，请勿粘贴截图

DECLARE
@OperatorPointCode    NVARCHAR(32) = 'JCFT' ,
@OperatorCode        NVARCHAR(32) = 'JCFT' ,
@PointCode            NVARCHAR(32) = 'JCFT' ,
@BeginDT         DATETIME='2022-2-25',
@EndDT         DATETIME=NULL,
@SearchFlag            INT = 0
BEGIN

    IF @PointCode IS NULL OR @PointCode = ''
    SET @PointCode = @OperatorPointCode
    
    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 T.[收款单号]) AS 序号,
    T.[网点号] AS 网点号,
    T.[收款单号] AS 收款单号,
    T.[业务单号] AS 业务单号,
    T.[网点名称] AS 网点名称,
    T.[业务类型] AS 业务类型,
    T.[收款日期] AS 收款日期,
    CAST (T.[收款金额] AS NUMERIC(15,2))  AS 收款金额,
    T.[支付方式] AS 支付方式,
    CAST(T.[支付金额] AS NUMERIC(15,2)) AS 支付金额
    INTO #PayDetail 
    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
        LEFT JOIN
        (SELECT 
          ROW_NUMBER() OVER(ORDER BY T1.SerialNo) 序号,T1.PointCode,T1.SerialNo
          FROM T_SparepartsPaymentDetail T1 WITH (NOLOCK)
          LEFT JOIN (SELECT * FROM T_TY_PayMent WITH (NOLOCK) WHERE BillType='SparepartsPay') B ON T1.PointCode=B.PointCode AND T1.SerialNo= B.BillCode
          WHERE (T1.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection(@PointCode, 1, 1, -1 * ISNULL(@SearchFlag, 0), 1))) 
          AND (B.BusinessDT BETWEEN @BeginDT AND @EndDT) 
        )AS T_Lincode ON S1.PointCode=T_Lincode.PointCode AND S1.SerialNo=T_Lincode.SerialNo
        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(B.BusinessDT,C1.BusinessDT),120)  AS 收款日期,
          CAST (ISNULL(C1.PayedeDestineMoney,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_CS_CarSaleDestine C1 WITH (NOLOCK)
          LEFT JOIN T_PointInfo  WITH(NOLOCK)   ON (T_PointInfo.PointCode = C1.PointCode) 
          LEFT JOIN (SELECT * FROM T_TY_PayMent WITH (NOLOCK) WHERE BillType='CarDestine') B ON C1.PointCode=B.PointCode AND C1.DestineBillCode= 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 (C1.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection(@PointCode, 1, 1, -1 * ISNULL(@SearchFlag, 0), 1))) 
          AND (ISNULL(B.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(B.BusinessDT,CS_T.BalanceDT),120)  AS 收款日期,
          CAST (ISNULL(B.PayBalance,CS_T.PayedSaleTotalSale) AS  NUMERIC(15,5))AS 收款金额,
          CASE WHEN ISNULL(C2.PayWay,'') = '' THEN '其他' ELSE ISNULL(C2.PayWay,'') END AS 支付方式,
          CAST (ISNULL(C2.PayBalance,0) 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 * FROM T_TY_PayMent WITH (NOLOCK) WHERE BillType='CarSales') B ON C2.PointCode=B.PointCode AND C2.BillCode= B.BillCode AND B.BillType = C2.BillType
          LEFT JOIN T_CS_CarSaleBillDetail CS_T WITH (NOLOCK) ON C2.PointCode=CS_T.PointCode AND C2.BillCode=CS_T.SaleBillCode
          WHERE (C2.PointCode IN (SELECT PointCode FROM DBO.F_GetRelationPointCollection(@PointCode, 1, 1, -1 * ISNULL(@SearchFlag, 0), 1))) 
          AND (ISNULL(CS_T.BalanceDT,B.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.[支付金额]
    
    SELECT * 
    FROM #PayDetail
    UNION ALL
        SELECT NULL 序号,
          NULL AS 网点号,
          NULL AS 收款单号,
          NULL AS 业务单号,
          NULL  AS 网点名称,
          NULL AS 业务类型,
          NULL  AS 收款日期,
          SUM([收款金额]),
          [支付方式],
          SUM([支付金额])
        FROM #PayDetail
        GROUP BY [支付方式]

        
        DROP TABLE #PayDetail
END

运行结果及报错内容

我的解答思路和尝试过的方法

使用临时表写入，未能达到效果。

我想要达到的结果

合计为一行数据，不需要进行分组显示。

写回答
好问题 0 提建议
追加酬金
关注问题
分享
邀请回答
编辑收藏删除
收藏举报

1条回答默认最新

关注

码龄粉丝数原力等级 --

被采纳

被点赞

采纳率
DarkAthena ORACLE应用及数据库设计方案咨询师 2022-02-26 11:25
关注
把最下面那段select改成这样

SELECT NULL 序号, NULL AS 网点号, NULL AS 收款单号, NULL AS 业务单号, NULL AS 网点名称, NULL AS 业务类型, NULL AS 收款日期, SUM([收款金额]), null as 支付方式, SUM([支付金额]) FROM #PayDetail
本回答被题主选为最佳回答 , 对您是否有帮助呢?

解决无用
评论打赏
分享
举报

评论

按下Enter换行，Ctrl+Enter发表内容

报告相同问题？

关注问题

sqlserver 查询相同数据只取第一条 sql 有问必答
2022-09-29 19:43

回答 2 已采纳用distinct对查询的字段去重。 select distinct wareid from u_sale_c where wareid in (select b.wareid from u_ware
sqlserver 如何查询的同时并删除这行 sqlserver 数据库开发
2022-04-21 12:13

回答 2 已采纳查询是为了输出数据，如果只是为了删除，那么你加where条件直接删除这条数据就行了，没必要再去查询它
SQL SERVER 用多条件如何进行批量查询?请专家答疑解惑 sql
2022-01-19 22:57

回答 7 已采纳查询的字段DQ_RpNr1,DQ_RpNr2,DQ_RpNr3,DQ_RpNr4可以动态生成，现在示例中使用CET表只能查询一次，改成实体表、表变量或者临时表都可以生成，组成动态SQL再exec就可以
Sql server 千万级大数据SQL查询优化
2020-02-29 14:25

卖裤子上网的博客 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id ...
SqlServer 2008 R2 如何多行数据输出为一行 sql 数据库
2015-09-01 03:08

回答 2 已采纳使用xml path ``` select name,adminid,(select Content from 资源跟踪表 where adminid=资源表.adminid from FO
（SQL SERVER存储过程）将查询的数据放到另外一张表的字段里，仅此而已 sql
2017-12-10 06:07

回答 4 已采纳 select ISNULL(T1.月份,T2.月份) as 月份, 装配总数,合同金额,装配成本,装配利润,装配利润率, 销售台数,开票金额,销售成本,销售利润,销售利润率 FROM
SQL SERVER 怎么将多行多列数据合并为一行 sql
2020-01-02 14:20

回答 1 已采纳就是想同一商品同一颜色的数量,做个合计展示吧,方便统计库存 ``` select * from goods select GoodsNumber,GoodsName,Color,sum(S)
Sql server 千万级大数据SQL查询优化的几点建议
2022-01-18 09:36

qhdzj87的博客 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id ...
SQL server 对数据进行拆分 sql sqlserver
2022-05-31 08:51

回答 3 已采纳一个简单的with递归就行了 create table test_t (ItmSpec varchar(10),Qty int); insert into test_t values ('Spec1'
【SQL】怎么在对已分组数据进行总合计并在列中展示 sql
2022-01-19 14:16

回答 3 已采纳提供一个思路，先获得下面的结果集等级总计总占比A 37389 1.40%B 37389 1.40%C 37389 1.40%D 37389 1.40%在left join on a.等级 =
sql server 批量循环更新同一个表里数据 sql 数据库
2022-11-07 18:47

回答 8 已采纳重点在于从分区第一条数据开始计算对吧，然后就是更新，没得SqlServer以mysql为例吧，思路是分区递归计算 CREATE TABLE t_example ( f_year INT NU
SQL Server 处理百万级以上的数据处理与优化
2019-07-19 17:34

豆皮没有豆的博客一.处理百万级以上的数据提高查询速度的方法： 1.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 2.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 ...
sql server 怎么查询一个时间段内每个小时内的最新一条数据? c# java sql 有问必答
2021-06-21 09:46

回答 1 已采纳使用row_number 分组排序，partition by date,hour order by time desc，去分组编号为1的记录即可
SQL 大数据查询如何进行优化？
2020-03-06 00:21

霍先生的虚拟宇宙网络的博客 1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索来看一个Sql: 首先考虑下，如果这个表有100条数据，200条，2万。这样也许没有问题。但是如果 20万，200万，两千万...
SQL 大数据查询如何进行优化？sqlserver和oracle整理
2017-03-30 11:58

云草桑的博客六十多条大数据优化建议。涉及到sqlserver和oracle的SQL语句。
没有解决我的问题, 去提问

问题事件

关注

码龄粉丝数原力等级 --

被采纳

被点赞

采纳率
系统已结题 3月6日
关注

码龄粉丝数原力等级 --

被采纳

被点赞

采纳率
已采纳回答 2月26日
关注

码龄粉丝数原力等级 --

被采纳

被点赞

采纳率
创建了问题 2月26日

悬赏问题

¥35 平滑拟合曲线该如何生成
¥100 c语言，请帮蒟蒻写一个题的范例作参考
¥15 名为“Product”的列已属于此 DataTable
¥15 安卓adb backup备份应用数据失败
¥15 eclipse运行项目时遇到的问题
¥15 关于#c##的问题：最近需要用CAT工具Trados进行一些开发
¥15 南大pa1 小游戏没有界面，并且报了如下错误，尝试过换显卡驱动，但是好像不行
¥15 自己瞎改改，结果现在又运行不了了
¥15 链式存储应该如何解决
¥15 没有证书，nginx怎么反向代理到只能接受https的公网网站

SQL SERVER 存储过程 查询出来的数据为一行合计 不需要进行分组,如何进行调整。

问题遇到的现象和发生背景

问题相关代码，请勿粘贴截图

运行结果及报错内容

我的解答思路和尝试过的方法

我想要达到的结果

1条回答 默认 最新

问题事件

悬赏问题

SQL SERVER 存储过程查询出来的数据为一行合计不需要进行分组,如何进行调整。

1条回答默认最新