比如查2003-01-15的余额:
select name,sum(dayin)-sum(dayout)
from income
where dt<='2003-01-15'
group by name
order by name
select name,sum(dayin)-sum(dayout)
from income
where dt<='2003-01-15'
group by name
order by name
解决方案 »
- 怎样给存储过程的参数负空值?
- 大家看看 这个sql语句如何优化?? 谢谢
- 触发器中运用事务的问题。
- 建立一张特殊表的问题(在线等待!立即结贴!)
- 麻烦帮我看看这个语句
- sql server 客户端连接服务器问题?
- SQL数据库表给替换了,现在要恢复
- 请教:如何从一开始就避免数据库因事务日志文件占满磁盘空间而无法完成某个指令?谢谢
- 在sql server查询分析器中,如何可以对select出来的记录进行编辑
- VFP用什么命令退出应用程序啊!
- 我如果检索到几万条记录,肯定不可能在Msflexgrid表里一次性显示,该怎么办呢?
- 请问ORACLE与MSSQL。SERVER主要区别与各自优势。具体使用有什么不同之处
insert @income values('2002-1-1','aa',5,3)
insert @income values('2002-1-1','bb',3,4)
insert @income values('2002-1-2','bb',7,5)
insert @income values('2002-1-3','aa',5,3)
select c.dt,c.name,isnull(a.dayin,0) dayin,isnull(a.dayout,0) dayout,(select sum(isnull(dayin,0)-isnull(dayout,0)) from @income where name=c.name and dt<=c.dt) net_income
from @income a right join (select dt,name from (select distinct dt from @income )aaa ,(select distinct name from @income) bbb) c on a.dt=c.dt and a.name=c.name order by c.dt,c.name
insert @income values('2002-1-1','bb',3,4)
insert @income values('2002-1-3','aa',5,3)
select top 1000 identity(int,1,1) id into # from sysobjects a,sysobjects b,sysobjects c
select c.dt,c.name,isnull(a.dayin,0) dayin,isnull(a.dayout,0) dayout,(select sum(isnull(dayin,0)-isnull(dayout,0)) from @income where name=c.name and dt<=c.dt) net_income
from @income a right join (select dt,name from (select dateadd(day,id,dmin-1) dt from (select min(dt) dmin,datediff(day,min(dt),max(dt))+1 f from @income) a join # b on a.f>=b.id)aaa ,(select distinct name from @income) bbb) c on a.dt=c.dt and a.name=c.name order by c.dt,c.name drop table #
insert @income values('2002-1-1','aa',5,3)
insert @income values('2002-1-1','bb',3,4)
insert @income values('2002-1-3','aa',5,3)
select top 1000 identity(int,1,1) id into # from sysobjects a,sysobjects b,sysobjects c
select c.dt,c.name,isnull(a.dayin,0) dayin,isnull(a.dayout,0) dayout,(select sum(isnull(dayin,0)-isnull(dayout,0)) from @income where name=c.name and dt<=c.dt) net_income
from @income a right join (select dt,name from (select dateadd(day,id,dmin-1) dt from (select min(dt) dmin,datediff(day,min(dt),max(dt))+1 f from @income) a join # b on a.f>=b.id)aaa ,(select distinct name from @income) bbb) c on a.dt=c.dt and a.name=c.name order by c.dt,c.name drop table #
日合计收入 dayin
日合计支出 dayout
的默认值是多少?
默认值是‘null’吧?
insert @income values('2002-1-1','aa',5,3)
insert @income values('2002-1-1','bb',3,4)
insert @income values('2002-1-3','aa',5,3)select c.dt,c.name,isnull(a.dayin,0) dayin,isnull(a.dayout,0) dayout,(select sum(isnull(dayin,0)-isnull(dayout,0)) from @income where name=c.name and dt<=c.dt) net_income
from @income a right join (select dt,name from (select distinct dt from @income )aaa ,(select distinct name from @income) bbb) c on a.dt=c.dt and a.name=c.name order by c.dt,c.name---如果这样就少了一天的!那你只能用第二种方法!