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

kotiry 2010-10-18 10:15:05
产品编号 上下架时间 上下架状态(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

我要怎么 求出这个产品的上架总天数呢,请帮忙提供一点思路~
...全文
74 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
clarkcheng505 2010-10-18
真是高手啊 收藏了
回复
kotiry 2010-10-18
非常感谢!太经典了~
回复
dawugui 2010-10-18
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
回复
SQLCenter 2010-10-18
--> 测试数据:#
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
*/
回复
siegebaoniu 2010-10-18
/*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
*/
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-18 10:15
社区公告
暂无公告