日期:2014-05-18  浏览次数:20714 次

如何对非连续记录相减
表1
  A B C
1 a 10
2 b 11
3 b 13
4 b 15
5 c 16
6 a 18
7 c 20
8 b 25
9 a 31
10 c 34
11 b 38
12 a 39
13 a 45
14 a 47
15 c 49
16 c 55
请教用SELECT语句如何查询字段B中,a、b、c对应的字段C值的变动

------解决方案--------------------
是这个意思吗?
SQL code

create table tb
(A int,B varchar(1),c int)

insert into tb 
select 1, 'a', 10 union all
select 2, 'b', 11 union all
select 3, 'b', 13 union all
select 4, 'b', 15 union all
select 5, 'c', 16 union all
select 6, 'a', 18 union all
select 7, 'c', 20 union all
select 8, 'b', 25 union all
select 9, 'a', 31 union all
select 10, 'c', 34 union all
select 11, 'b', 38 union all
select 12, 'a', 39 union all
select 13, 'a', 45 union all
select 14, 'a', 47 union all
select 15, 'c', 49 union all
select 16, 'c', 55 

select *,(select top 1 c from tb b where a.B =b.B and a.c<b.c order by b.B ,b.c) -a.c  from tb a

------解决方案--------------------
需要得到的结果是什么?
------解决方案--------------------
SQL code

if object_id('tb') is not null
    drop table tb
create table tb
(A int,B varchar(1),c int)

insert into tb 
select 1, 'a', 10 union all
select 2, 'b', 11 union all
select 3, 'b', 13 union all
select 4, 'b', 15 union all
select 5, 'c', 16 union all
select 6, 'a', 18 union all
select 7, 'c', 20 union all
select 8, 'b', 25 union all
select 9, 'a', 31 union all
select 10, 'c', 34 union all
select 11, 'b', 38 union all
select 12, 'a', 39 union all
select 13, 'a', 45 union all
select 14, 'a', 47 union all
select 15, 'c', 49 union all
select 16, 'c', 55 

select a.*, 
    a.C - isnull(b.C,0) as delta
from tb a
left join tb b on b.B = a.B
    and b.A < a.A
    and not exists (
        select 1 from tb c
        where c.B = a.B
        and c.A < a.A
        and c.A > b.A)

------解决方案--------------------
SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
    DROP TABLE tba
END
GO
CREATE TABLE tba
(
    A int,
    B varchar(1),
    C int
)
GO
insert into tba 
select 1, 'a', 10 union all
select 2, 'b', 11 union all
select 3, 'b', 13 union all
select 4, 'b', 15 union all
select 5, 'c', 16 union all
select 6, 'a', 18 union all
select 7, 'c', 20 union all
select 8, 'b', 25 union all
select 9, 'a', 31 union all
select 10, 'c', 34 union all
select 11, 'b', 38 union all
select 12, 'a', 39 union all
select 13, 'a', 45 union all
select 14, 'a', 47 union all
select 15, 'c', 49 union all
select 16, 'c', 55

SELECT A,B,C,C - ISNULL((SELECT TOP 1 C FROM tba WHERE B = t.B AND A < t.A ORDER BY A DESC),0) FROM tba AS t ORDER BY B,A

A    B    C    (No column name)
1    a    10    10
6    a    18    8
9    a    31    13
12    a    39    8
13    a    45    6
14    a    47    2
2    b    11    11
3    b    13    2
4    b    15    2
8    b    25    10
11    b    38    13
5    c    16    16
7    c    20    4
10    c    34    14
15    c    49    15
16    c    55    6