27,579
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-11-12 12:01:21
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([购入量] numeric(5,2),[使用量] numeric(4,2),[商家] varchar(5),[产品类型] varchar(5),[姓名] varchar(4))
insert [tb]
select 12.3,0,'商家A','产品A','王红' union all
select 0,28.45,'商家A','产品C','王红' union all
select 45,0,'商家A','产品A','王红' union all
select 101.2,0,'商家A','产品B','刘明' union all
select 0,27.25,'商家B','产品A','王红' union all
select 70,0,'商家C','产品A','刘明' union all
select 0,33.15,'商家B','产品C','刘明' union all
select 0,29.75,'商家B','产品A','孙浩' union all
select 0,37.67,'商家C','产品B','王红' union all
select 12.54,0,'商家A','产品B','孙浩' union all
select 0,31.56,'商家B','产品A','孙浩' union all
select 7.0,0,'商家A','产品C','孙浩' union all
select 88,0,'商家C','产品C','孙浩' union all
select 0,37.08,'商家C','产品A','王红' union all
select 0,34.90,'商家A','产品A','刘明' union all
select 4,0,'商家B','产品B','刘明' union all
select 0,30.14,'商家A','产品C','刘明'
--------------开始查询--------------------------
select
a.购入量,a.使用量,b.*
from
(select isnull(sum([购入量]),0) as 购入量 ,isnull(sum([使用量]),0)as 使用量 ,[商家],[产品类型],[姓名] from tb group by [商家],[产品类型],[姓名])a
left join
(
select isnull(sum([购入量]),0)-isnull(sum([使用量]),0) as 剩余量,[商家],[产品类型],[姓名] from tb group by [商家],[产品类型],[姓名]
)b
on
a.[商家]=b.[商家] and a.[产品类型]=b.[产品类型] and a.[姓名]=b.[姓名]
----------------结果----------------------------
/*
(17 行受影响)
购入量 使用量 剩余量 商家 产品类型 姓名
--------------------------------------- --------------------------------------- --------------------------------------- ----- ----- ----
0.00 34.90 -34.90 商家A 产品A 刘明
57.30 0.00 57.30 商家A 产品A 王红
101.20 0.00 101.20 商家A 产品B 刘明
12.54 0.00 12.54 商家A 产品B 孙浩
0.00 30.14 -30.14 商家A 产品C 刘明
7.00 0.00 7.00 商家A 产品C 孙浩
0.00 28.45 -28.45 商家A 产品C 王红
0.00 61.31 -61.31 商家B 产品A 孙浩
0.00 27.25 -27.25 商家B 产品A 王红
4.00 0.00 4.00 商家B 产品B 刘明
0.00 33.15 -33.15 商家B 产品C 刘明
70.00 0.00 70.00 商家C 产品A 刘明
0.00 37.08 -37.08 商家C 产品A 王红
0.00 37.67 -37.67 商家C 产品B 王红
88.00 0.00 88.00 商家C 产品C 孙浩
(15 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-11-12 12:01:21
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([购入量] numeric(5,2),[使用量] numeric(4,2),[商家] varchar(5),[产品类型] varchar(5),[姓名] varchar(4))
insert [tb]
select 12.3,0,'商家A','产品A','王红' union all
select 0,28.45,'商家A','产品C','王红' union all
select 45,0,'商家A','产品A','王红' union all
select 101.2,0,'商家A','产品B','刘明' union all
select 0,27.25,'商家B','产品A','王红' union all
select 70,0,'商家C','产品A','刘明' union all
select 0,33.15,'商家B','产品C','刘明' union all
select 0,29.75,'商家B','产品A','孙浩' union all
select 0,37.67,'商家C','产品B','王红' union all
select 12.54,0,'商家A','产品B','孙浩' union all
select 0,31.56,'商家B','产品A','孙浩' union all
select 7.0,0,'商家A','产品C','孙浩' union all
select 88,0,'商家C','产品C','孙浩' union all
select 0,37.08,'商家C','产品A','王红' union all
select 0,34.90,'商家A','产品A','刘明' union all
select 4,0,'商家B','产品B','刘明' union all
select 0,30.14,'商家A','产品C','刘明'
--------------开始查询--------------------------
select
b.*
from
tb a
left join
(
select isnull(sum([购入量]),0)-isnull(sum([使用量]),0) as 剩余量,[商家],[产品类型],[姓名] from tb group by [商家],[产品类型],[姓名]
)b
on
a.[商家]=b.[商家] and a.[产品类型]=b.[产品类型] and a.[姓名]=b.[姓名]
----------------结果----------------------------
/*剩余量 商家 产品类型 姓名
--------------------------------------- ----- ----- ----
57.30 商家A 产品A 王红
-28.45 商家A 产品C 王红
57.30 商家A 产品A 王红
101.20 商家A 产品B 刘明
-27.25 商家B 产品A 王红
70.00 商家C 产品A 刘明
-33.15 商家B 产品C 刘明
-61.31 商家B 产品A 孙浩
-37.67 商家C 产品B 王红
12.54 商家A 产品B 孙浩
-61.31 商家B 产品A 孙浩
7.00 商家A 产品C 孙浩
88.00 商家C 产品C 孙浩
-37.08 商家C 产品A 王红
-34.90 商家A 产品A 刘明
4.00 商家B 产品B 刘明
-30.14 商家A 产品C 刘明
(17 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-11-12 12:01:21
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([购入量] numeric(5,2),[使用量] numeric(4,2),[商家] varchar(5),[产品类型] varchar(5),[姓名] varchar(4))
insert [tb]
select 12.3,0,'商家A','产品A','王红' union all
select 0,28.45,'商家A','产品C','王红' union all
select 45,0,'商家A','产品A','王红' union all
select 101.2,0,'商家A','产品B','刘明' union all
select 0,27.25,'商家B','产品A','王红' union all
select 70,0,'商家C','产品A','刘明' union all
select 0,33.15,'商家B','产品C','刘明' union all
select 0,29.75,'商家B','产品A','孙浩' union all
select 0,37.67,'商家C','产品B','王红' union all
select 12.54,0,'商家A','产品B','孙浩' union all
select 0,31.56,'商家B','产品A','孙浩' union all
select 7.0,0,'商家A','产品C','孙浩' union all
select 88,0,'商家C','产品C','孙浩' union all
select 0,37.08,'商家C','产品A','王红' union all
select 0,34.90,'商家A','产品A','刘明' union all
select 4,0,'商家B','产品B','刘明' union all
select 0,30.14,'商家A','产品C','刘明'
--------------开始查询--------------------------
select
a.* ,b.剩余量
from
tb a
left join
(
select isnull(sum([购入量]),0)-isnull(sum([使用量]),0) as 剩余量,[商家],[产品类型],[姓名] from tb group by [商家],[产品类型],[姓名]
)b
on
a.[商家]=b.[商家] and a.[产品类型]=b.[产品类型] and a.[姓名]=b.[姓名]
----------------结果----------------------------
/* 购入量 使用量 商家 产品类型 姓名 剩余量
--------------------------------------- --------------------------------------- ----- ----- ---- ---------------------------------------
12.30 0.00 商家A 产品A 王红 57.30
0.00 28.45 商家A 产品C 王红 -28.45
45.00 0.00 商家A 产品A 王红 57.30
101.20 0.00 商家A 产品B 刘明 101.20
0.00 27.25 商家B 产品A 王红 -27.25
70.00 0.00 商家C 产品A 刘明 70.00
0.00 33.15 商家B 产品C 刘明 -33.15
0.00 29.75 商家B 产品A 孙浩 -61.31
0.00 37.67 商家C 产品B 王红 -37.67
12.54 0.00 商家A 产品B 孙浩 12.54
0.00 31.56 商家B 产品A 孙浩 -61.31
7.00 0.00 商家A 产品C 孙浩 7.00
88.00 0.00 商家C 产品C 孙浩 88.00
0.00 37.08 商家C 产品A 王红 -37.08
0.00 34.90 商家A 产品A 刘明 -34.90
4.00 0.00 商家B 产品B 刘明 4.00
0.00 30.14 商家A 产品C 刘明 -30.14
(17 行受影响)
*/
SELECT * ,shengyu=(SELECT SUM(b.[购入量]-b.[使用量])from #tb b
where b.[商家]=t.[商家] and b.[产品类型]=t.[产品类型]
AND b.[姓名]=t.[姓名] and b.id<=t.id)
FROM #tb t
ID 购入量 使用量 商家 产品类型 姓名 shengyu
----------- --------------------------------------- --------------------------------------- ----- ----- ---- ---------------------------------------
1 12.30 0.00 商家A 产品A 王红 12.30
2 0.00 28.45 商家A 产品C 王红 -28.45
3 45.00 0.00 商家A 产品A 王红 57.30
4 101.20 0.00 商家A 产品B 刘明 101.20
5 0.00 27.25 商家B 产品A 王红 -27.25
6 70.00 0.00 商家C 产品A 刘明 70.00
7 0.00 33.15 商家B 产品C 刘明 -33.15
8 0.00 29.75 商家B 产品A 孙浩 -29.75
9 0.00 37.67 商家C 产品B 王红 -37.67
10 12.54 0.00 商家A 产品B 孙浩 12.54
11 0.00 31.56 商家B 产品A 孙浩 -61.31
12 7.00 0.00 商家A 产品C 孙浩 7.00
13 88.00 0.00 商家C 产品C 孙浩 88.00
14 0.00 37.08 商家C 产品A 王红 -37.08
15 0.00 34.90 商家A 产品A 刘明 -34.90
16 4.00 0.00 商家B 产品B 刘明 4.00
17 0.00 30.14 商家A 产品C 刘明 -30.14
(17 row(s) affected)
-- 这个表里有个自增主键:ID的
-- 这是我想要的结果
购入量 使用量 商家 产品类型 姓名
---------------------------------------
12.3 0 商家A 产品A 王红 12.3
0 28.45 商家A 产品C 王红 -28.45
45 0 商家A 产品A 王红 57.3
101.2 0 商家A 产品B 刘明 101.2
0 27.25 商家B 产品A 王红 -27.25
70 0 商家C 产品A 刘明 70
0 33.15 商家B 产品C 刘明 -33.15
0 29.75 商家B 产品A 孙浩 -29.75
0 37.67 商家C 产品B 王红 -37.67
12.54 0 商家A 产品B 孙浩 12.54
0 31.56 商家B 产品A 孙浩 -61.31
7.0 0 商家A 产品C 孙浩 7
88 0 商家C 产品C 孙浩 88
0 37.08 商家C 产品A 王红 37.08
0 34.90 商家A 产品A 刘明 34.90
4 0 商家B 产品B 刘明 4
0 30.14 商家B 产品B 刘明 -26.14
-- 最后按ID排序
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([购入量] numeric(5,2),[使用量] numeric(4,2),[商家] varchar(5),[产品类型] varchar(5),[姓名] varchar(4))
insert [tb]
select 12.3,0,'商家A','产品A','王红' union all
select 0,28.45,'商家A','产品C','王红' union all
select 45,0,'商家A','产品A','王红' union all
select 101.2,0,'商家A','产品B','刘明' union all
select 0,27.25,'商家B','产品A','王红' union all
select 70,0,'商家C','产品A','刘明' union all
select 0,33.15,'商家B','产品C','刘明' union all
select 0,29.75,'商家B','产品A','孙浩' union all
select 0,37.67,'商家C','产品B','王红' union all
select 12.54,0,'商家A','产品B','孙浩' union all
select 0,31.56,'商家B','产品A','孙浩' union all
select 7.0,0,'商家A','产品C','孙浩' union all
select 88,0,'商家C','产品C','孙浩' union all
select 0,37.08,'商家C','产品A','王红' union all
select 0,34.90,'商家A','产品A','刘明' union all
select 4,0,'商家B','产品B','刘明' union all
select 0,30.14,'商家A','产品C','刘明'
select sum(购入量) 购入量,sum(使用量) 使用量,商家,产品类型,姓名,sum(购入量)-sum(使用量) 剩余量
from tb
group by 商家,产品类型,姓名
/*
购入量 使用量 商家 产品类型 姓名 剩余量
0.00 34.90 商家A 产品A 刘明 -34.90
57.30 0.00 商家A 产品A 王红 57.30
101.20 0.00 商家A 产品B 刘明 101.20
12.54 0.00 商家A 产品B 孙浩 12.54
0.00 30.14 商家A 产品C 刘明 -30.14
7.00 0.00 商家A 产品C 孙浩 7.00
0.00 28.45 商家A 产品C 王红 -28.45
0.00 61.31 商家B 产品A 孙浩 -61.31
0.00 27.25 商家B 产品A 王红 -27.25
4.00 0.00 商家B 产品B 刘明 4.00
0.00 33.15 商家B 产品C 刘明 -33.15
70.00 0.00 商家C 产品A 刘明 70.00
0.00 37.08 商家C 产品A 王红 -37.08
0.00 37.67 商家C 产品B 王红 -37.67
88.00 0.00 商家C 产品C 孙浩 88.00
*/