declare @t table( id int identity(1,1),type varchar(10),name varchar(20),number int, flag char(1)) insert into @t select '服装','上衣',100,'1'insert into @t select '服装','上衣',100,'1'insert into @t select '服装','上衣',30,'0'insert into @t select '服装','上衣',50,'0'
insert into @t select '食品','牛奶',200,'1'insert into @t select '食品','牛奶',200,'1'insert into @t select '食品','牛奶',400,'0'-----------------------------------------------------------------------------------要得到的结果: type name number 服装 上衣 120 ---------------------------------------------------------------------------------------注:这是一个出入库的操作,flag=1代表入库数据,flag=0代表出库数据
查询出的number=120是“服装”的库存的结余数,即 100+100-30-50=120困难之处在于:如果库存的结余数大于0,则显示出大于0的查询结果
如果库存的结余数等于0,则不显示等于0的查询结果
如:“牛奶”的库存结余数 200+200-400=0,则在查询结果中不显示出来。
insert into @t select '食品','牛奶',200,'1'insert into @t select '食品','牛奶',200,'1'insert into @t select '食品','牛奶',400,'0'-----------------------------------------------------------------------------------要得到的结果: type name number 服装 上衣 120 ---------------------------------------------------------------------------------------注:这是一个出入库的操作,flag=1代表入库数据,flag=0代表出库数据
查询出的number=120是“服装”的库存的结余数,即 100+100-30-50=120困难之处在于:如果库存的结余数大于0,则显示出大于0的查询结果
如果库存的结余数等于0,则不显示等于0的查询结果
如:“牛奶”的库存结余数 200+200-400=0,则在查询结果中不显示出来。
解决方案 »
- sql server 7.0的数据附加到2000上,然后还可以在7.0中恢复吗
- 请高手看看?为何{"第 15 行: ',' 附近有语法错误。\r\n在关键字 'AS' 附近有语法错误。\r\n在关键字 'AS' 附近有语法错误。" }
- SQL2005 怎么没有查询窗口?
- 一个SQL语句问题
- 一个表不该被反复3次以上不断的重复join的?
- 100分求:多条数据转成一条记录的问题,请教各位高手
- 事務中出錯語句如何跳轉?
- 求一个SQL查询语句
- 什么办法生成一张表的速度最快?
- ?我每次启动SBASE SQL ANYWHERE数据库后,有两个表的多条记录被锁定
- select,where,in子查询问题
- 怎样将本地sql中的数据表导入到远程的空间上?
insert into @t select '服装','上衣',100,'1'
insert into @t select '服装','上衣',30,'0'
insert into @t select '服装','上衣',50,'0' insert into @t select '食品','牛奶',200,'1'
insert into @t select '食品','牛奶',200,'1'
insert into @t select '食品','牛奶',400,'0'
select type,name,sum(case when flag=1 then number else -1*number end) as number
from @t
group by type,name
having sum(case when flag=1 then number else -1*number end)<>0
from @t group by type,name having sum(case flag when 1 then number when 0 then -1*number else 0 end) > 0
insert into @t select '服装','上衣',100,'1'
insert into @t select '服装','上衣',30,'0'
insert into @t select '服装','上衣',50,'0' insert into @t select '食品','牛奶',200,'1'
insert into @t select '食品','牛奶',200,'1'
insert into @t select '食品','牛奶',400,'0'SELECT * FROM @TSELECT
TYPE,
NAME,
SUM(CASE WHEN FLAG='1' THEN NUMBER ELSE -NUMBER END) AS 结余
FROM @T
GROUP BY TYPE,NAME
having SUM(CASE WHEN FLAG='1' THEN NUMBER ELSE -NUMBER END)<>0
select a.type,a.name,(b.number1-c.number2) as number3
from @t a,(select type,sum(number) as number1 from @t where flag='1' group by type) as b,(select type,sum(number) as number2 from @t where flag='0' group by type) as c
where a.type=b.type and b.type=c.type and (b.number1-c.number2) <>'0'
group by a.type,a.name,b.number1,c.number2
order by a.type,a.name,b.number1,c.number2
--
go
declare @t table( id int identity(1,1),type varchar(10),name varchar(20),number int, flag char(1)) insert into @t select '服装','上衣',100,'1'insert into @t select '服装','上衣',100,'1'insert into @t select '服装','上衣',30,'0'insert into @t select '服装','上衣',50,'0'
insert into @t select '食品','牛奶',200,'1'insert into @t select '食品','牛奶',200,'1'insert into @t select '食品','牛奶',400,'0'--
select a.type,a.name,(b.number1-c.number2) as number3
from @t a,(select type,sum(number) as number1 from @t where flag='1' group by type) as b,(select type,sum(number) as number2 from @t where flag='0' group by type) as c
where a.type=b.type and b.type=c.type and (b.number1-c.number2) <>'0'
group by a.type,a.name,b.number1,c.number2
order by a.type,a.name,b.number1,c.number2