表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值的变动
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值的变动
(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
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)
1 a 10 10
2 b 11 11
3 b 13 2
4 b 15 2
5 c 16 16
6 a 18 8
7 c 20 4
8 b 25 10
9 a 31 13
10 c 34 14
11 b 38 13
12 a 39 8
13 a 45 6
14 a 47 2
15 c 49 15
16 c 55 6
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', 55SELECT 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,AA 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