注册 登录
编程论坛 → SQL Server论坛

[出题了]2008年9月25日

师妃暄 发布于 2008-09-25 22:19， 3505 次点击
现在有三个表,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]]
30 回复
#2
liyanhong2008-09-25 22:47
路过SQL区……
#3
卜酷塔2008-09-26 08:18
占个座，剪完指甲给在搞
[bo][un]球球[/un] 在 2008-9-26 13:03 的发言：[/bo]

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
 ...

这个不行？

[[it] 本帖最后由 卜酷塔 于 2008-9-26 14:09 编辑 [/it]]
#4
szy3452008-09-26 12:19
师姐，你那个结果是对的吗，是不是我哪里没弄懂，下面这条结果的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 呢，是不是我哪里错了？！
#5
师妃暄2008-09-26 12:48
43没错的

2008.2.2-2008.3.4之间为IN的  t2中num1为22 t3中num2为21
#6
球球2008-09-26 12:57
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]]
#7
球球2008-09-26 13:03
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
#8
球球2008-09-26 13:06
这样统计好象有点问题，上一天的又加到下个月去了？
#9
师妃暄2008-09-26 13:08
球球加我QQ
#10
球球2008-09-26 13:33
加了，没反应。我在你群里。昨晚半夜还问你问题来着。记起没
#11
szy3452008-09-26 14:20
select num1+num2 as num ,a.type
 from  
(select sum(num1)as num1,type  from t2  
where t2.date >'2008-2-2'and t2.date <'2008-3-4'
group by type) as a
join
(select sum(num2)as num2,type  from t3  
where t3.date >'2008-2-2'and t3.date <'2008-3-4'
group by type )as b
on a.type=b.type

*************
num  type
65   in
88   out
*************

select num1+num2 as num ,a.type
 from  
(select sum(num1)as num1,type  from t2  
where t2.date >'2008-3-4'and t2.date <'2008-11-02'
group by type) as a
join
(select sum(num2)as num2,type  from t3  
where t3.date >'2008-3-4'and t3.date <'2008-11-02'
group by type )as b
on a.type=b.type


*************
num  type
122   in
21   out
*************
#12
西风独自凉2008-09-26 14:23
select a.*,
numin=(select sum(num1)  from table2 where sdate between  (select top 1  sdate from table1 where sdate <a.sdate order by sdate desc ) and  a.sdate  group by type having type ='in' )
+ (select sum(num2)  from table3 where sdate between   (select top 1  sdate from table1 where sdate <a.sdate order by sdate desc ) and  a.sdate   group by type having type ='in' ),
numout=(select sum(num1)  from table2 where sdate between  (select top 1  sdate from table1 where sdate <a.sdate order by sdate desc ) and  a.sdate  group by type having type ='out' )
+ (select sum(num2)  from table3 where sdate between   (select top 1  sdate from table1 where sdate <a.sdate order by sdate desc ) and  a.sdate   group by type having type ='out' )
 from Table1 a     這句搞定
#13
西风独自凉2008-09-26 14:24
回复 11# szy345 的帖子
這樣日期被你寫死了﹐有很多數據怎么辦
#14
球球2008-09-26 14:33
总有一天，我得把你面纱给揭了。
#15
西风独自凉2008-09-26 14:42
﹖﹖﹖﹖﹖ 說我
#16
球球2008-09-26 14:42
说妃子
#17
szy3452008-09-26 14:47
#18
师妃暄2008-09-26 14:50
西风版主来了就别跑了..你可以了上任
#19
西风独自凉2008-09-26 14:52
干嘛
#20
网易2008-09-26 16:44
那个头像的确挺漂亮的
#21
seiya0278482008-09-26 17:31
--获得时间区域
select a.date as startdate,b.date as enddate,b.tag from t1 as a,t1 as b
where a.tag=b.tag-1

--获得时间区域表
select t1.date,t1.num,t1.tag,dtrang.startdate from t1 left join (
select a.date as startdate,b.date as enddate,b.tag from t1 as a,t1 as b
where a.tag=b.tag-1
) dtrang on t1.tag=dtrang.tag

--获得数据集合
select * from t2
union all
select * from t3

--获得所需要的数据
select dataset.type as datatype,dataset.num1,timesheet.startdate,timesheet.date as enddate,timesheet.tag from
(select t1.date,t1.num,t1.tag,dtrang.startdate from t1 left join (
select a.date as startdate,b.date as enddate,b.tag from t1 as a,t1 as b
where a.tag=b.tag-1
) dtrang on t1.tag=dtrang.tag) as timesheet
,(select * from t2 union all select * from t3) dataset
where dataset.date between timesheet.startdate and timesheet.date

--凑结构
select date,num,t1.tag,
sum(case when datatype='in' then num1 end) as numin,
sum(case when datatype='out' then num1 end) as numout from t1
left join (
select dataset.type as datatype,dataset.num1,timesheet.startdate,timesheet.date as enddate,timesheet.tag from
(select t1.date,t1.num,t1.tag,dtrang.startdate from t1 left join (
select a.date as startdate,b.date as enddate,b.tag from t1 as a,t1 as b
where a.tag=b.tag-1
) dtrang on t1.tag=dtrang.tag) as timesheet
,(select * from t2 union all select * from t3) dataset
where dataset.date between timesheet.startdate and timesheet.date
) as result on t1.tag=result.tag
group by date,num,t1.tag

结果：
只有本站会员才能查看附件，请 登录
#22
师妃暄2008-09-26 17:42
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的呢
#23
sunkaidong2008-09-26 19:19
学习中。。。数据库真好玩，又找到好玩的了
#24
Ethip2008-09-26 21:14
路过下...
#25
liyanhong2008-09-26 21:34
好玩就多玩玩哈
#26
happynight2008-09-27 15:25
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');


/*
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的和)
*/
SELECT * FROM t1 order by date
SELECT * FROM t2 order by date
SELECT * FROM t3 order by date

SELECT date,num,tag,
       (SELECT SUM(CASE TYPE WHEN 'IN' THEN num1 ELSE 0 END)
        FROM t2  INNER JOIN
             (SELECT MAX(date) AS Ndate FROM t1 a WHERE  t1.date>a.date) a
              ON t2.date>a.Ndate
        WHERE t2.date<=t1.date)
       +  
       (SELECT SUM(CASE TYPE WHEN 'IN' THEN num2 ELSE 0 END)
        FROM t3  INNER JOIN
             (SELECT MAX(date) AS Ndate FROM t1 a WHERE  t1.date>a.date) a
              ON t3.date>a.Ndate
        WHERE t3.date<=t1.date) AS numin,
      
        (SELECT SUM(CASE TYPE WHEN 'OUT' THEN num1 ELSE 0 END)
        FROM t2  INNER JOIN
             (SELECT MAX(date) AS Ndate FROM t1 a WHERE  t1.date>a.date) a
              ON t2.date>a.Ndate
        WHERE t2.date<=t1.date)
       +  
       (SELECT SUM(CASE TYPE WHEN 'OUT' THEN num2 ELSE 0 END)
        FROM t3  INNER JOIN
             (SELECT MAX(date) AS Ndate FROM t1 a WHERE  t1.date>a.date) a
              ON t3.date>a.Ndate
        WHERE t3.date<=t1.date) AS numout
FROM t1  

DROP TABLE t1
DROP TABLE t2
DROP TABLE t3
#27
happynight2008-09-27 15:29
结果：
date                       num      tag      numin    numout
2008-02-02 00:00:00.000    34    2    NULL    NULL
2008-03-04 00:00:00.000    65    3    65    88
2008-11-02 00:00:00.000    33    4    122    21

2008-02-02   和   2008-03-04 之间
t2:
1             22         2008.2.4         in
4            22          2008.2.21      in

t3:
4            21          2008.2.5        in

一共是65
你应该是漏了t2中的数据了
#28
happynight2008-09-27 15:49
[bo][un]西风独自凉[/un] 在 2008-9-26 14:23 的发言：[/bo]

select a.*,
numin=(select sum(num1)  from table2 where sdate between  (select top 1  sdate from table1 where sdate  

高
不过你的表结构和LZ的有出入 改了下
select a.*,
numin=(select sum(num1)  from t2
       where date between  
        (select top 1  date from t1 where date <a.date order by date desc )
         and  a.date  group by type having type ='in' )
    + (select sum(num2)  from t3
       where date between   
         (select top 1  date from t1 where date <a.date order by date desc )
          and  a.date   group by type having type ='in' ),

numout=(select sum(num1)  from t2
        where date between  
        (select top 1  date from t1 where date <a.date order by date desc )
         and  a.date  group by type having type ='out' )
        + (select sum(num2)  from t3 where date between   
           (select top 1  date from t1 where date <a.date order by date desc )
          and  a.date   group by type having type ='out' )
from t1 a
#29
happynight2008-09-27 15:53
呵呵 再盗版你的 不会追究版权吧
select a.*,
numin=(select sum(num1)  from t2
       where date between  
        (select top 1  date from t1 where date <a.date order by date desc )
         and  a.date  AND type ='in'  group by type )
    + (select sum(num2)  from t3
       where date between   
         (select top 1  date from t1 where date <a.date order by date desc )
          and  a.date   AND type ='in'  group by type ),

numout=(select sum(num1)  from t2
        where date between  
        (select top 1  date from t1 where date <a.date order by date desc )
         and  a.date  AND type ='out'  group by type  )
        + (select sum(num2)  from t3 where date between   
           (select top 1  date from t1 where date <a.date order by date desc )
          and  a.date   AND type ='out'  group by type   )
from t1 a
--------------------------
修改下我的
SELECT date,num,tag,
       (SELECT SUM( num1)
        FROM t2  INNER JOIN
             (SELECT MAX(date) AS Ndate FROM t1 a WHERE  t1.date>a.date) a
              ON t2.date>a.Ndate
        WHERE t2.date<=t1.date AND t2.TYPE='IN')
       +  
       (SELECT SUM(num2)
        FROM t3  INNER JOIN
             (SELECT MAX(date) AS Ndate FROM t1 a WHERE  t1.date>a.date) a
              ON t3.date>a.Ndate
        WHERE t3.date<=t1.date  AND t3.TYPE='IN') AS numin,
      
        (SELECT SUM(num1)
        FROM t2  INNER JOIN
             (SELECT MAX(date) AS Ndate FROM t1 a WHERE  t1.date>a.date) a
              ON t2.date>a.Ndate
        WHERE t2.date<=t1.date AND t2.TYPE='OUT')
       +  
       (SELECT SUM(num2)
        FROM t3  INNER JOIN
             (SELECT MAX(date) AS Ndate FROM t1 a WHERE  t1.date>a.date) a
              ON t3.date>a.Ndate
        WHERE t3.date<=t1.date AND t3.TYPE='OUT') AS numout
FROM t1

[[it] 本帖最后由 happynight 于 2008-9-27 15:57 编辑 [/it]]
#30
jackesing2008-09-28 16:22
楼主!
2008.2.2-2008.3.4之间为IN的和是不是漏加了一个22呀!
我怎么算来算去都是65!!(其他的楼的高手解答也是65!!!)
楼主再确认一下如何!
#31
Vitamin19992009-10-15 14:12
2008.2.2-2008.3.4之间为IN的  t2中num1为44啊
结果有问题啊
1
©2025, BC-CN.NET
电脑版