求每天的收入和支出
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](名称 varchar(10),金额 int,标志 varchar(2),日期 varchar(30))--收入1 支出为2
insert [tb]
select 'a',100,'1','2009-11-23 13:45' union all
select 'b',150,'2','2009-11-22 13:45' union all
select 'c',190,'1','2009-11-23 13:45' union all
select 'd',110,'2','2009-11-23 13:45' union all
select 'e',145,'1','2009-11-24 13:45'
select * from tb
select
convert(varchar(10),日期,120) as 日期,
sum(case 标志 when 1 then 金额 else 0 end) as 收入,
sum(case 标志 when 2 then 金额 else 0 end) as 支出
from
tb
group by
convert(varchar(10),日期,120)
/*
名称 金额 标志 日期
---------- ----------- ---- ------------------------------
a 100 1 2009-11-23 13:45
b 150 2 2009-11-22 13:45
c 190 1 2009-11-23 13:45
d 110 2 2009-11-23 13:45
e 145 1 2009-11-24 13:45
日期 收入 支出
---------- ----------- -----------
2009-11-22 0 150
2009-11-23 290 110
2009-11-24 145 0
*/
select batchid, max (message), min (SendTime), sum ( case result1 when 0 then 1 else 0 end ) as TotalSub, sum ( case result2 when 0 then 1 else 0 end ) as SuccSubNumber, sum ( case result3 when 0 then 1 else 0 end ) as AbsoluteSuccNumber from Send WHERE CompanyID=1 group by batchid order by batchid desc |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步