27,579
社区成员
发帖
与我相关
我的任务
分享
--name 顾客名称 date 预约时间,cl 预约次数
create table #tb(name varchar(8),date datetime,cl int)
insert #tb
select '张三','2011-01-01', 3 union all
select '张三','2011-02-01', 2 union all
select '张三','2011-03-01', 1 union all
select '李四','2011-01-01', 1 union all
select '李四','2011-02-01', 5 union all
select '李四','2011-03-01', 1
--name 顾客名称 ,cl 完成次数
create table #ta(name varchar(8),cl int)
insert #ta
select '张三',4 union all
select '李四',6
select b.* from #tb b,#ta a
where b.name=a.name
and (select sum(cl) from #tb c where c.name=a.name and c.date<=b.date)>a.cl
and (select sum(cl) from #tb c where c.name=a.name and c.date<b.date)<=a.cl
/*
name date cl
-------- ------------------------------------------------------ -----------
张三 2011-02-01 00:00:00.000 2
李四 2011-03-01 00:00:00.000 1
(所影响的行数为 2 行)
*/
;with cte as
(
select t1.userid,t1.預約時間,
累計次數=(select sum(item_num) from sales_order_item t2
where t1.userid=t2.userid and t2.預約時間<=t1.預約時間),
取消完成次數=(select sum(item_num) from sales_order_item t3
where t1.userid=t3.userid
and t3.SKU in ('取消','完成'))
from sales_order_item t1
where SKU='預約'
)
select userid,最小預約時間=min(預約時間)
from cte
where 累計次數>取消完成次數
group by userid
;with cte as
(
select t1.userid,t1.預約時間,
累計次數=(select sum(item_num) from sales_order_item t2
where t1.userid=t2.userid and t2.預約時間<=t1.預約時間),
取消完成次數=(select sum(item_num) from sales_order_item t3
where t1.userid=t2.userid
and SKU in ('取消','完成'))
from sales_order_item t1
where SKU='預約'
)
select userid,最小預約時間=min(預約時間)
from cte
where 累計次數>取消完成次數
group by userid