if object_id('tb') is not null drop table tb go create table tb(PTID int, P1 int, P2 int,Month int) insert tb select 1, 3000 , 1100, 6 union all select 1, 2100 , 3000, 7 union all select 1, 1050 , 1300, 8 union all select 1, 700 , 2400, 9 union all select 1, 1050 , 1200 , 10 select a.PTID, 存货=case when a.p1-b.p2>0 then 0 else a.p1-b.p2 end, a.month from tb a,tb b where a.month=b.month+1 PTID 存货 month ----------- ----------- ----------- 1 0 7 1 -1950 8 1 -600 9 1 -1350 10(4 行受影响)
-->Title:Generating test data -->Author:happy_stone【不會飛的石頭】 -->Date :2009-10-21 13:17:11
if not object_id('Tempdb..#t') is null drop table #t Go Create table #t([PTID] int,[P1] int,[P2] int,[Month] int) Insert #t select 1,3000,1100,6 union all select 1,2100,3000,7 union all select 1,1050,1300,8 union all select 1,700,2400,9 union all select 1,1050,1200,10 Go Select a.[PTID], a.[P1]-b.[P2] from #t a,#t b where a.[PTID]=b.[PTID] and a.[Month]=b.[Month]+1 /* PTID ----------- ----------- 1 1000 1 -1950 1 -600 1 -1350(4 個資料列受到影響) */
--> 生成测试数据: @tb DECLARE @tb TABLE (PTID int,P1 int,P2 int,Month int) INSERT INTO @tb SELECT 1,3000,1100,6 UNION ALL SELECT 1,2100,3000,7 UNION ALL SELECT 1,1050,1300,8 UNION ALL SELECT 1,700,2400,9 UNION ALL SELECT 1,1050,1200,10--SQL查询如下:SELECT PTID,Month, CASE WHEN P1-(SELECT TOP 1 P2 FROM @tb WHERE Month < A.Month ORDER BY Month DESC) > 0 THEN 0 ELSE P1-(SELECT TOP 1 P2 FROM @tb WHERE Month < A.Month ORDER BY Month DESC) END AS 存货 FROM @tb AS A WHERE EXISTS(SELECT * FROM @tb WHERE Month < A.Month)/* PTID Month 存货 ----------- ----------- ----------- 1 7 0 1 8 -1950 1 9 -600 1 10 -1350(4 行受影响)*/
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-21 13:21:58 -- 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.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([PTID] int,[P1] int,[P2] int,[Month] int) insert [tb] select 1,3000,1100,6 union all select 1,2100,3000,7 union all select 1,1050,1300,8 union all select 1,700,2400,9 union all select 1,1050,1200,10 --------------开始查询-------------------------- select a.PTID, (case when a.p1-b.p2>0 then 0 else a.p1-b.p2 end) as 存货, a.[month] from tb a join tb b on a.[month]=b.[month]+1 ----------------结果---------------------------- /* PTID 存货 month ----------- ----------- ----------- 1 0 7 1 -1950 8 1 -600 9 1 -1350 10(4 行受影响)*/
--修正 -->Title:Generating test data -->Author:happy_stone【不會飛的石頭】 -->Date :2009-10-21 13:17:11
if not object_id('Tempdb..#t') is null drop table #t Go Create table #t([PTID] int,[P1] int,[P2] int,[Month] int) Insert #t select 1,3000,1100,6 union all select 1,2100,3000,7 union all select 1,1050,1300,8 union all select 1,700,2400,9 union all select 1,1050,1200,10 Go Select a.[PTID], case when a.[P1]-b.[P2]>0 then 0 else a.[P1]-b.[P2] end 存货, a.[Month] from #t a,#t b where a.[PTID]=b.[PTID] and a.[Month]=b.[Month]+1 /* PTID 存货 Month ----------- ----------- ----------- 1 0 7 1 -1950 8 1 -600 9 1 -1350 10(4 個資料列受到影響) */
if object_ID('tb') is not null drop table tb go create table tb(PTID int, P1 int, P2 int, Month int) go insert into tb select 1 ,3000, 1100, 6 union all select 1 ,2100, 3000, 7 union all select 1 ,1050, 1300, 8 union all select 1 ,700 , 2400, 9 union all select 1 ,1050, 1200, 10 goselect t1.ptid, 存货=case when (t1.p1-t2.p2)>0 then 0 else t1.p1-t2.p2 end, t1.month from tb t1 left join tb t2 on t1.month=t2.month+1 where t2.p2 is not null /*ptid 存货 month ----------- ----------- ----------- 1 0 7 1 -1950 8 1 -600 9 1 -1350 10*/
select ptid, case when a.p1-b.p2>0 then 0 else a.p1-b.p2 end as 存货, month from tb as a,tb as b where a.month=b.month+1
select t1.ptid, 存货=case when (t1.p1-t2.p2)>0 then 0 else t1.p1-t2.p2 end, t1.month from tb t1 left join tb t2 on t1.month=t2.month+1 where t2.p2 is not null
试了好久,我们用的是Access 好像没有 case 语句
select ptid, '0' as 存货, month from tb as a,tb as b where a.month=b.month+1 and a.p1-b.p2>0 union all select ptid, a.p1-b.p2 as 存货, month from tb as a,tb as b where a.month=b.month+1 and a.p1-b.p2<=0
select a.PTID, iif(a.p1-b.p2>0 ,0,a.p1-b.p2) as 存货, a.[month] from tb a join tb b on a.[month]=b.[month]+1
if object_id('tb') is not null drop table tb go create table tb(PTID int, P1 int, P2 int,Month int) insert tb select 1, 3000 , 1100, 6 union all select 1, 2100 , 3000, 7 union all select 1, 1050 , 1300, 8 union all select 1, 700 , 2400, 9 union all select 1, 1050 , 1200 , 10 select a.PTID,num= a.p1-b.p2,a.month into #t from tb a,tb b where a.month=b.month+1 update #t set num=0 ---那就再更新一下 where num>0 select * from #t PTID num month ----------- ----------- ----------- 1 0 7 1 -1950 8 1 -600 9 1 -1350 10(4 行受影响)drop table #t
if object_id('tb') is not null drop table tb
go
create table tb(PTID int, P1 int, P2 int,Month int)
insert tb select
1, 3000 , 1100, 6 union all select
1, 2100 , 3000, 7 union all select
1, 1050 , 1300, 8 union all select
1, 700 , 2400, 9 union all select
1, 1050 , 1200 , 10
select
a.PTID,
存货=case when a.p1-b.p2>0 then 0 else a.p1-b.p2 end,
a.month
from tb a,tb b
where a.month=b.month+1 PTID 存货 month
----------- ----------- -----------
1 0 7
1 -1950 8
1 -600 9
1 -1350 10(4 行受影响)
-->Author:happy_stone【不會飛的石頭】
-->Date :2009-10-21 13:17:11
if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([PTID] int,[P1] int,[P2] int,[Month] int)
Insert #t
select 1,3000,1100,6 union all
select 1,2100,3000,7 union all
select 1,1050,1300,8 union all
select 1,700,2400,9 union all
select 1,1050,1200,10
Go
Select a.[PTID],
a.[P1]-b.[P2]
from #t a,#t b
where a.[PTID]=b.[PTID] and a.[Month]=b.[Month]+1
/*
PTID
----------- -----------
1 1000
1 -1950
1 -600
1 -1350(4 個資料列受到影響)
*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-21 13:20:38
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (PTID int,P1 int,P2 int,Month int)
INSERT INTO @tb
SELECT 1,3000,1100,6 UNION ALL
SELECT 1,2100,3000,7 UNION ALL
SELECT 1,1050,1300,8 UNION ALL
SELECT 1,700,2400,9 UNION ALL
SELECT 1,1050,1200,10--SQL查询如下:SELECT PTID,Month,
CASE WHEN P1-(SELECT TOP 1 P2 FROM @tb
WHERE Month < A.Month ORDER BY Month DESC) > 0 THEN 0
ELSE P1-(SELECT TOP 1 P2 FROM @tb
WHERE Month < A.Month ORDER BY Month DESC) END AS 存货
FROM @tb AS A
WHERE EXISTS(SELECT * FROM @tb WHERE Month < A.Month)/*
PTID Month 存货
----------- ----------- -----------
1 7 0
1 8 -1950
1 9 -600
1 10 -1350(4 行受影响)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-21 13:21:58
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([PTID] int,[P1] int,[P2] int,[Month] int)
insert [tb]
select 1,3000,1100,6 union all
select 1,2100,3000,7 union all
select 1,1050,1300,8 union all
select 1,700,2400,9 union all
select 1,1050,1200,10
--------------开始查询--------------------------
select
a.PTID,
(case when a.p1-b.p2>0 then 0 else a.p1-b.p2 end) as 存货,
a.[month]
from
tb a
join
tb b
on
a.[month]=b.[month]+1
----------------结果----------------------------
/* PTID 存货 month
----------- ----------- -----------
1 0 7
1 -1950 8
1 -600 9
1 -1350 10(4 行受影响)*/
-->Title:Generating test data
-->Author:happy_stone【不會飛的石頭】
-->Date :2009-10-21 13:17:11
if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([PTID] int,[P1] int,[P2] int,[Month] int)
Insert #t
select 1,3000,1100,6 union all
select 1,2100,3000,7 union all
select 1,1050,1300,8 union all
select 1,700,2400,9 union all
select 1,1050,1200,10
Go
Select a.[PTID],
case when a.[P1]-b.[P2]>0 then 0 else a.[P1]-b.[P2] end 存货,
a.[Month]
from #t a,#t b
where a.[PTID]=b.[PTID] and a.[Month]=b.[Month]+1
/*
PTID 存货 Month
----------- ----------- -----------
1 0 7
1 -1950 8
1 -600 9
1 -1350 10(4 個資料列受到影響)
*/
if object_ID('tb') is not null
drop table tb
go
create table tb(PTID int, P1 int, P2 int, Month int)
go
insert into tb select
1 ,3000, 1100, 6 union all select
1 ,2100, 3000, 7 union all select
1 ,1050, 1300, 8 union all select
1 ,700 , 2400, 9 union all select
1 ,1050, 1200, 10
goselect t1.ptid,
存货=case when (t1.p1-t2.p2)>0 then 0 else t1.p1-t2.p2 end,
t1.month
from tb t1
left join tb t2 on t1.month=t2.month+1
where t2.p2 is not null
/*ptid 存货 month
----------- ----------- -----------
1 0 7
1 -1950 8
1 -600 9
1 -1350 10*/
case when a.p1-b.p2>0 then 0 else a.p1-b.p2 end as 存货,
month
from tb as a,tb as b
where a.month=b.month+1
select t1.ptid,
存货=case when (t1.p1-t2.p2)>0 then 0 else t1.p1-t2.p2 end,
t1.month
from tb t1
left join tb t2 on t1.month=t2.month+1
where t2.p2 is not null
'0' as 存货,
month
from tb as a,tb as b
where a.month=b.month+1 and a.p1-b.p2>0
union all
select ptid,
a.p1-b.p2 as 存货,
month
from tb as a,tb as b
where a.month=b.month+1 and a.p1-b.p2<=0
a.PTID,
iif(a.p1-b.p2>0 ,0,a.p1-b.p2) as 存货,
a.[month]
from
tb a
join
tb b
on
a.[month]=b.[month]+1
if object_id('tb') is not null drop table tb
go
create table tb(PTID int, P1 int, P2 int,Month int)
insert tb select
1, 3000 , 1100, 6 union all select
1, 2100 , 3000, 7 union all select
1, 1050 , 1300, 8 union all select
1, 700 , 2400, 9 union all select
1, 1050 , 1200 , 10 select
a.PTID,num= a.p1-b.p2,a.month into #t
from tb a,tb b
where a.month=b.month+1 update #t set num=0 ---那就再更新一下
where num>0
select * from #t
PTID num month
----------- ----------- -----------
1 0 7
1 -1950 8
1 -600 9
1 -1350 10(4 行受影响)drop table #t