create table pooling(a datetime,b varchar(10),c int)insert into pooling values('20060224','n01',33)
insert into pooling values('20060225','n01',32)
insert into pooling values('20060227','n01',45)
insert into pooling values('20060227','n02',45)
-----
select a.a,b.a ,a.b ,(case when a.a=b.a then a.c+b.c when a.a<>b.a then a.c end)
from pooling a,pooling b
where (a.b=b.b and (a.a<b.a and datediff(dd,a.a,b.a)=1 )) or(a.b=b.b and a.a=b.a and dateadd(dd,1,a.a) not in (select c.a from pooling c) and dateadd(dd,-1,a.a) not in (select d.a from pooling d) )
insert into pooling values('20060225','n01',32)
insert into pooling values('20060227','n01',45)
insert into pooling values('20060227','n02',45)
-----
select a.a,b.a ,a.b ,(case when a.a=b.a then a.c+b.c when a.a<>b.a then a.c end)
from pooling a,pooling b
where (a.b=b.b and (a.a<b.a and datediff(dd,a.a,b.a)=1 )) or(a.b=b.b and a.a=b.a and dateadd(dd,1,a.a) not in (select c.a from pooling c) and dateadd(dd,-1,a.a) not in (select d.a from pooling d) )
解决方案 »
- 多线程“同步”
- 新手求救!2005建表
- 怎麼用script修改已經有數據的表呢,例如primary Key, Default....
- 求教如何获得这种日期格式。
- 求一句最后登录时间的语句
- 打开数据库找不到对象msysdb的问题
- 我的sql2000 小问题!!!新建数据库!!!?〉???
- why????God help me!!pls
- 对于表间关联的疑问
- 无法执行该操作,因为链接服务器 "srv2" 的 OLE DB 访问接口 "SQLNCLI10" 无法启动分布式事务
- (搞了一天)假若一个用户名 同时在两台机器上登录域会出现此问题么!
- SQL语句问题:如何实现把另一个表中符合某个条件的结果集的某个字段显示在一条记录中?
create table pooling(a datetime,b varchar(10),c int)
insert into pooling values('20060224','n01',33)
insert into pooling values('20060225','n01',32)
insert into pooling values('20060227','n01',45)
insert into pooling values('20060227','n02',45)
-----
select a.a,b.a ,a.b ,(case when a.a<>b.a then a.c+b.c when a.a=b.a then a.c end)
from pooling a,pooling b
where (a.b=b.b and (a.a<b.a and datediff(dd,a.a,b.a)=1 )) or(a.b=b.b and a.a=b.a and dateadd(dd,1,a.a) not in (select c.a from pooling c) and dateadd(dd,-1,a.a) not in (select d.a from pooling d) )结果: a b
------------------------------------------------------ ------------------------------------------------------ ---------- -----------
2006-02-24 00:00:00.000 2006-02-25 00:00:00.000 n01 65
2006-02-27 00:00:00.000 2006-02-27 00:00:00.000 n01 45
2006-02-27 00:00:00.000 2006-02-27 00:00:00.000 n02 45