求数据库脚本大神帮忙！
tb_records:
name num date
张三 0002 2013-12-12 12345
李四 0002 2013-12-15 12345
王五 0002 2013-12-20 12345
赵六 0002 2013-12-30 12345
王二 0005 2014-01-13 12345
麻子 0005 2014-02-13 12345
求相同num，其他时间在当前行时间过去3天内的记录数和过去7天内的记录数，最后输出结果如下。
name num date count3 count7
张三 0002 2013-12-12 12345 1 1
李四 0002 2013-12-15 12345 2 2
王五 0002 2013-12-20 12345 1 2
赵六 0002 2013-12-30 12345 1 1
王二 0005 2013-12-13 12345 2 2
麻子 0005 2013-12-13 12345 2 2
小弟尝试各种方案均不通过，无奈之下集思广益，先谢谢各位!
[解决办法]
create table tb_records(name varchar(20), num varchar(10), date datetime)
insert into tb_records
select '张三', '0002', '2013-12-12' union all
select '李四', '0002', '2013-12-15' union all
select '王五', '0002', '2013-12-20' union all
select '赵六', '0002', '2013-12-30' union all
select '王二', '0005', '2013-12-13' union all
select '麻子', '0005', '2013-12-13'
go
select *,
(select count(*) from tb_records t2
where t1.num = t2.num and t2.date <= t1.date and datediff(day,t2.date,t1.date)<=3) as count3,
(select count(*) from tb_records t2
where t1.num = t2.num and t2.date <= t1.date and datediff(day,t2.date,t1.date)<=7) as count7
from tb_records t1
/*
namenumdatecount3count7
张三00022013-12-12 00:00:00.00011
李四00022013-12-15 00:00:00.00022
王五00022013-12-20 00:00:00.00012
赵六00022013-12-30 00:00:00.00011
王二00052013-12-13 00:00:00.00022
麻子00052013-12-13 00:00:00.00022
*/
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a](name varchar(6),num varchar(6),date varchar(20))
insert [a]
select '张三','0002','2013-12-12 12345' union all
select '李四','0002','2013-12-15 12345' union all
select '王五','0002','2013-12-20 12345' union all
select '赵六','0002','2013-12-30 12345' union all
select '王二','0005','2013-01-13 12345' union all
select '麻子','0005','2013-02-13 12345'
select a.*,(select count(*) from a b
where b.num = a.num and substring(b.date,1,10) <= substring(a.date,1,10)
and datediff(day,substring(b.date,1,10),substring(a.date,1,10))<=3),(select count(*) from a b
where b.num = a.num and substring(b.date,1,10) <= substring(a.date,1,10)
and datediff(day,substring(b.date,1,10),substring(a.date,1,10))<=7)
from a a
赵六 0002 2013-12-30 12345 1 1
王二 0005 2013-01-13 12345 1 1
麻子 0005 2013-02-13 12345 1 1
(6 行受影响)
结果怎么不一样呢......
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-16 16:39:47
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[tb_records]
if object_id('[tb_records]') is not null drop table [tb_records]
go
create table [tb_records]([name] varchar(4),[num] varchar(4),[date] datetime)
insert [tb_records]
select '张三','0002','2013-12-12' union all
select '李四','0002','2013-12-15' union all
select '王五','0002','2013-12-20' union all
select '赵六','0002','2013-12-30' union all
select '王二','0005','2014-01-13' union all
select '麻子','0005','2014-02-13'
--------------开始查询--------------------------
SELECT *,(SELECT COUNT(1) FROM [tb_records] b WHERE a.num=b.num AND b.[date]<=a.[date] AND DATEDIFF(dd,b.[date],a.[date])<=3 )[count3],
(SELECT COUNT(1) FROM [tb_records] b WHERE a.num=b.num AND b.[date]<=a.[date] AND DATEDIFF(dd,b.[date],a.[date])<=7 )[count7]
FROM [tb_records] a
----------------结果----------------------------
/*
name num date count3 count7
---- ---- ----------------------- ----------- -----------
张三 0002 2013-12-12 00:00:00.000 1 1
李四 0002 2013-12-15 00:00:00.000 2 2
王五 0002 2013-12-20 00:00:00.000 1 2
赵六 0002 2013-12-30 00:00:00.000 1 1
王二 0005 2014-01-13 00:00:00.000 1 1
麻子 0005 2014-02-13 00:00:00.000 1 1
*/
select a.name,a.num,a.date,b.count3,d.count7
from tb_records a
cross apply
(
select c.num,count(1) as count3 from tb_records c
where a.num=c.num and datediff(day,c.date,a.date) between 0 and 3
group by c.num
)b
cross apply
(
select e.num,count(1) as count7 from tb_records e
where a.num=e.num and datediff(day,e.date,a.date) between 0 and 7
group by e.num
)d
name num date count3 count7
---- ---- ----------------------- ----------- -----------
张三 0002 2013-12-12 00:00:00.000 1 1
李四 0002 2013-12-15 00:00:00.000 2 2
王五 0002 2013-12-20 00:00:00.000 1 2
赵六 0002 2013-12-30 00:00:00.000 1 1
王二 0005 2014-01-13 00:00:00.000 1 1
麻子 0005 2014-02-13 00:00:00.000 1 1
(6 行受影响)
