注册 登录
编程论坛 → SQL Server论坛

[求助]此类查询语句该如何写（有难度的题目）

weihongkun 发布于 2006-12-09 20:37， 1157 次点击
这是在做配方成本计算的时候遇到的，急需解决，题目如下：
Bom(配方号) t_item(产品代码) BegDate（启用时间） EndDate（结束时间）
1 2001 2006-08-29 2006-09-03
2 2002 2006-08-30 2006-09-02
3 2001 2006-09-03 2006-09-10
4 2001 2006-09-12 2006-09-18
5 2003 2006-08-29 2006-09-18
6 2004 2006-09-29 2006-10-03
7 2001 2006-09-18 2006-10-03
 8 2001 2006-10-10 2006-11-01

问题描述：同一产品代码的下一配方的启用时间应该为上一配方的结束时间，且下一配方的启用时间应该大于上一配方的结束时间，如上表中 2001 有配方1、3、4、7、8
Bom(配方号) t_item(产品代码) BegDate（启用时间） EndDate（结束时间）
1 2001 2006-08-29 2006-09-03
3 2001 2006-09-03 2006-09-10
4 2001 2006-09-12 2006-09-18
7 2001 2006-09-18 2006-10-03
8 2001 2006-10-10 2006-11-01

上表中4号配方的‘启用时间’不等于3号配方的‘结束时间’；8号配方的‘启用时间’不等于7号配方的‘结束时间’

问题：如何通过语句（sql）查找出这种错误？

请高手帮忙解答，急待！

[此贴子已经被作者于2006-12-9 20:39:38编辑过]

18 回复
#2
weihongkun2006-12-09 21:05

这个版块里面没有高手么？
上为日期连续性问题
自己顶起来
#3
棉花糖ONE2006-12-09 21:17

你去建个带自增字段的临时表,然后用循环来判断,我这机子没装sql 2000,没法帮你了,sorry
#4
bygg2006-12-10 00:50
select DATEDIFF(day,(select EndDate from table1 where Bom=3),(select BegData from table1 where Bom=4))
如果这个数大于0,则说明4号的时间比3号的时间大,小于-->4号的比3号的小;等于-->两个时间相等

你可以通过循环或者是递归来实现全部和判断.
#5
weihongkun2006-12-10 16:45

谢谢,先去试试
#6
weihongkun2006-12-10 18:52
还是不行哦,必须是同一产品代码相比较,但同一品种代码的bom不是连续的,如表中的
2001产品代码的就是配方1、3、4、7、8
#7
棉花糖ONE2006-12-10 19:40

你先写个存储过程,把编号取出来,建一张临时表(含有一个自增字段的),就能保证每个编号都是连续的,再用循环来调用存储过程
#8
weihongkun2006-12-13 22:25

还不是很明白,请您再帮帮我吧!
#9
weihongkun2006-12-14 11:47
请高手们给予解答,谢过
#10
weihongkun2006-12-18 18:20
顶上去,求人回答
#11
weihongkun2006-12-24 19:21

求人回答,再顶上去
#12
LouisXIV2006-12-24 23:05

declare @test table
(
BOM int identity(1,1),
T_ITEM char(4),
BEGIN_DATE datetime,
END_DATE datetime
)
insert into @test
select '2001','2006-08-29','2006-09-03' union all
select '2002','2006-08-30','2006-09-02' union all
select '2001','2006-09-03','2006-09-10' union all
select '2001','2006-09-12','2006-09-18' union all
select '2003','2006-08-29','2006-09-18' union all
select '2004','2006-09-29','2006-10-03' union all
select '2001','2006-09-18','2006-10-03' union all
select '2001','2006-10-10','2006-11-01'

select * from @test t
where BEGIN_DATE!=(select END_DATE from @test where T_ITEM=t.T_ITEM and BOM=(select max(BOM) from @test where T_ITEM=t.T_ITEM and BOM<t.BOM))
or END_DATE!=(select BEGIN_DATE from @test where T_ITEM=t.T_ITEM and BOM=(select min(BOM) from @test where T_ITEM=t.T_ITEM and BOM>t.BOM))


/*
BOM T_ITEM BEGIN_DATE END_DATE
----------- ------ ------------------------------------------------------ ------------------------------------------------------
3 2001 2006-09-03 00:00:00.000 2006-09-10 00:00:00.000
4 2001 2006-09-12 00:00:00.000 2006-09-18 00:00:00.000
7 2001 2006-09-18 00:00:00.000 2006-10-03 00:00:00.000
8 2001 2006-10-10 00:00:00.000 2006-11-01 00:00:00.000
*/
#13
棉花糖ONE2006-12-25 19:12

我用存储过程做了一下,不过真麻烦
还是斑竹的简单
drop table s
create table s
(
T_ITEM int,
BEGIN_DATE datetime,
END_DATE datetime
)
insert s select 2001,'2006-08-29','2006-09-03' union all
select 2002,'2006-08-30','2006-09-02' union all
select 2001,'2006-09-03','2006-09-10' union all
select 2001,'2006-09-12','2006-09-18' union all
select 2003,'2006-08-29','2006-09-18' union all
select 2004,'2006-09-29','2006-10-03' union all
select 2001,'2006-09-18','2006-10-03' union all
select 2001,'2006-10-10','2006-11-01'
drop procedure pro_s
创建存储过程
create procedure pro_s
(@i int)
as
declare @j int
set @j=1
select identity(int,1,1) as bom ,* into #s1 from s where T_ITEM=@i
declare @begin_time datetime
declare @end_time datetime
while @j<=(select count(1) from #s1)-1
begin
select @end_time=end_date from #s1 where bom=@j
select @begin_time=begin_date from #s1 where bom=@j+1
if @begin_time<>@end_time
begin
print ' not filled'
select * from #s1 where T_ITEM=@i
set @j=(select count(1) from #s1)-1
end
set @j=@j+1
end
用临时表来保存T_ITEM
drop table #s
create table #s
(
i int identity(1,1),
T_ITEM int
)
insert into #s select distinct T_ITEM from s
用循环调用存储过程
declare @j int
declare @T_ITEM int
set @j=1
while @j<=(select count(1) from #s )
begin
select @T_ITEM=T_ITEM from #s where @j=i
exec pro_s @T_ITEM
set @j=@j+1
end

[此贴子已经被作者于2006-12-25 20:57:50编辑过]
#14
棉花糖ONE2006-12-25 20:20
看了很长时间,才看懂斑竹的代码,但是有一点不明,如果插入的日期不是按从小到达的话,那段代码可以吗
#15
weihongkun2006-12-29 21:16

老大们加点注释呀?看不明白哦!
#16
棉花糖ONE2006-12-29 21:35
以下是引用LouisXIV在2006-12-24 23:05:45的发言：

declare @test table
(
BOM int identity(1,1),
T_ITEM char(4),
BEGIN_DATE datetime,
END_DATE datetime
)
insert into @test
select '2001','2006-08-29','2006-09-03' union all
select '2002','2006-08-30','2006-09-02' union all
select '2001','2006-09-03','2006-09-10' union all
select '2001','2006-09-12','2006-09-18' union all
select '2003','2006-08-29','2006-09-18' union all
select '2004','2006-09-29','2006-10-03' union all
select '2001','2006-09-18','2006-10-03' union all
select '2001','2006-10-10','2006-11-01'

select * from @test t
where BEGIN_DATE!=(select END_DATE from @test where T_ITEM=t.T_ITEM and BOM=(select max(BOM) from @test where T_ITEM=t.T_ITEM and BOM<t.BOM))
or END_DATE!=(select BEGIN_DATE from @test where T_ITEM=t.T_ITEM and BOM=(select min(BOM) from @test where T_ITEM=t.T_ITEM and BOM>t.BOM))


/*
BOM T_ITEM BEGIN_DATE END_DATE
----------- ------ ------------------------------------------------------ ------------------------------------------------------
3 2001 2006-09-03 00:00:00.000 2006-09-10 00:00:00.000
4 2001 2006-09-12 00:00:00.000 2006-09-18 00:00:00.000
7 2001 2006-09-18 00:00:00.000 2006-10-03 00:00:00.000
8 2001 2006-10-10 00:00:00.000 2006-11-01 00:00:00.000
*/

我老大给你的这段程序你看懂了没啊
#17
weihongkun2006-12-30 14:49
还没完全弄懂,正在研究,谢了,大哥们
#18
棉花糖ONE2006-12-30 14:53
先去找点简单的子查询看看,再来看他这代码
#19
weihongkun2006-12-30 23:57

问题基本得到解决,谢谢大家!
不过还没有完全弄明白原理,呵呵
1
©2025, BC-CN.NET
电脑版