29号发了一个贴
http://topic.csdn.net/u/20110429/17/a3f008a0-320f-4338-bf0b-96d1656ee2f0.html
和这个差不多,本来认为这样设计很不合理
但是。。
客户说:我就要这样做没办法
重新将这个问题再放上来可能是我没说清楚
上一个贴的答案
都不是正确的有如下一些字段
id name time
1 AAA 2011-4-25 8:43:04
2 AAA 2011-4-25 9:15:09
3 BBB 2011-4-25 9:43:10
4 AAA 2011-4-25 9:43:15
5 BBB 2011-4-25 9:49:22
6 CCC 2011-4-25 9:50:24
7 AAA 2011-4-25 11:50:44
8 AAA 2011-4-27 8:43:04
9 AAA 2011-4-27 9:15:09
10 BBB 2011-4-27 9:43:10
11 AAA 2011-4-28 9:43:01
12 BBB 2011-4-28 9:49:22
13 CCC 2011-4-28 9:50:24
14 AAA 2011-4-28 11:50:44
规则,在一个小时内出现的数据只统计一次
比如 name 为AAA出现的第一次是 2011-4-25 8:43:04
那么在 2011-4-25 9:43:04 之间的数据都不进行统计也就是说 上面的数据中 id为 2的数据不统计 只统计id为1的数据
id为4的 2011-4-25 9:43:15 超过了一个小时
则加入统计 那么 4月25号的数据 应 统计的如下
1 AAA 2011-4-25 8:43:04
3 BBB 2011-4-25 9:43:10
4 AAA 2011-4-25 9:43:15
6 CCC 2011-4-25 9:50:24
7 AAA 2011-4-25 11:50:44
求个大神帮帮忙
分不够 在开一贴 给答案正确者 谢谢
http://topic.csdn.net/u/20110429/17/a3f008a0-320f-4338-bf0b-96d1656ee2f0.html
和这个差不多,本来认为这样设计很不合理
但是。。
客户说:我就要这样做没办法
重新将这个问题再放上来可能是我没说清楚
上一个贴的答案
都不是正确的有如下一些字段
id name time
1 AAA 2011-4-25 8:43:04
2 AAA 2011-4-25 9:15:09
3 BBB 2011-4-25 9:43:10
4 AAA 2011-4-25 9:43:15
5 BBB 2011-4-25 9:49:22
6 CCC 2011-4-25 9:50:24
7 AAA 2011-4-25 11:50:44
8 AAA 2011-4-27 8:43:04
9 AAA 2011-4-27 9:15:09
10 BBB 2011-4-27 9:43:10
11 AAA 2011-4-28 9:43:01
12 BBB 2011-4-28 9:49:22
13 CCC 2011-4-28 9:50:24
14 AAA 2011-4-28 11:50:44
规则,在一个小时内出现的数据只统计一次
比如 name 为AAA出现的第一次是 2011-4-25 8:43:04
那么在 2011-4-25 9:43:04 之间的数据都不进行统计也就是说 上面的数据中 id为 2的数据不统计 只统计id为1的数据
id为4的 2011-4-25 9:43:15 超过了一个小时
则加入统计 那么 4月25号的数据 应 统计的如下
1 AAA 2011-4-25 8:43:04
3 BBB 2011-4-25 9:43:10
4 AAA 2011-4-25 9:43:15
6 CCC 2011-4-25 9:50:24
7 AAA 2011-4-25 11:50:44
求个大神帮帮忙
分不够 在开一贴 给答案正确者 谢谢
select a.* from tab a
where not exists ( select 1 from tab where name = a.name and time < a.time)
union all
select a.* from tab a,cte b
where a.name = b.name
and a.time >= dateadd(hour,1,b.time)
and not exists ( select 1 from tab where name = a.name and time >= dateadd(hour,1,b.time) and time < a.time)
)
select * from cte order by id
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(3),[time] datetime)
insert [tb]
select 1,'AAA','2011-4-25 8:43:04' union all
select 2,'AAA','2011-4-25 9:15:09' union all
select 3,'BBB','2011-4-25 9:43:10' union all
select 4,'AAA','2011-4-25 9:43:15' union all
select 5,'BBB','2011-4-25 9:49:22' union all
select 6,'CCC','2011-4-25 9:50:24' union all
select 7,'AAA','2011-4-25 11:50:44' union all
select 8,'AAA','2011-4-27 8:43:04' union all
select 9,'AAA','2011-4-27 9:15:09' union all
select 10,'BBB','2011-4-27 9:43:10' union all
select 11,'AAA','2011-4-28 9:43:01' union all
select 12,'BBB','2011-4-28 9:49:22' union all
select 13,'CCC','2011-4-28 9:50:24' union all
select 14,'AAA','2011-4-28 11:50:44'
---查询---
select min(id) as id,name,dt as time
from
(
select *,
dt=(select min(time) from tb where name=t.name and datediff(ss,time,t.time)<3600)
from tb t
) a
group by name,dt
order by id,name,time
---结果---
id name time
----------- ---- -----------------------
1 AAA 2011-04-25 08:43:04.000
3 BBB 2011-04-25 09:43:10.000
4 AAA 2011-04-25 09:15:09.000
6 CCC 2011-04-25 09:50:24.000
7 AAA 2011-04-25 11:50:44.000
8 AAA 2011-04-27 08:43:04.000
10 BBB 2011-04-27 09:43:10.000
11 AAA 2011-04-28 09:43:01.000
12 BBB 2011-04-28 09:49:22.000
13 CCC 2011-04-28 09:50:24.000
14 AAA 2011-04-28 11:50:44.000(11 行受影响)
(
id INT,
[name] VARCHAR(10),
[time] DATETIME
)
INSERT #temp
select '1', 'AAA', '2011-4-25 8:43:04' union all
select '2', 'AAA', '2011-4-25 9:15:09' union all
select '3', 'BBB', '2011-4-25 9:43:10' union all
select '4', 'AAA', '2011-4-25 9:43:15' union all
select '5', 'BBB', '2011-4-25 9:49:22' union all
select '6', 'CCC', '2011-4-25 9:50:24' union all
select '7', 'AAA', '2011-4-25 11:50:44' union all
select '8', 'AAA', '2011-4-27 8:43:04' union all
select '9', 'AAA', '2011-4-27 9:15:09' union all
select '10', 'BBB', '2011-4-27 9:43:10' union all
select '11', 'AAA', '2011-4-28 9:43:01' union all
select '12', 'BBB', '2011-4-28 9:49:22' union all
select '13', 'CCC', '2011-4-28 9:50:24' union all
select '14', 'AAA', '2011-4-28 11:50:44'
GO
--SQL:
SELECT *, flag=NULL INTO # FROM #temp
ORDER BY [name], [time]DECLARE
@name VARCHAR(10),
@time DATETIME,
@flag BIT
SELECT
@name = '',
@time = ''UPDATE #
SET flag = @flag,
@flag=CASE WHEN [name]<>@name THEN 1 WHEN DATEADD(HOUR, 1, @time)<[time] THEN 1 ELSE 0 END,
@time=CASE WHEN [name]<>@name THEN [time] WHEN DATEADD(HOUR, 1, @time)<[time] THEN [time] ELSE @time END,
@name=[name]
--RESULT:
SELECT id,[name],[time] FROM # WHERE flag = 1 ORDER BY id
/*
1 AAA 2011-04-25 08:43:04.000
3 BBB 2011-04-25 09:43:10.000
4 AAA 2011-04-25 09:43:15.000
6 CCC 2011-04-25 09:50:24.000
7 AAA 2011-04-25 11:50:44.000
8 AAA 2011-04-27 08:43:04.000
10 BBB 2011-04-27 09:43:10.000
11 AAA 2011-04-28 09:43:01.000
12 BBB 2011-04-28 09:49:22.000
13 CCC 2011-04-28 09:50:24.000
14 AAA 2011-04-28 11:50:44.000
*/
这条记录
go
create table [tb]([id] int,[name] varchar(3),[time] datetime)
insert [tb]
select 1,'AAA','2011-4-25 8:43:04' union all
select 2,'AAA','2011-4-25 9:15:09' union all
select 3,'BBB','2011-4-25 9:43:10' union all
select 4,'AAA','2011-4-25 9:43:15' union all
select 5,'BBB','2011-4-25 9:49:22' union all
select 6,'CCC','2011-4-25 9:50:24' union all
select 7,'AAA','2011-4-25 11:50:44' union all
select 8,'AAA','2011-4-27 8:43:04' union all
select 9,'AAA','2011-4-27 9:15:09' union all
select 10,'BBB','2011-4-27 9:43:10' union all
select 11,'AAA','2011-4-28 9:43:01' union all
select 12,'BBB','2011-4-28 9:49:22' union all
select 13,'CCC','2011-4-28 9:50:24' union all
select 14,'AAA','2011-4-28 11:50:44'
;with cte as (
select a.* from tb a
where not exists ( select 1 from tb where name = a.name and time < a.time)
union all
select a.* from tb a,cte b
where a.name = b.name
and a.time >= dateadd(hour,1,b.time)
and not exists ( select 1 from tb where name = a.name and time >= dateadd(hour,1,b.time) and time < a.time)
)
select * from cte order by id--结果
1 AAA 2011-04-25 08:43:04.000
3 BBB 2011-04-25 09:43:10.000
4 AAA 2011-04-25 09:43:15.000
6 CCC 2011-04-25 09:50:24.000
7 AAA 2011-04-25 11:50:44.000
8 AAA 2011-04-27 08:43:04.000
10 BBB 2011-04-27 09:43:10.000
11 AAA 2011-04-28 09:43:01.000
12 BBB 2011-04-28 09:49:22.000
13 CCC 2011-04-28 09:50:24.000
14 AAA 2011-04-28 11:50:44.000