现在有三个表,Table1,Tabel2,Tabel3
tabel1如下:
date            num      tag
2008.2.2        34        2
2008.3.4        65        3
2008.11.2       33        4

tabel2如下:
id           num1     date               type
1             22         2008.2.4         in
2             44         2008.3.1         out
3             67         2008.9.1        in
4            22          2008.2.21      in
5            10          2008.5.3        out

tabel3如下:
id           num2     date               type

2            55         2008.3.6         in
3            44          2008.3.3        out
4            21          2008.2.5        in
5           11           2008.6.1         out
求tabel2 和tabel3中按type分组的num1 和num2的和,并分别作为tabel1中的两列.
得到如下结果:

date            num      tag    numin     numout
2008.2.2        34        2                                    (第一条没有上一条的时间,所以这两列为空)
2008.3.4        65        3       43               88       (日期在2008.2.2-2008.3.4之间的tabel2,tabel3中的num1,num2的和)
2008.11.2       33        4      122             21        (日期在2008.3.4-2008.11.2之间的tabel2,tabel3中的num1,num2的和)

一起探讨下..看谁能给出答案

初始化脚本

create table t1
(
    [date] datetime,
    num int,
    tag varchar(10)
);

create table t2
(
    [id] int identity(1,1) primary key not null,
    num1 int,
    [date] datetime,
    [type] char(8)
);

create table t3
(
    [id] int identity(1,1) primary key not null,
    num2 int,
    [date] datetime,
    [type] char(8)
);

insert into t1 values('2008-2-2',34,'2');
insert into t1 values('2008-3-4',65,'3');
insert into t1 values('2008-11-2',33,'4');

insert into t2 values(22,'2008-2-4','in');
insert into t2 values(44,'2008-3-1','out');
insert into t2 values(67,'2008-9-1','in');
insert into t2 values(22,'2008-2-21','in');
insert into t2 values(10,'2008-5-3','out');

insert into t3 values(55,'2008-3-6','in');
insert into t3 values(44,'2008-3-3','out');
insert into t3 values(21,'2008-2-5','in');
insert into t3 values(11,'2008-6-1','out');

[[it] 本帖最后由 师妃暄 于 2008-9-25 22:20 编辑 [/it]]

43没错的

2008.2.2-2008.3.4之间为IN的  t2中num1为22 t3中num2为21

球球加我QQ

西风版主来了就别跑了..你可以了上任

select a.date as startdate,b.date as enddate,b.tag from t1 as a,t1 as b
where a.tag=b.tag-1

where条件貌似不能这样..因为tag并不保证上下就相差1的呢