现在有三个表,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]]

路过SQL区……

占个座，剪完指甲给在搞

这个不行？

[[it] 本帖最后由 卜酷塔 于 2008-9-26 14:09 编辑 [/it]]

师姐，你那个结果是对的吗，是不是我哪里没弄懂，下面这条结果的43 numin值我觉的有点问题呢

date        num  tag  numin   numout
2008.3.4    65    3    43       88   (日期在2008.2.2-2008.3.4之间的tabel2,tabel3中的num1,num2的和)

首先在2008.2.2-2008.3.4的IN值有3条记录：
id           num1     date               type
1             22         2008.2.4         in
4            22          2008.2.21        in
4            21          2008.2.5         in

3个之和的话，应该是65 呢，是不是我哪里错了？！

43没错的

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

SELECT SUM(num1) FROM
(
SELECT num1,type,date FROM t2
UNION ALL
SELECT num2,type,date FROM t3
) a WHERE type='in' AND date BETWEEN '2008-02-02' AND '2008-03-04'

65

SELECT SUM(num1) FROM
(
SELECT num1,type,date FROM t2
UNION ALL
SELECT num2,type,date FROM t3
) a WHERE type='out' AND date BETWEEN '2008-02-02' AND '2008-03-04'

88

[[it] 本帖最后由 球球 于 2008-9-26 13:00 编辑 [/it]]

SELECT SUM(num1) FROM
(
SELECT num1,type,date FROM t2
UNION ALL
SELECT num2,type,date FROM t3
) a WHERE type='in' AND date BETWEEN '2008-03-04' AND '2008-11-02'

122

SELECT sum(num1) FROM
(
SELECT * FROM t2
UNION all
SELECT * FROM t3
) a WHERE type='out' AND date BETWEEN '2008-03-04' AND '2008-11-02'

21

这样统计好象有点问题，上一天的又加到下个月去了？

球球加我QQ

加了，没反应。我在你群里。昨晚半夜还问你问题来着。记起没