sqlserver分组求和语句,oracle 分组求和
sql server 一段语句求多个和。
1 先按人名和支付方式汇总
创新互联建站成立与2013年，先为江永等服务建站，江永等地企业，进行企业商务咨询服务。为江永企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
2 行列转换
sqlserver行列转换知识
create table A(name varchar(10),sort varchar(30),q int)
insert into A values('A','现金',19)
insert into A values('A','现金',19)
insert into A values('B','现金',19)
insert into A values('C','现金',19)
insert into A values('A','微信',19)
insert into A values('A','银行卡',19)
insert into A values('A','支付宝',19)
go
select * from
(
select name,sort,sum(q) as qtyall from A
group by name,sort
) as t
pivot(sum(qtyall) for sort
in([现金],[银行卡],[微信],[支付宝]))
t ;
go
drop table A
如有疑问，及时沟通！
sqlserver中求平均值跟求和语句
平均值
select avg(某字段) from dual；
求和
select sum(某字段) from dual;
sqlserver怎么实现同一个表中多个count查询并且分组并且统计总数
可以有两种解决方法，
所需工具：SQL
查询两个count的方法1:
SELECT paperName , COUNT (1) AS 总题数 , sum (CASE WHEN statu = 1 THEN 1 ELSE 0 END) AS 审核题数FROM questionGROUP BY paperNme
查询两个count的方法2:
select s.总题数, s.审核题数, s.paperNamefrom (select COUNT(1) as 总题数, case when status = 1 then count(1) else 0 end as 审核题数, paperNamefrom question--where papername in (select distinct paperName from question), 这个条件可以不要了group by paperNme, stauts -- status也要作为分组字段，因为在case中有使用) s
sql 按类型分组求和
参考sql语句
select no ,
max(case when type=1 then type) else null end) type1,
max(case when type=2 then type) else null end) type2,
sum(case when type=1 then sumsource) else null end) source1,
sum(case when type=2then sumsource) else null end) source2,
from(
selct no , type, sum(source) sumsource
group by no,type
)
SQLServer 关于Sum的语句
select buy.cust_no,SUM(amount) from buy join customer c on buy.cust_no=c.cust_no group by buy.cust_no having SUM(amount)=100
对分组过滤用的是having子句
关于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
文章名称：sqlserver分组求和语句,oracle 分组求和
本文来源：http://scyanting.com/article/dsgeeso.html
