create table #t (id int identity(1,1),caller varchar(1),called varchar(1),dt varchar(20),Ans varchar(1))
insert into #t values('a','b','2009-10-10 11:15:00','0')
insert into #t values('a','b','2009-10-10 11:17:00','1')
insert into #t values('e','b','2009-10-10 11:17:08','1')
insert into #t values('a','b','2009-10-10 11:20:00','0')
insert into #t values('a','b','2009-10-10 11:24:00','0')
insert into #t values('e','b','2009-10-10 11:24:08','0')
insert into #t values('e','b','2009-10-10 11:27:08','1')
insert into #t values('a','b','2009-10-10 11:29:00','0')
insert into #t values('a','b','2009-10-10 11:36:00','1')
go需求:
如果主叫呼叫被叫没有应答,查询同一主被应答与上次没有应答时间间隔大于1分钟的记录。目标结果:
ID caller called DT Ans
2 a b 2009-10-10 11:17:00 1
7 e b 2009-10-10 11:27:08 1
9 a b 2009-10-10 11:36:00 1
insert into #t values('a','b','2009-10-10 11:15:00','0')
insert into #t values('a','b','2009-10-10 11:17:00','1')
insert into #t values('e','b','2009-10-10 11:17:08','1')
insert into #t values('a','b','2009-10-10 11:20:00','0')
insert into #t values('a','b','2009-10-10 11:24:00','0')
insert into #t values('e','b','2009-10-10 11:24:08','0')
insert into #t values('e','b','2009-10-10 11:27:08','1')
insert into #t values('a','b','2009-10-10 11:29:00','0')
insert into #t values('a','b','2009-10-10 11:36:00','1')
go需求:
如果主叫呼叫被叫没有应答,查询同一主被应答与上次没有应答时间间隔大于1分钟的记录。目标结果:
ID caller called DT Ans
2 a b 2009-10-10 11:17:00 1
7 e b 2009-10-10 11:27:08 1
9 a b 2009-10-10 11:36:00 1
解决方案 »
- 已有这些信息,请问怎么远程登入此数据库,详细步骤
- oracle 一个select语句的问题
- 创建全文检索,在应用于多表关联的时候的疑惑
- 用sql语句实现这样的功能可行吗???
- 求sql语句
- 数据库再问?(急)
- 几天没来,又改版了,信息的速度,问前辈一个问题,在sql server中的企业管理器,把值改为'上午 00:21:00',他自己变成上午 12:21:00,怎么办啊
- 一个比较麻烦的问题
- 在VFP6中,建立视图,按show the sql window按钮查看视图的SQL代码,怎么会是只读的,不能编辑代码,谁能告诉我怎么能修改SQL代码啊?
- 关于事务日志和差异备份的问题?
- [百分求助]SQL2000升级到2005后访问变慢的问题
- 关于VB2005速成版与SQL2005的问题
insert into #t values('a','b','2009-10-10 11:15:00','0')
insert into #t values('a','b','2009-10-10 11:17:00','1')
insert into #t values('e','b','2009-10-10 11:17:08','1')
insert into #t values('a','b','2009-10-10 11:20:00','0')
insert into #t values('a','b','2009-10-10 11:24:00','0')
insert into #t values('e','b','2009-10-10 11:24:08','0')
insert into #t values('e','b','2009-10-10 11:27:08','1')
insert into #t values('a','b','2009-10-10 11:29:00','0')
insert into #t values('a','b','2009-10-10 11:36:00','1')
go SELECT * FROM #t AS T
WHERE Ans = 1
AND EXISTS(SELECT * FROM #t WHERE id = T.id-1
AND caller = T.caller AND called = T.called
AND Ans = 0)
DROP TABLE #t/*
id caller called dt Ans
----------- ------ ------ -------------------- ----
2 a b 2009-10-10 11:17:00 1
7 e b 2009-10-10 11:27:08 1
9 a b 2009-10-10 11:36:00 1(3 行受影响)*/
insert into #t values('a','b','2009-10-10 11:15:00','0')
insert into #t values('a','b','2009-10-10 11:17:00','1')
insert into #t values('e','b','2009-10-10 11:17:08','1')
insert into #t values('a','b','2009-10-10 11:20:00','0')
insert into #t values('a','b','2009-10-10 11:24:00','0')
insert into #t values('e','b','2009-10-10 11:24:08','0')
insert into #t values('e','b','2009-10-10 11:27:08','1')
insert into #t values('a','b','2009-10-10 11:29:00','0')
insert into #t values('a','b','2009-10-10 11:36:00','1')
go select n.id ,n.caller,n.called ,n.dt,n.Ans from
(
select *,px=(select count(1) from #t where caller = t.caller and called = t.called and id < t.id) + 1 from #t t
) m,
(
select *,px=(select count(1) from #t where caller = t.caller and called = t.called and id < t.id) + 1 from #t t
) n
where m.caller = n.caller and m.called = n.called and n.ans = 1 and m.id = n.id - 1 and datediff(mi,m.dt,n.dt) > 1drop table #t
/*
id caller called dt Ans
----------- ------ ------ -------------------- ----
2 a b 2009-10-10 11:17:00 1
7 e b 2009-10-10 11:27:08 1
9 a b 2009-10-10 11:36:00 1(所影响的行数为 3 行)
*/
insert into #t values('a','b','2009-10-10 11:15:00','0')
insert into #t values('a','b','2009-10-10 11:17:00','1')
insert into #t values('e','b','2009-10-10 11:17:08','1')
insert into #t values('a','b','2009-10-10 11:20:00','0')
insert into #t values('a','b','2009-10-10 11:24:00','0')
insert into #t values('e','b','2009-10-10 11:24:08','0')
insert into #t values('e','b','2009-10-10 11:27:08','1')
insert into #t values('a','b','2009-10-10 11:29:00','0')
insert into #t values('a','b','2009-10-10 11:36:00','1')
go select n.id ,n.caller,n.called ,n.dt,n.Ans from
(
select *,px=(select count(1) from #t where caller = t.caller and called = t.called and id < t.id) + 1 from #t t
) m,
(
select *,px=(select count(1) from #t where caller = t.caller and called = t.called and id < t.id) + 1 from #t t
) n
where m.caller = n.caller and m.called = n.called and m.ans = 0 and n.ans = 1 and m.id = n.id - 1 and datediff(mi,m.dt,n.dt) > 1drop table #t
/*
id caller called dt Ans
----------- ------ ------ -------------------- ----
2 a b 2009-10-10 11:17:00 1
7 e b 2009-10-10 11:27:08 1
9 a b 2009-10-10 11:36:00 1(所影响的行数为 3 行)
*/
不连续的话 2000用临时表 加个自增列
2005用rownumber函数 排个序 然后再按照乌龟的方法做
create table #t (id int identity(1,1),caller varchar(1),called varchar(1),dt varchar(20),Ans varchar(1))
insert into #t values('a','b','2009-10-10 11:15:00','0')
insert into #t values('a','b','2009-10-10 11:17:00','1')
insert into #t values('a','b','2009-10-10 11:29:00','1')
insert into #t values('a','b','2009-10-10 11:36:00','1')
是后三条都要吗?
create table #t (id int identity(1,1),caller varchar(1),called varchar(1),dt varchar(20),Ans varchar(1))
insert into #t values('a','b','2009-10-10 11:15:00','0')
insert into #t values('a','b','2009-10-10 11:17:00','1')
insert into #t values('e','b','2009-10-10 11:17:08','1')
insert into #t values('a','b','2009-10-10 11:20:00','0')
insert into #t values('a','b','2009-10-10 11:24:00','0')
insert into #t values('e','b','2009-10-10 11:24:08','0')
insert into #t values('e','b','2009-10-10 11:27:08','1')
insert into #t values('a','b','2009-10-10 11:29:00','0')
insert into #t values('a','b','2009-10-10 11:36:00','1')
go select *
from #t t
where t.ans = 1
and(select top 1 ans from #t t2
where t2.caller = t.caller
and t2.dt < t.dt
order by t2.dt desc)=0
and exists
(select * from #t t3
where t3.caller = t.caller
and datediff(mi,t3.dt,t.dt) > 1
)
drop table #tid caller called dt Ans
----------- ------ ------ -------------------- ----
2 a b 2009-10-10 11:17:00 1
7 e b 2009-10-10 11:27:08 1
9 a b 2009-10-10 11:36:00 1
insert into #t values('a','b','2009-10-10 11:15:00','0')
insert into #t values('a','b','2009-10-10 11:17:00','1')
insert into #t values('e','b','2009-10-10 11:17:08','1')
insert into #t values('a','b','2009-10-10 11:20:00','0')
insert into #t values('a','b','2009-10-10 11:24:00','0')
insert into #t values('e','b','2009-10-10 11:24:08','0')
insert into #t values('e','b','2009-10-10 11:27:08','1')
insert into #t values('a','b','2009-10-10 11:29:00','0')
insert into #t values('a','b','2009-10-10 11:36:00','1')
go select *
from #t t
where t.ans = 1
and(select top 1 ans from #t t2
where t2.caller = t.caller
and t2.dt < t.dt
order by t2.dt desc
)=0
and (select top 1 datediff(mi,t3.dt,t.dt)
from #t t3
where t3.caller = t.caller
and t3.dt < t.dt
order by t3.dt desc
)>1drop table #tid caller called dt Ans
----------- ------ ------ -------------------- ----
2 a b 2009-10-10 11:17:00 1
7 e b 2009-10-10 11:27:08 1
9 a b 2009-10-10 11:36:00 1
if object_id('[TableA]') is not null drop table [TableA]
go
create table [TableA]([OrderNo] varchar(3),[Name] varchar(2),[Color] varchar(6),[AtAmount] int,[Place] varchar(2))
insert [TableA]
select 'A01','AA','Red',60,'A1' union all
select 'A01','AA','Yellow',500,'A2' union all
select 'A02','BB','Bule',0,'A3' union all
select 'A03','CC','Black',300,'A4' union all
select 'A04','DD','White',400,'A5'
--> 测试数据:[TableB]
if object_id('[TableB]') is not null drop table [TableB]
go
create table [TableB]([OrderNo] varchar(3),[Name] varchar(2),[Color] varchar(6),[InAmount] int,[Place] varchar(2))
insert [TableB]
select 'A01','AA','Red',50,'A1' union all
select 'A01','AA','Red',50,'A1' union all
select 'A02','BB','Yellow',500,'A2' union all
select 'A03','CC','Bule',150,'A3' union all
select 'A03','CC','Bule',150,'A3' union all
select 'A04','DD','White',400,'A5'
--> 测试数据:[TableC]
if object_id('[TableC]') is not null drop table [TableC]
go
create table [TableC]([OrderNo] varchar(3),[Name] varchar(2),[Color] varchar(4),[OutAmount] int,[Place] varchar(2))
insert [TableC]
select 'A01','AA','Red',20,'A1' union all
select 'A01','AA','Red',20,'A1' union all
select 'A02','BB','Bule',100,'A3'
go
select OrderNo , Name , Color ,(sum(AtAmount)+sum(OutAmount)) InAmount , sum(AtAmount) AtAmount , sum(OutAmount) OutAmount , Place from
(
select OrderNo , Name , Color , AtAmount , OutAmount = 0 , Place from tablea
union all
select OrderNo , Name , Color , AtAmount = 0 , OutAmount , Place from tablec
) t
group by OrderNo , Name , Color ,Place /*
OrderNo Name Color InAmount AtAmount OutAmount Place
------- ---- ------ ----------- ----------- ----------- -----
A01 AA Red 100 60 40 A1
A01 AA Yellow 500 500 0 A2
A02 BB Bule 100 0 100 A3
A03 CC Black 300 300 0 A4
A04 DD White 400 400 0 A5
*/