困扰我好多天的问题,求帮忙解决
表代码:
select a.帐号,a.媒体,sum(b.充值金额) 充值金额 from
( select 帐号,媒体,注册时间 注册时间A,
isnull((select min(注册时间) from c c2 where c1.帐号=c2.帐号 and c2.注册时间>c1.注册时间),'9999-01-01') 注册时间B
from c c1
) a
join d b on a.帐号=b.帐号 and a.注册时间A<=b.充值时间 and a.注册时间B>b.充值时间
group by a.帐号,a.媒体 order by 1
CREATE TABLE [dbo].[c](
[帐号] [varchar](50) NULL,
[媒体] [varchar](50) NULL,
[注册时间] [datetime2](7) NULL
) ON [PRIMARY]
insert into [dbo].[c] values ('qqq','tx','2013-06-05 00:00:00.0000000'),
('qqq','pp','2013-06-23 00:00:00.0000000'),
('qqq','xx','2013-07-05 00:00:00.0000000'),
('wwwwww','pp','2013-06-01 00:00:00.0000000'),
('wwwwww','fb','2013-06-15 00:00:00.0000000'),
('wwwwww','tos','2013-06-25 00:00:00.0000000')
CREATE TABLE [dbo].[d](
[帐号] [varchar](50) NULL,
[充值金额] [money] NULL,
[充值时间] [datetime2](7) NULL
) ON [PRIMARY]
insert into [dbo].[d] values ('qqq','12.00','2013-06-18 00:00:00.0000000'),
('qqq','33.00','2013-06-28 00:00:00.0000000'),
('qqq','55.00','2013-08-01 00:00:00.0000000'),
('wwwwww','10.00','2013-06-03 00:00:00.0000000'),
('wwwwww','20.00','2013-06-04 00:00:00.0000000'),
('wwwwww','20.00','2013-06-16 00:00:00.0000000'),
('wwwwww','50.00','2013-06-26 00:00:00.0000000'),
('wwwwww','33.00','2013-06-29 00:00:00.0000000')
;with r
as
(
select 帐号,媒体,注册时间,
ROW_NUMBER() over(partition by 帐号 order by 注册时间) as rownum
from c
),
r1
as
(
select r.帐号,r.媒体,
r.注册时间 as start_time, --开始时间,也就是媒体的注册时间
--结束时间,也就是下一次的其他媒体的注册时间,如果没有,默认为3000年1月1日
isnull(rr.注册时间,'3000-01-01 00:00:00') as end_time
from r
left join r rr
on r.帐号 = rr.帐号
and r.rownum + 1 = rr.rownum
)
select r1.帐号,
r1.媒体,
SUM(d.充值金额) as 充值金额
from r1
left join d
on d.帐号 = r1.帐号
and d.充值时间 >= r1.start_time
and d.充值时间 < r1.end_time
group by r1.帐号,
r1.媒体
order by r1.帐号 desc
CREATE TABLE [dbo].[c](
[帐号] varchar(50) NULL,
[媒体] varchar(50) NULL,
[注册时间] datetime NULL
) ON [PRIMARY]
insert into [dbo].[c] values ('qqq','tx','2013-06-05 00:00:00')
insert into [dbo].[c] values ('qqq','pp','2013-06-23 00:00:00')
insert into [dbo].[c] values ('qqq','xx','2013-07-05 00:00:00')
insert into [dbo].[c] values ('wwwwww','pp','2013-06-01 00:00:00')
insert into [dbo].[c] values ('wwwwww','fb','2013-06-15 00:00:00')
insert into [dbo].[c] values ('wwwwww','tos','2013-06-25 00:00:00')
CREATE TABLE [dbo].[d](
[帐号] varchar(50) NULL,
[充值金额] money NULL,
[充值时间] datetime NULL
) ON [PRIMARY]
insert into [dbo].[d] values ('qqq',12.00,'2013-06-18 00:00:00')
insert into [dbo].[d] values ('qqq',33.00,'2013-06-28 00:00:00')
insert into [dbo].[d] values ('qqq',55.00,'2013-08-01 00:00:00')
insert into [dbo].[d] values ('wwwwww',10.00,'2013-06-03 00:00:00')
insert into [dbo].[d] values ('wwwwww',20.00,'2013-06-04 00:00:00')
insert into [dbo].[d] values ('wwwwww',20.00,'2013-06-16 00:00:00')
insert into [dbo].[d] values ('wwwwww',50.00,'2013-06-26 00:00:00')
insert into [dbo].[d] values ('wwwwww',33.00,'2013-06-29 00:00:00')
select * from c
select * from d
select c.帐号, c.媒体,充值金额=(select sum(充值金额) from d
where c.帐号=d.帐号 and d.充值时间 between c.注册时间 and isnull((select top 1 注册时间 from c t where t.注册时间>c.注册时间),getdate()))
from c
drop table c,d
结果:
/*
帐号 媒体充值金额
-----------------------
qqqtx12.0000
qqqpp33.0000
qqqxx55.0000
wwwwwwpp30.0000
wwwwwwfb20.0000
wwwwwwtos83.0000
*/