create table tb( id int,PNO varchar(10), 库存 int, 使用库存 int)
insert into tb values(1,'P001' , 2000 , 500 )
insert into tb values(2,'P001' , 2000 , 300 )
insert into tb values(3,'P001' , 2000 , 100 )
insert into tb values(4,'P002' , 3000 , 1500 )
insert into tb values(5,'P002' , 3000 , 1000 )
insert into tb values(6,'P002' , 3000 , 500 )
goselect *,
剩余库存 = (select top 1 库存 from tb where pno = t.pno) -
(select sum(使用库存) from tb where pno = t.pno and id <= t.id )
from tb tdrop table tb/*
id PNO 库存 使用库存 剩余库存
----------- ---------- ----------- ----------- -----------
1 P001 2000 500 1500
2 P001 2000 300 1200
3 P001 2000 100 1100
4 P002 3000 1500 1500
5 P002 3000 1000 500
6 P002 3000 500 0(所影响的行数为 6 行)*/
insert into tb values(1,'P001' , 2000 , 500 )
insert into tb values(2,'P001' , 2000 , 300 )
insert into tb values(3,'P001' , 2000 , 100 )
insert into tb values(4,'P002' , 3000 , 1500 )
insert into tb values(5,'P002' , 3000 , 1000 )
insert into tb values(6,'P002' , 3000 , 500 )
goselect *,
剩余库存 = (select top 1 库存 from tb where pno = t.pno) -
(select sum(使用库存) from tb where pno = t.pno and id <= t.id )
from tb tdrop table tb/*
id PNO 库存 使用库存 剩余库存
----------- ---------- ----------- ----------- -----------
1 P001 2000 500 1500
2 P001 2000 300 1200
3 P001 2000 100 1100
4 P002 3000 1500 1500
5 P002 3000 1000 500
6 P002 3000 500 0(所影响的行数为 6 行)*/
解决方案 »
- [请教]SQL Update语句 把查询结果链接起来合并成一个字段
- C/S模式下连接SQL2000数据库的问题C#
- 帮个忙,SQL~~
- 关于SQL Server 2008 安装中服务帐户选择的问题?
- sqlserver数据库过几天就无法连接
- 请问个密码的问题
- 恳求大侠回答问题:关于SQL Server2005中数据库镜像功能“无法”实现故障自动转移。
- 我想在SQLserver中实现一个函数,能够将10进制转化为36进制
- 我一安装SQL Server2000就失败,提示“安装程序”配置服务器失败......"?
- 还原 数据库错误?
- 表变量不能用在sql字符串当中?
- 关于 sql2000 动态触发器的问题
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([PNO] varchar(4),[库存] int,[使用库存] int)
insert [tb]
select 'P001',2000,500 union all
select 'P001',2000,300 union all
select 'P001',2000,100 union all
select 'P002',3000,1500 union all
select 'P002',3000,1000 union all
select 'P002',3000,500
---查询---
select tid=identity(int,1,1),* into # from tb select
PNO,
库存,
使用库存,
剩余库存=库存-(select sum(使用库存) from # where PNO=t.PNO and tid<=t.tid)
from # tdrop table #---结果---
PNO 库存 使用库存 剩余库存
---- ----------- ----------- -----------
P001 2000 500 1500
P001 2000 300 1200
P001 2000 100 1100
P002 3000 1500 1500
P002 3000 1000 500
P002 3000 500 0(所影响的行数为 6 行)
declare @t table(PNO varchar(10),库存 int,使用库存 int)
insert into @t select 'P001',2000, 500 --1500
insert into @t select 'P001',2000, 300 --1200
insert into @t select 'P001',2000, 100 --1100
insert into @t select 'P002',3000,1500 --1500
insert into @t select 'P002',3000,1000 -- 500
insert into @t select 'P002',3000, 500 -- 0 select identity(int,1,1) as ID,* into # from @tselect
PNO,库存,使用库存,剩余库存=库存-(select sum(使用库存) from # where PNO=t.PNO and id<=t.id)
from
# t/*
PNO 库存 使用库存 剩余库存
---------- ----------- ----------- -----------
P001 2000 500 1500
P001 2000 300 1200
P001 2000 100 1100
P002 3000 1500 1500
P002 3000 1000 500
P002 3000 500 0
*/drop table #
go
create table [tb]([PNO] varchar(4),[库存] int,[使用库存] int)
insert [tb]
select 'P001',2000,500 union all
select 'P001',2000,300 union all
select 'P001',2000,100 union all
select 'P002',3000,1500 union all
select 'P002',3000,1000 union all
select 'P002',3000,500--select * from [tb]select id=identity(int),* into # from tbselect [PNO],[库存],[使用库存]
,[剩余库存]=[库存]-(select sum([使用库存]) from # where pno=t.pno and id<=t.id)
from # t
--测试结果:
/*
id PNO 库存 使用库存 剩余库存
----------- ---- ----------- ----------- -----------
1 P001 2000 500 1500
2 P001 2000 300 1200
3 P001 2000 100 1100
4 P002 3000 1500 1500
5 P002 3000 1000 500
6 P002 3000 500 0(6 行受影响)
*/
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (PNO VARCHAR(4),库存 INT,使用库存 INT)
INSERT INTO @T
SELECT 'P001',2000,500 UNION ALL
SELECT 'P001',2000,300 UNION ALL
SELECT 'P001',2000,100 UNION ALL
SELECT 'P002',3000,1500 UNION ALL
SELECT 'P002',3000,1000 UNION ALL
SELECT 'P002',3000,500--SQL查询如下:;WITH Liang AS
(
SELECT PNO,库存,使用库存,rnk=ROW_NUMBER() OVER(ORDER BY GETDATE())
FROM @T
)
SELECT
PNO,库存,使用库存,
剩余库存=库存-(SELECT SUM(使用库存)
FROM Liang
WHERE PNO=A.PNO
AND rnk<=A.rnk)
FROM Liang AS A/*
PNO 库存 使用库存 剩余库存
---- ----------- ----------- -----------
P001 2000 500 1500
P001 2000 300 1200
P001 2000 100 1100
P002 3000 1500 1500
P002 3000 1000 500
P002 3000 500 0(6 行受影响)*/