/*
原数据:
----------------------------------
存货编码 数量 标识
-----------------------------------
0001 100 0
0001 50 1
0001 100 2注:标识0表示期初,1表示收入,2表示发出
*/需要得出以下结果:
[code=SQL]
----------------------------------
存货编码 收入 发出 结存
-----------------------------------
0001 100
0001 50 150
0001 100 50 [/code]
sum(case when 标识 = 0 then 数量 end) 收入,
sum(case when 标识 = 1 then 数量 end) 发出,
sum(case when 标识 = 2 then 数量 end) 结存
from tb
group by 存货编码
试试
请看清需求,再发贴
sum(case when 标识 = 0 then 数量 end) 收入,
sum(case when 标识 = 1 then 数量 end) 发出,
sum(case when 标识 = 2 then 数量 end) 结存
from tb
group by 存货编码,标识/*
存货编码,收入,发出,结存
0001,100,NULL,NULL
0001,NULL,50,NULL
0001,NULL,NULL,100
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
和你要的有点出入
select 存货编码,
sum(case when 标识 = 1 then 数量 end) 收入,
sum(case when 标识 = 2 then 数量 end) 发出,
sum(case when 标识 in (0,1,2) then 数量 end) 结存
from tb
group by 存货编码,标识/*
存货编码,收入,发出,结存
0001,NULL,NULL,100
0001,50,NULL,50
0001,NULL,100,100
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
select *,row_number() over (PARTITION BY 存货编码 order by getdate()) as num
from tab
)
select 存货编码,
case when 标识 = 1 then 数量 end 收入,
case when 标识 = 2 then 数量 end 发出,
(select sum(case when 标识 = 2 then -数量 else 数量 end) ) from cte where 存货编码 = a.存货编码 and num<=a.num) 结存
from cte a
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-09-13 13:29:53
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([存货编码] varchar(4),[数量] int,[标识] int)
insert [tb]
select '0001',100,0 union all
select '0001',50,1 union all
select '0001',100,2
--------------开始查询--------------------------
select
存货编码,
isnull(ltrim(sum(case when 标识 = 0 then 数量 end)),'') 收入,
isnull(ltrim(sum(case when 标识 = 1 then 数量 end)),'') 发出,
isnull(ltrim(sum(case when 标识 = 2 then 数量 end)),'') 结存
from
tb
group by
存货编码,标识
----------------结果----------------------------
/* 存货编码 收入 发出 结存
---- ------------ ------------ ------------
0001 100
0001 50
0001 100
警告: 聚合或其他 SET 操作消除了 Null 值。(3 行受影响)*/
(select sum(case when 标识 = 2 then -数量 else 数量 end) ) from
这个括号去掉
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-09-13 13:29:53
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([存货编码] varchar(4),[数量] int,[标识] int)
insert [tb]
select '0001',100,0 union all
select '0001',50,1 union all
select '0001',100,2
--------------开始查询--------------------------
;with f as (
select *,row_number() over (PARTITION BY 存货编码 order by getdate()) as num from tb)
select
存货编码,
case when 标识 = 1 then 数量 end as 收入,
case when 标识 = 2 then 数量 end as 发出,
(select sum(case when 标识 = 2 then -数量 else 数量 end) from f where 存货编码 =t.存货编码 and num<=t.num) as 结存
from
f t----------------结果----------------------------
/*存货编码 收入 发出 结存
---- ----------- ----------- -----------
0001 NULL NULL 100
0001 50 NULL 150
0001 NULL 100 50(3 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-09-13 13:29:53
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([存货编码] varchar(4),[数量] int,[标识] int)
insert [tb]
select '0001',100,0 union all
select '0001',50,1 union all
select '0001',100,2
--------------开始查询--------------------------
select IDENTITY (int,1,1) as id ,* into #t from tb
select
存货编码,
case when 标识 = 1 then 数量 end as 收入,
case when 标识 = 2 then 数量 end as 发出,
(select sum(case when 标识 = 2 then -数量 else 数量 end) from #t where 存货编码 =t.存货编码 and id<=t.id) as 结存
from
#t tdrop table #t
----------------结果----------------------------
/*存货编码 收入 发出 结存
---- ----------- ----------- -----------
0001 NULL NULL 100
0001 50 NULL 150
0001 NULL 100 50(3 行受影响)
*/
select * ,IDENTITY(int,1,1) as num
into #cte
from tab
select 存货编码,
case when 标识 = 1 then 数量 end 收入,
case when 标识 = 2 then 数量 end 发出,
(select sum(case when 标识 = 2 then -数量 else 数量 end) from #cte where 存货编码 = a.存货编码 and num<=a.num) 结存
from #cte a
order by 存货编码,num
union all
select 2,存货编码,数量,null,数量+(select 数量 from tb where 标识=0) from tb where 标识=1
union all
select 3,存货编码,null,(select sum(数量) from tb) from tb where 标识=2 order by id