这是一张记录用户订单的时间表
user_name user_id Expr2
[email protected] 10023 2008-12-4 8:08:13
[email protected] 10023 2009-3-5 10:58:23 2009-3-5
[email protected] 10023 2009-3-26 15:22:33
[email protected] 10024 2008-12-3 17:19:16
[email protected] 10024 2008-12-8 17:25:33
[email protected] 10024 2009-3-3 13:27:41 例如 user_id=10023 他的第一次下订单时间是2008-12-4 第二次是2009-3-5 他属于4周以上下第二单的用户。要查的是1周之内下了第二单的用户 1-2周 、2-4周、 4周以上、 再没下过第二单的用户数。
用户有很多。每个人的下单时间不完全相同。请问大虾们 这样该怎么写sql?
user_name user_id Expr2
[email protected] 10023 2008-12-4 8:08:13
[email protected] 10023 2009-3-5 10:58:23 2009-3-5
[email protected] 10023 2009-3-26 15:22:33
[email protected] 10024 2008-12-3 17:19:16
[email protected] 10024 2008-12-8 17:25:33
[email protected] 10024 2009-3-3 13:27:41 例如 user_id=10023 他的第一次下订单时间是2008-12-4 第二次是2009-3-5 他属于4周以上下第二单的用户。要查的是1周之内下了第二单的用户 1-2周 、2-4周、 4周以上、 再没下过第二单的用户数。
用户有很多。每个人的下单时间不完全相同。请问大虾们 这样该怎么写sql?
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-16 13:47:43
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([user_name] varchar(30),[user_id] int,[Expr2] datetime)
insert [tb]
select '[email protected]',10023,'2008-12-4 8:08:13' union all
select '[email protected]',10023,'2009-3-5 10:58:23' union all
select '[email protected]',10023,'2009-3-26 15:22:33' union all
select '[email protected]',10024,'2008-12-3 17:19:16' union all
select '[email protected]',10024,'2008-12-8 17:25:33' union all
select '[email protected]',10024,'2009-3-3 13:27:41'
--------------开始查询--------------------------
;with f as
(
select id=row_number()over(partition by [user_id] order by user_id), * from [tb]
)
select
datediff(wk,a.Expr2,b.Expr2)
from
f a, f b
where
a.id=b.id-1
and
a.id=1 and b.id=2
----------------结果----------------------------
/* -----------
13
1
13
1(4 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-16 13:47:43
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([user_name] varchar(30),[user_id] int,[Expr2] datetime)
insert [tb]
select '[email protected]',10023,'2008-12-4 8:08:13' union all
select '[email protected]',10023,'2009-3-5 10:58:23' union all
select '[email protected]',10023,'2009-3-26 15:22:33' union all
select '[email protected]',10024,'2008-12-3 17:19:16' union all
select '[email protected]',10024,'2008-12-8 17:25:33' union all
select '[email protected]',10024,'2009-3-3 13:27:41'
--------------开始查询--------------------------
;with f as
(
select id=row_number()over(partition by [user_id] order by user_id), * from [tb]
),
f1 as
(
select
a.[user_name],datediff(wk,a.Expr2,b.Expr2) as [time]
from
(select * from f where id=1) a,
(select * from f where id=2) b
where
a.id=b.id-1
and
a.[user_name]=b.[user_name]
)
select
[user_name],
sum(case when [time] between 1 and 2 then 1 else 0 end) as '1-2周',
sum(case when [time] between 2 and 4 then 1 else 0 end) as '2-4周',
sum(case when [time]>4 then 1 else 0 end) as '4周以上'
from
f1
group by
[user_name]
----------------结果----------------------------
/*user_name 1-2周 2-4周 4周以上
------------------------------ ----------- ----------- -----------
[email protected] 1 0 0
[email protected] 0 0 1(2 行受影响)
*/
我现在只想到一个思路。
先查出每个用户的第一次下单时间。
但是接下来 有点难度。
SELECT MIN(Expr2) AS 第一下单时间, User_Id
FROM 表
GROUP BY User_Id
ORDER BY User_Id