表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 allselect 2, 'b', 11 union allselect 3, 'b', 13 union allselect 4, 'b', 15 union allselect 5, 'c', 16 union allselect 6, 'a', 18 union allselect 7, 'c', 20 union allselect 8, 'b', 25 union allselect 9, 'a', 31 union allselect 10, 'c', 34 union allselect 11, 'b', 38 union allselect 12, 'a', 39 union allselect 13, 'a', 45 union allselect 14, 'a', 47 union allselect 15, 'c', 49 union allselect 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 tbcreate table tb(A int,B varchar(1),c int)insert into tb select 1, 'a', 10 union allselect 2, 'b', 11 union allselect 3, 'b', 13 union allselect 4, 'b', 15 union allselect 5, 'c', 16 union allselect 6, 'a', 18 union allselect 7, 'c', 20 union allselect 8, 'b', 25 union allselect 9, 'a', 31 union allselect 10, 'c', 34 union allselect 11, 'b', 38 union allselect 12, 'a', 39 union allselect 13, 'a', 45 union allselect 14, 'a', 47 union allselect 15, 'c', 49 union allselect 16, 'c', 55 select a.*, a.C - isnull(b.C,0) as deltafrom tb aleft 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 tbaENDGOCREATE TABLE tba( A int, B varchar(1), C int)GOinsert into tba select 1, 'a', 10 union allselect 2, 'b', 11 union allselect 3, 'b', 13 union allselect 4, 'b', 15 union allselect 5, 'c', 16 union allselect 6, 'a', 18 union allselect 7, 'c', 20 union allselect 8, 'b', 25 union allselect 9, 'a', 31 union allselect 10, 'c', 34 union allselect 11, 'b', 38 union allselect 12, 'a', 39 union allselect 13, 'a', 45 union allselect 14, 'a', 47 union allselect 15, 'c', 49 union allselect 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 106 a 18 89 a 31 1312 a 39 813 a 45 614 a 47 22 b 11 113 b 13 24 b 15 28 b 25 1011 b 38 135 c 16 167 c 20 410 c 34 1415 c 49 1516 c 55 6