关于时间计算的一个疑难问题~

[kotiry]

产品编号 上下架时间 上下架状态（0下架，1上架）
81 2010-05-10 11:00:54.577 0
81 2010-05-10 11:00:57.377 1
81 2010-07-05 14:16:11.780 0
81 2010-08-15 23:07:53.523 1
81 2010-08-15 23:08:56.133 0

我要怎么 求出这个产品的上架总天数呢，请帮忙提供一点思路~

[siegebaoniu]

/*if object_id('yy') is not null drop table yy
create table yy(id int,udtime datetime,flag int)
insert yy
select 81,'2010-05-10 11:00:54.577',0 union all
select 81,'2010-05-10 11:00:57.377',1 union all
select 81,'2010-07-05 14:16:11.780',0 union all
select 81,'2010-08-15 23:07:53.523',1 union all
select 81,'2010-08-15 23:08:56.133',0*/

select id,days=sum(num) from
(
select id,
num=datediff(day,(select top 1 udtime from yy 
where udtime<t.udtime and id=t.id order by udtime desc),udtime) 
from yy t where flag =1
)a
group by id 

/*
81	41
*/

[SQLCenter]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(产品编号 int, 上下架时间 datetime, 上下架状态 int)
insert into #
select 81, '2010-05-10 11:00:54.577', 0 union all
select 81, '2010-05-10 11:00:57.377', 1 union all
select 81, '2010-07-05 14:16:11.780', 0 union all
select 81, '2010-08-15 23:07:53.523', 1 union all
select 81, '2010-08-15 23:08:56.133', 0

;with cte as
(
	select row=row_number()over(partition by 产品编号 order by 上下架时间), * from #
)
select a.产品编号, sum(datediff(day,a.上下架时间,isnull(b.上下架时间,getdate())))days from cte a left join cte b
on a.产品编号=b.产品编号 and a.row=b.row-1 and b.上下架状态=1
where a.上下架状态=0
group by a.产品编号

/*
产品编号    days
----------- -----------
81          105
*/

[dawugui]

create table tb(产品编号 int, 上下架时间 datetime, 上下架状态 int)
insert into tb
select 81, '2010-05-10 11:00:54.577', 0 union all
select 81, '2010-05-10 11:00:57.377', 1 union all
select 81, '2010-07-05 14:16:11.780', 0 union all
select 81, '2010-08-15 23:07:53.523', 1 union all
select 81, '2010-08-15 23:08:56.133', 0

select m.产品编号 , 
       sum(datediff(dd,m.上下架时间,isnull(n.上下架时间,getdate())) + 1) 天数
from 
(
  select t.*,px = (select count(1) from tb where 产品编号 = t.产品编号 and 上下架状态 = t.上下架状态 and 上下架时间 < t.上下架时间) + 1 from tb t
) m left join
(
  select t.*,px = (select count(1) from tb where 产品编号 = t.产品编号 and 上下架状态 = t.上下架状态 and 上下架时间 < t.上下架时间) + 1 from tb t
) n
on m.产品编号 = n.产品编号 and m.px = n.px and n.上下架状态 = 1
where m.上下架状态 = 0 
group by m.产品编号
/*
产品编号        天数          
----------- ----------- 
81          108

（所影响的行数为 1 行）
*/

select m.产品编号 , 
       sum(datediff(dd,m.上下架时间,isnull(n.上下架时间,getdate()))) 天数
from 
(
  select t.*,px = (select count(1) from tb where 产品编号 = t.产品编号 and 上下架状态 = t.上下架状态 and 上下架时间 < t.上下架时间) + 1 from tb t
) m left join
(
  select t.*,px = (select count(1) from tb where 产品编号 = t.产品编号 and 上下架状态 = t.上下架状态 and 上下架时间 < t.上下架时间) + 1 from tb t
) n
on m.产品编号 = n.产品编号 and m.px = n.px and n.上下架状态 = 1
where m.上下架状态 = 0 
group by m.产品编号
/*
产品编号        天数          
----------- ----------- 
81          105

（所影响的行数为 1 行）
*/

select m.产品编号 , 
       sum(datediff(dd,m.上下架时间,n.上下架时间) + 1) 天数
from 
(
  select t.*,px = (select count(1) from tb where 产品编号 = t.产品编号 and 上下架状态 = t.上下架状态 and 上下架时间 < t.上下架时间) + 1 from tb t
) m ,
(
  select t.*,px = (select count(1) from tb where 产品编号 = t.产品编号 and 上下架状态 = t.上下架状态 and 上下架时间 < t.上下架时间) + 1 from tb t
) n
where m.产品编号 = n.产品编号 and m.px = n.px and n.上下架状态 = 1
and m.上下架状态 = 0 
group by m.产品编号
/*

产品编号        天数          
----------- ----------- 
81          43

（所影响的行数为 1 行）
*/


select m.产品编号 , 
       sum(datediff(dd,m.上下架时间,n.上下架时间)) 天数
from 
(
  select t.*,px = (select count(1) from tb where 产品编号 = t.产品编号 and 上下架状态 = t.上下架状态 and 上下架时间 < t.上下架时间) + 1 from tb t
) m ,
(
  select t.*,px = (select count(1) from tb where 产品编号 = t.产品编号 and 上下架状态 = t.上下架状态 and 上下架时间 < t.上下架时间) + 1 from tb t
) n
where m.产品编号 = n.产品编号 and m.px = n.px and n.上下架状态 = 1
and m.上下架状态 = 0 
group by m.产品编号
/*
产品编号        天数          
----------- ----------- 
81          41

（所影响的行数为 1 行）
*/

drop table tb

[kotiry]

非常感谢！太经典了~

[clarkcheng505]

真是高手啊 收藏了