同一sessionid同一alcode下的amt要累加后的amt<0
解决方案 »
- Syntax error converting datetime from character string
- 修改一个表内容
- win7安装SQL Server2008最后提示reporting service失败?
- 客户端应用程式怎样连sql server 2005镜像上的数据库
- 如何用存储工程描述底下这表之间汇总?
- 'textcopy' 不是内部或外部命令,也不是可运行的程序
- 关于xml 类型数据中批量修改
- SQL信任连接无法读取text类型数据
- 怎样知道数据库中每个表的最近更新时间
- ORACLE都有那些版本可以安装在WINNT/WIN2000/W98上,几张盘?
- 重返SQL基础版
- openrowset 打开其它计算机上的共享文件时说找不到文件,怎么会这样?
where rundate between '2004-07-01' and '2004-08-01'
and alcode='00001'
and amt>0
union
select * from 表 a
where alcode='00001'
and(select sum(amt) from 表 where sessionid=a.sessionid)<0
union all
select amt=sum(amt),sessionid , alcode from tb where amt<0 convert(varchar(10),rendate,120) between '2004-07-01' and '2004-08-01' group by sessionid , alcode
union all
select amt=sum(amt),sessionid , alcode from tb where amt<0 and alcode='00001' and convert(varchar(10),rendate,120) between '2004-07-01' and '2004-08-01' group by sessionid , alcode
where rundate between '2004-07-01' and '2004-08-01'
and alcode='00001'
and amt>0
union
select * from 表
where alcode='00001'
and sessionid=a.sessionid
and amt<0
and sheetid not in
(
select sheetid from 表
where rundate between '2004-07-01' and '2004-08-01'
and alcode='00001'
and amt>0
)
where rundate between '2004-07-01' and '2004-08-01'
and alcode='00001'
and (select sum(amt) from 表 where sessionid=a.sessionid)>0
union
select * from 表 a
where alcode='00001'
and(select sum(amt) from 表 where sessionid=a.sessionid)<0
但是如果要同一sessionid同一alcode下的amt>0,必定有一条数据的amt是>0的,但是这个数据不能被列出,这和第一个条件又冲突了,所以我写不来了
如果他们的合计<0,那么不管rundate的范围,这三条数据都应该被列出
create table tb(sheetid int,amt int,sessionid varchar(10),alcode varchar(10),rundate varchar(10))
insert tb select 1, 200,'00001','00001','2004-07-18'
union all select 2,-400,'00001','00001','2004-07-18'
union all select 3, 200,'00002','00001','2004-07-18'
union all select 4, 400,'00001','00001','2004-07-18'
union all select 5,-200,'00001','00001','2004-08-18'
go--这个意思?
select *
from tb a
where rundate between '2004-07-01'
and '2004-08-01' --在2004-07-01至2004-08-01这段时间内
and alcode='00001'
and amt>0 --lcode=00001,所有的amt>0
and exists( --同一sessionid同一alcode下的amt>0
--这个sessionid下的所有数据都不能被列出
--也就是必须存在<=0的
select * from tb
where sessionid=a.sessionid
and alcode=a.alcode
and amt<=0)
union
select a.*
from tb a,(
select sessionid,alcode
from tb
where alcode='00001' --alcode=00001
group by sessionid,alcode
having sum(amt)<0 --同一sessionid同一alcode下的amt<0
)b where a.sessionid=b.sessionid
and a.alcode=b.alcode
go--删除测试
drop table tb/*--测试结果sheetid amt sessionid alcode rundate
----------- ----------- ---------- ---------- ----------
1 200 00001 00001 2004-07-18
4 400 00001 00001 2004-07-18(所影响的行数为 2 行)
--*/