这个查寻在SQL 2005 中 开发语言是C#的. 进入主题:本查询中涉及4 个表 但是关系应该是两个表中的关联 其他的UNION 就可以了.下面就把表中的情况说一下
基本表 INFO_NO(主键)int INFO_DT(datetime)INFO_STATE(char) INFO_TYPE (char)
INFO_NO INFO_DT INFO_STATE INFO_TYPE
1 2005-02-01 1 1
2 2005-02-02 1 2
3 2005-03-01 0 3
4 2005-03-02 1 1统计表 COMM_NO(主键)int COMM_DATE(datetime) COMM_INFO_NO(基本表中的主键) INFO_TYPE (char)
COMM_NO COMM_DATE COMM_INFO_NO COMM_INFO_TYPE
1 2005-02-01 1 1
2 2005-02-01 1 1
3 2005-02-06 1 1
4 2005-02-02 2 2
5 2005-02-02 2 2
6 2005-02-08 2 2
7 2005-03-01 3 3
8 2005-02-03 4 1
9 2005-02-05 4 1主要的业务逻辑:
在基本表中 如果该信息状态(INFO_STATE)为1的话表示已经被处理过,0表示未被处理过,信息类型(INFO_TYPE)表示要处理的天数如果是1要处理3天;2表示要处理2天;3表示要处理1天,并且在统计表中有可能产生几条相对应的数据如示例.查询的逻辑,如果基本表中已经处理过的信息(INFO_STATE=1)的话,那么统计表中的相对应的天数如果大于基本表中的天数+3的话.那么就在结果中显示否则不显示.解释说明以INFO_NO 为 1 (统计表最后处理的该条信息的时间为 2005-02-06 该条数据应该在2005-02-04因该被处理完(根据INFO_TYPE=1)所以在结果中显示第一条数据,依此类推基本表中的弟4条信息就不应该显示)
2,如果该信息状态(INFO_STATE)为0的话那么表示未被处理过,则以当前系统时间为准 其他的就如上了
基本表 INFO_NO(主键)int INFO_DT(datetime)INFO_STATE(char) INFO_TYPE (char)
INFO_NO INFO_DT INFO_STATE INFO_TYPE
1 2005-02-01 1 1
2 2005-02-02 1 2
3 2005-03-01 0 3
4 2005-03-02 1 1统计表 COMM_NO(主键)int COMM_DATE(datetime) COMM_INFO_NO(基本表中的主键) INFO_TYPE (char)
COMM_NO COMM_DATE COMM_INFO_NO COMM_INFO_TYPE
1 2005-02-01 1 1
2 2005-02-01 1 1
3 2005-02-06 1 1
4 2005-02-02 2 2
5 2005-02-02 2 2
6 2005-02-08 2 2
7 2005-03-01 3 3
8 2005-02-03 4 1
9 2005-02-05 4 1主要的业务逻辑:
在基本表中 如果该信息状态(INFO_STATE)为1的话表示已经被处理过,0表示未被处理过,信息类型(INFO_TYPE)表示要处理的天数如果是1要处理3天;2表示要处理2天;3表示要处理1天,并且在统计表中有可能产生几条相对应的数据如示例.查询的逻辑,如果基本表中已经处理过的信息(INFO_STATE=1)的话,那么统计表中的相对应的天数如果大于基本表中的天数+3的话.那么就在结果中显示否则不显示.解释说明以INFO_NO 为 1 (统计表最后处理的该条信息的时间为 2005-02-06 该条数据应该在2005-02-04因该被处理完(根据INFO_TYPE=1)所以在结果中显示第一条数据,依此类推基本表中的弟4条信息就不应该显示)
2,如果该信息状态(INFO_STATE)为0的话那么表示未被处理过,则以当前系统时间为准 其他的就如上了
看到這個,干嗎非要掉過來呢???想不通了。
信息类型(INFO_TYPE)表示要处理的天数如果是1要处理3天;2表示要处理2天;
3表示要处理1天,为什么要颠倒呢?呵呵``
create table base(INFO_NO int,INFO_DT datetime ,INFO_STATE char ,INFO_TYPE Char)
insert base select 1 ,'2005-02-01','1','1'
union all select 2 ,'2005-02-02','1','2'
union all select 3 ,'2005-03-01','0','3'
union all select 4 ,'2005-03-02','1','1' create table cal(COMM_NO int ,COMM_DATE datetime,COMM_INFO_NO int , INFO_TYPE char )
insert cal select 1 ,'2005-02-01',1,1
union all select 2 ,'2005-02-01',1, 1
union all select 3 ,'2005-02-06',1 , 1
union all select 4 ,'2005-02-02',2 , 2
union all select 5 ,'2005-02-02',2 , 2
union all select 6 ,'2005-02-08',2 , 2
union all select 7 ,'2005-03-01',3 , 3
union all select 8 ,'2005-02-03',4 , 1
union all select 9 ,'2005-02-05',4 , 1 select *
from base a,cal b
where a.INFO_NO=b.COMM_INFO_NO
and (a.INFO_STATE=0 OR
(a.INFO_STATE=1
and datediff(day,a.INFO_DT,b.COMM_DATE)>(case a.INFO_TYPE when 1 then 3 when 2 then 2 else 1 end))
)
--这样?create table t1(INFO_NO int,INFO_DT datetime,INFO_STATE int,INFO_TYPE int)
insert into t1 select 1,'2005-02-01',1,1
insert into t1 select 2,'2005-02-02',1,2
insert into t1 select 3,'2005-03-01',0,3
insert into t1 select 4,'2005-03-02',1,1create table t2(COMM_NO int,COMM_DATE datetime,COMM_INFO_NO int,COMM_INFO_TYPE int)
insert into t2 select 1,'2005-02-01',1,1
insert into t2 select 2,'2005-02-01',1,1
insert into t2 select 3,'2005-02-06',1,1
insert into t2 select 4,'2005-02-02',2,2
insert into t2 select 5,'2005-02-02',2,2
insert into t2 select 6,'2005-02-08',2,2
insert into t2 select 7,'2005-03-01',3,3
insert into t2 select 8,'2005-02-03',4,1
insert into t2 select 9,'2005-02-05',4,1select b.* from t1 a,t2 b
where a.info_no=b.COMM_INFO_NO
and datediff(dd,a.INFO_DT,b.COMM_DATE)<=
(case a.INFO_TYPE when 3 then 1 when 2 then 2 when 1 then 3 end)
and INFO_STATE=1
union all
select b.* from t1 a,t2 b
where a.info_no=b.COMM_INFO_NO
and INFO_STATE=0
本示例的正确结果应该是
1 2005-02-01 1 1
2 2005-02-02 1 2
3 2005-03-01 0 3
--这样?
create table t1(INFO_NO int,INFO_DT datetime,INFO_STATE int,INFO_TYPE int)
insert into t1 select 1,'2005-02-01',1,1
insert into t1 select 2,'2005-02-02',1,2
insert into t1 select 3,'2005-03-01',0,3
insert into t1 select 4,'2005-03-02',1,1create table t2(COMM_NO int,COMM_DATE datetime,COMM_INFO_NO int,COMM_INFO_TYPE int)
insert into t2 select 1,'2005-02-01',1,1
insert into t2 select 2,'2005-02-01',1,1
insert into t2 select 3,'2005-02-06',1,1
insert into t2 select 4,'2005-02-02',2,2
insert into t2 select 5,'2005-02-02',2,2
insert into t2 select 6,'2005-02-08',2,2
insert into t2 select 7,'2005-03-01',3,3
insert into t2 select 8,'2005-02-03',4,1
insert into t2 select 9,'2005-02-05',4,1select distinct a.* from t1 a left join t2 b
on a.info_no=b.COMM_INFO_NO
where (datediff(dd,a.INFO_DT,b.COMM_DATE)>
(case a.INFO_TYPE when 3 then 1 when 2 then 2 when 1 then 3 end)
and INFO_STATE=1)
or (INFO_STATE=0
and a.INFO_DT<getdate())
insert into t1 select 1,'2005-02-01',1,1
insert into t1 select 2,'2005-02-02',1,2
insert into t1 select 3,'2005-03-01',0,3
insert into t1 select 4,'2005-03-02',1,1create table t2(COMM_NO int,COMM_DATE datetime,COMM_INFO_NO int,COMM_INFO_TYPE int)
insert into t2 select 1,'2005-02-01',1,1
insert into t2 select 2,'2005-02-01',1,1
insert into t2 select 3,'2005-02-06',1,1
insert into t2 select 4,'2005-02-02',2,2
insert into t2 select 5,'2005-02-02',2,2
insert into t2 select 6,'2005-02-08',2,2
insert into t2 select 7,'2005-03-01',3,3
insert into t2 select 8,'2005-02-03',4,1
insert into t2 select 9,'2005-02-05',4,1 select a.INFO_NO,convert(char(10),a.INFO_DT,120),a.INFO_STATE ,INFO_TYPE
from t1 a,t2 b
where a.INFO_NO=b.COMM_INFO_NO
and (a.INFO_STATE=0 OR
(a.INFO_STATE=1
and datediff(day,a.INFO_DT,b.COMM_DATE)> abs( a.INFO_TYPE - 4))
)
/*
INFO_NO INFO_STATE INFO_TYPE
----------- ---------- ----------- -----------
1 2005-02-01 1 1
2 2005-02-02 1 2
3 2005-03-01 0 3(所影响的行数为 3 行)
*/drop table t1,t2
select distinct a.* from t1 a left join t2 b
on a.info_no=b.COMM_INFO_NO
where (datediff(dd,a.INFO_DT,b.COMM_DATE)>
(case a.INFO_TYPE when 3 then 1 when 2 then 2 when 1 then 3 end)
and INFO_STATE=1)
or (INFO_STATE=0
and datediff(dd,a.INFO_DT,getdate())>
(case a.INFO_TYPE when 3 then 1 when 2 then 2 when 1 then 3 end))
结贴!散分
题外话以后可能还要感谢CCTV MT 和CHANELV