sql - 使用 Sql 服务器按总和分组,每个总和组结束最后一行
问题描述
我已经附加了一个图像,我想要 7800-2000=5800,基于使用 sql server 查询的组最后一行总和
我想要这样的输出
GroupName Credit Debit DrCrEffect UnderGroupName Total
Opening Balance 0.00 0.00 Dr Master Group NULL
Opening Stock 0.00 0.00 Dr Opening Stock NULL
Purchase 0.00 7800.00 Dr Purchase 0.00
Purchase Return 2000.00 0.00 Dr Purchase 5800
询问:
SELECT MAG.GroupName,
SUM(isnull(tm.Credit,0.00))Credit,
SUM(ISNULL(TM.Debit,0.00))Debit,
mag.DrCrEffect,
MAG.UnderGroupName,
SUM(TM.Credit-tm.Debit)Total
FROM dbo.Mst_Account_Group MAG
LEFT JOIN dbo.Party P ON MAG.Mst_Account_Group_Id = P.Mst_Account_Group_Id
AND mag.EffectTo='Trading Account' AND MAG.DrCrEffect='dr'
LEFT JOIN dbo.Transaction_Master TM ON tm.Ledger_Id=p.Party_Id
WHERE P.Company_Id=1 AND P.Branch_Id=1
GROUP BY mag.GroupName, mag.DrCrEffect, MAG.UnderGroupName
解决方案
您可以使用LAG功能来实现这一点。LAG 将为您提供当前行选择中上一行的值。
declare @table table (groupname varchar(50),
credit numeric(18,2),
debit numeric(18,2),
drcreffect varchar(50),
undergroup varchar(50))
insert into @table values
('Opening Balance', 0.00 , 0.00 ,'Dr', 'Master Group' ),
('Opening Stock', 0.00 , 0.00 ,'Dr', 'Opening Stock' ),
('Purchase', 0.00 , 7800.00 ,'Dr', 'Purchase'),
('Purchase Return', 2000.00, 0.00 ,'Dr', 'Purchase')
select groupname,
credit,
debit,
drcreffect,
undergroup,
LAG(debit,1,0) OVER (ORDER BY groupname)-credit AS Total
from @table
输出:
注意:替换@table
为您的实际表名。
更新:
declare @table table (groupname varchar(50),
credit numeric(18,2),
debit numeric(18,2),
drcreffect varchar(50),
undergroup varchar(50))
insert into @table values
('Opening Balance', 0.00 , 0.00 ,'Dr', 'Master Group' ),
('Opening Stock', 0.00 , 0.00 ,'Dr', 'Opening Stock' ),
('Purchase', 0.00 , 7800.00 ,'Dr', 'Purchase'),
('Purchase Return', 2000.00, 0.00 ,'Dr', 'Purchase'),
('Purchase Return', 3000.00, 0.00 ,'Dr', 'Purchase'),
('Sales', 0.00, 5000.00 ,'Dr', 'Sales'),
('Sales Return', 2000.00, 0.00 ,'Dr', 'Sales'),
('Sales Return', 2000.00, 0.00 ,'Dr', 'Sales')
SELECT groupname,credit,debit,drcreffect,undergroup, LAG(debit,1,0) OVER (ORDER BY groupname)-credit AS Total from (
SELECT DISTINCT groupname,SUM(credit) OVER(PARTITION BY groupname) AS Credit,SUM(debit) OVER(PARTITION BY groupname) AS debit,drcreffect,undergroup
FROM @table ) X
推荐阅读
- php - 我可以在不破坏站点的情况下将所有 PHP 非多字节函数更改为它们的 mb_ 等效函数(即 mb_str_replace、mb_strlen 等)吗?
- php - 寻找一种简单的方法来将整数 ID #s 映射到一个模糊的、更长的(非连续的)常量大小的整数
- c# - 等待 DevTools 加载
- windows - 如何使用 ansible 在 C: 上获得剩余大小?
- javascript - 如何在比较另一个对象的同时过滤一组对象?
- c# - 防止 Specflow 步骤参数显示在 Specflow 报告中,即“密码”
- angular - Firebase 使用 ASP.NET Core 后端托管 Angular 应用程序,但如何连接到我的后端
- c++ - CHOLMOD 稀疏密集乘法问题
- python - 如何从文本中提取像 (010000 AAAAAAAAA) 这样的字符串
- php - 检索特定 xml 数据的问题