TaskBase表:
Id acceptTime limitTime
1 2010-1-1 2010-1-20
2 2010-1-1 2010-1-22
3 2010-1-2 2010-1-25
4 2010-1-2 2010-1-30TaskStateBase表:
Id TaskBase operationDate
1 1 2010-1-15
2 1 2010-1-30
3 2 2010-1-21acceptTime为受理时间,limitTime为最后期限
operationDate为处理时间现在需要查询哪个案件超时了。例如:案件1最后期限为2010-1-20,进行了2个处理操作,最后一个时间为2010-1-30,表示已经超时结果:
案件1已经超时
案件2还未超时
Id acceptTime limitTime
1 2010-1-1 2010-1-20
2 2010-1-1 2010-1-22
3 2010-1-2 2010-1-25
4 2010-1-2 2010-1-30TaskStateBase表:
Id TaskBase operationDate
1 1 2010-1-15
2 1 2010-1-30
3 2 2010-1-21acceptTime为受理时间,limitTime为最后期限
operationDate为处理时间现在需要查询哪个案件超时了。例如:案件1最后期限为2010-1-20,进行了2个处理操作,最后一个时间为2010-1-30,表示已经超时结果:
案件1已经超时
案件2还未超时
select * from TaskBase a
where
exists ( select * from TaskStateBase b where a.id = b.TaskBase and a.limitTime < b.operationDate )
不过好像你要的结果不合理。
'案件'+ltrim(a.id))+'已经超时' end 案件状态 from TaskBase a,TaskStateBase b where a.id=b.TaskBase
-- Author :SQL77(只为思齐老)
-- Date :2010-03-17 16:41:14
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TaskBase
if object_id('tempdb.dbo.#TaskBase') is not null drop table #TaskBase
go
create table #TaskBase([Id] int,[acceptTime] datetime,[limitTime] datetime)
insert #TaskBase
select 1,'2010-1-1','2010-1-20' union all
select 2,'2010-1-1','2010-1-22' union all
select 3,'2010-1-2','2010-1-25' union all
select 4,'2010-1-2','2010-1-30'
--> 测试数据:#TaskStateBase
if object_id('tempdb.dbo.#TaskStateBase') is not null drop table #TaskStateBase
go
create table #TaskStateBase([Id] int,[TaskBase] int,[operationDate] datetime)
insert #TaskStateBase
select 1,1,'2010-1-15' union all
select 2,1,'2010-1-30' union all
select 3,2,'2010-1-21'
--------------开始查询--------------------------select A.ID,
MAX(CASE WHEN A.limitTime<=B.operationDate THEN '超时' ELSE '未超时' END) AS STATE
from #TaskBase A, #TaskStateBase B
WHERE A.ID=B.TaskBase
AND operationDate=(SELECT MAX(operationDate)FROM #TaskStateBase WHERE TaskBase=B.TaskBase)
GROUP BY A.ID
----------------结果----------------------------
/* (所影响的行数为 4 行)
(所影响的行数为 3 行)ID STATE
----------- ------
1 超时
2 未超时(所影响的行数为 2 行)
*/
-- Author :SQL77(只为思齐老)
-- Date :2010-03-17 16:41:14
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TaskBase
if object_id('tempdb.dbo.#TaskBase') is not null drop table #TaskBase
go
create table #TaskBase([Id] int,[acceptTime] datetime,[limitTime] datetime)
insert #TaskBase
select 1,'2010-1-1','2010-1-20' union all
select 2,'2010-1-1','2010-1-22' union all
select 3,'2010-1-2','2010-1-25' union all
select 4,'2010-1-2','2010-1-30'
--> 测试数据:#TaskStateBase
if object_id('tempdb.dbo.#TaskStateBase') is not null drop table #TaskStateBase
go
create table #TaskStateBase([Id] int,[TaskBase] int,[operationDate] datetime)
insert #TaskStateBase
select 1,1,'2010-1-15' union all
select 2,1,'2010-1-30' union all
select 3,2,'2010-1-21'
--------------开始查询--------------------------select A.ID,
'案件'+LTRIM(A.ID)+CASE WHEN A.limitTime<=B.operationDate THEN '超时' ELSE '未超时' END AS STATE
from #TaskBase A, #TaskStateBase B
WHERE A.ID=B.TaskBase
AND operationDate=(SELECT MAX(operationDate)FROM #TaskStateBase WHERE TaskBase=B.TaskBase)----------------结果----------------------------
/*
(所影响的行数为 4 行)
(所影响的行数为 3 行)ID STATE
----------- ----------------------
1 案件1超时
2 案件2未超时(所影响的行数为 2 行)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-17 16:41:35
-- Verstion:
-- Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
-- Dec 16 2008 19:46:53
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[TaskBase]
if object_id('[TaskBase]') is not null drop table [TaskBase]
go
create table [TaskBase]([Id] int,[acceptTime] datetime,[limitTime] datetime)
insert [TaskBase]
select 1,'2010-1-1','2010-1-20' union all
select 2,'2010-1-1','2010-1-22' union all
select 3,'2010-1-2','2010-1-25' union all
select 4,'2010-1-2','2010-1-30'
--> 测试数据:[TaskStateBase]
if object_id('[TaskStateBase]') is not null drop table [TaskStateBase]
go
create table [TaskStateBase]([Id] int,[TaskBase] int,[operationDate] datetime)
insert [TaskStateBase]
select 1,1,'2010-1-15' union all
select 2,1,'2010-1-30' union all
select 3,2,'2010-1-21'
--------------开始查询--------------------------
select
case when a.[limitTime]>=b.[operationDate] then '已经超时' else '还未超时' end
from
[TaskBase] a,[TaskStateBase] b
where
a.[id]=b.[TaskBase]
--select * from [TaskBase]
--select * from [TaskStateBase]
----------------结果----------------------------
/* --------
已经超时
还未超时
已经超时(所影响的行数为 3 行)*/
insert into tb values(1, '2010-1-1', '2010-1-20')
insert into tb values(2, '2010-1-1', '2010-1-22')
insert into tb values(3, '2010-1-2', '2010-1-25')
insert into tb values(4, '2010-1-2', '2010-1-30')
create table TaskStateBase(Id int,TaskBase int,operationDate datetime)
insert into TaskStateBase values(1, 1 ,'2010-1-15')
insert into TaskStateBase values(2, 1 ,'2010-1-30')
insert into TaskStateBase values(3, 2 ,'2010-1-21')
goselect m.* , n.operationDate , case when m.limitTime < n.operationDate then '超时' else '未超时' end 结果 from tb m , TaskStateBase n where m.Id = n.TaskBase and n.operationDate = (select max(operationDate) from TaskStateBase where TaskBase = n.TaskBase)drop table tb , TaskStateBase/*
Id acceptTime limitTime operationDate 结果
----------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------
1 2010-01-01 00:00:00.000 2010-01-20 00:00:00.000 2010-01-30 00:00:00.000 超时
2 2010-01-01 00:00:00.000 2010-01-22 00:00:00.000 2010-01-21 00:00:00.000 未超时(所影响的行数为 2 行)*/
insert into tb values(1, '2010-1-1', '2010-1-20')
insert into tb values(2, '2010-1-1', '2010-1-22')
insert into tb values(3, '2010-1-2', '2010-1-25')
insert into tb values(4, '2010-1-2', '2010-1-30')
create table TaskStateBase(Id int,TaskBase int,operationDate datetime)
insert into TaskStateBase values(1, 1 ,'2010-1-15')
insert into TaskStateBase values(2, 1 ,'2010-1-30')
insert into TaskStateBase values(3, 2 ,'2010-1-21')select a.Id, case
when max(b.operationDate)>a.limitTime
then '案件'+ltrim(a.Id)+'已经超时'
else '案件'+ltrim(a.Id)+'未超时' end as status
from TaskBase a,TaskStateBase b where a.Id=b.TaskBase
group by a.Id,a.limitTime
insert into tb values(1, '2010-1-1', '2010-1-20')
insert into tb values(2, '2010-1-1', '2010-1-22')
insert into tb values(3, '2010-1-2', '2010-1-25')
insert into tb values(4, '2010-1-2', '2010-1-30')
create table TaskStateBase(Id int,TaskBase int,operationDate datetime)
insert into TaskStateBase values(1, 1 ,'2010-1-15')
insert into TaskStateBase values(2, 1 ,'2010-1-30')
insert into TaskStateBase values(3, 2 ,'2010-1-21')
goselect m.* from tb m , TaskStateBase n where m.Id = n.TaskBase and n.operationDate > m.limitTime and n.operationDate = (select max(operationDate) from TaskStateBase where TaskBase = n.TaskBase)drop table tb , TaskStateBase/*
Id acceptTime limitTime
----------- ------------------------------------------------------ ------------------------------------------------------
1 2010-01-01 00:00:00.000 2010-01-20 00:00:00.000(所影响的行数为 1 行)*/
inner join taskstatebase b on a.id=b.taskbase and a.limitTime < b.operationDate描述的不是很好,^_^,我要的这个也可以出来咯