[出题了]2008年9月25日 - SQL Server论坛

得分:0

--获得时间区域
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 楼

得分:0

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 楼

得分:0

学习中。。。数据库真好玩，又找到好玩的了

学习需要安静。。海盗要重新来过。。

第 24 楼

得分:0

路过下...

第 25 楼

得分:0

好玩就多玩玩哈

爱上你是我的错可是离开又舍不得听着你为我写的歌好难过
如果说我说如果我们还能重新来过不去计较谁对谁错会怎么做

第 26 楼

得分:0

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 楼

得分:0

结果：
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 楼

得分:0

[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 楼

得分:0

呵呵再盗版你的不会追究版权吧
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 楼

得分:0

楼主!
2008.2.2-2008.3.4之间为IN的和是不是漏加了一个22呀!
我怎么算来算去都是65!!(其他的楼的高手解答也是65!!!)
楼主再确认一下如何!