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
sql server的存储过程执行命令成功了却没有插入数据 sql 数据库
2022-06-26 17:11

回答 2 已采纳创建存储过程的代码后面都要加上go，这样exec调用才会插入数据下图才是正常的
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'
SqlServer 处理百万级以上的数据如何提高查询速度
2024-11-26 14:57

凯酱的博客例如耽搁查询的排序、连接、扫描和 GROUP BY 字句同时执行，SQL SERVER 根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的 CPU 的查询最适合并行处理。在 T-sql 的写法上有很大的讲究，下面列出常见的...
SQL SERVER 存储过程中SELECT 返回值如何赋值给变量 sql sqlserver
2021-07-27 13:37

回答 2 已采纳这和你另一个问题是相同的情况吧
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
2022-01-19 22:57

回答 7 已采纳查询的字段DQ_RpNr1,DQ_RpNr2,DQ_RpNr3,DQ_RpNr4可以动态生成，现在示例中使用CET表只能查询一次，改成实体表、表变量或者临时表都可以生成，组成动态SQL再exec就可以
MS SQL Server 实战统计与汇总重复记录
2024-10-08 15:18

初九之潜龙勿用的博客比如我们有一组题库数据，主要包括题目和选项字段（如单选题目、多选题目和判断题目），一个合理的数据存储应该至少保证这些题目在分类中不应该出现重复题目标题数据，本文将介绍如何利用group by 、with rollup、...
（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】怎么在对已分组数据进行总合计并在列中展示 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查询优化
2020-02-29 14:25

卖裤子上网的博客 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id ...
sqlserver 获取某段时间每天数据的前10行数据 sql sqlserver 数据库有问必答
2021-12-30 16:35

回答 4 已采纳 mssqlserver?有row_number，下面是取每天最新的10条，如果是升序，去掉desc select * from(select *,ROW_NUMBER() over(partition
Sql server 千万级大数据SQL查询优化的几点建议
2022-01-18 09:36

qhdzj87的博客 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id ...
SQL Server 百万级数据提高查询速度的方法
2018-07-30 17:27

Gonietz123的博客 2.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id ...
没有解决我的问题, 去提问

问题事件

关注

码龄粉丝数原力等级 --

被采纳

被点赞

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

码龄粉丝数原力等级 --

被采纳

被点赞

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

码龄粉丝数原力等级 --

被采纳

被点赞

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

悬赏问题

¥15 PADS Logic 原理图
¥15 PADS Logic 图标
¥15 电脑和power bi环境都是英文如何将日期层次结构转换成英文
¥20 气象站点数据求取中~
¥15 如何获取APP内弹出的网址链接
¥15 wifi 图标不见了不知道怎么办上不了网变成小地球了
¥50 STM32单片机传感器读取错误
¥15 (关键词-阻抗匹配，HFSS，RFID标签天线)
¥15 机器人轨迹规划相关问题
¥15 word样式右侧翻页键消失

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

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

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

运行结果及报错内容

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

我想要达到的结果

1条回答 默认 最新

问题事件

悬赏问题

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

1条回答默认最新