正文
sqlserver合计行,sql 合计
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
关于SqlServer数据库行求和的问题
create Table T
(
id int,
a int,
b int
)
Insert into t values(1,1,1)
Insert into t values(2,2,1)
Insert into t values(3,3,1)
Insert into t values(4,4,1)
Insert into t values(5,5,1)
Insert into t values(6,6,1)
/*思路:
id可能不连续,所以先用id排序,产生一个记录号id2
然后按3条记录一组产生一个分组号G,接着按G分组求和,id取最大
最后更新
*/
With CT
AS
(
Select max(id) As id,SUM(a) As A,SUM(B) As B
from
(
Select *,(id2+2)/3 As G
from (Select *,ROW_NUMBER() over(order by id) As id2 from T) S
)M Group by G
)
Update T Set a=CT.A-T.a,b=CT.B-T.b
From CT
where T.id=CT.id
相关问答
Q1: 求一个sql 语句添加行 合计 内容如下
select * from Table1
union all select name+' Toal' name ,sum(num1),sum(num2) from Table1
where name not in('Lab','RD')
group by name
--order by name
union all select 'RD Lab' name,sum(num1),sum(num2) from Table1
where name in('Lab','RD')
union all select 'Total' name,sum(num1),sum(num2) from Table1
order by name
Q2: SQL插入合计行的问题
因为count()函数只返回一个值,不能和查询多行数据一起使用
应该分开写:
select Name,Password from Users --查询出所有的行
select count(*) from Users --查询出所有记录的行数,*代表所有的列
