例子数据如下
id point name contorl realtime
1 A1 点1 C1 2008-8-27 0:05
2 A2 点2 C2 2008-8-27 0:05
3 A3 点3 C2 2008-8-27 0:05
4 A1 点1 C2 2008-8-27 0:15
5 A2 点2 C1 2008-8-27 0:15
6 A3 点3 C3 2008-8-27 0:15
7 A1 点1 C3 2008-8-27 0:35
8 A2 点2 C1 2008-8-27 0:35
9 A3 点3 C3 2008-8-27 0:35
10 A1 点1 C1 2008-8-27 0:55
其中id是自增列统计结果:
sum control realtime
4 C1 2008-8-27 0:55
3 C2 2008-8-27 0:35
3 C3 2008-8-27 0:15
统计相同control点下A点的总数,同时取得统计结果时的时间
id point name contorl realtime
1 A1 点1 C1 2008-8-27 0:05
2 A2 点2 C2 2008-8-27 0:05
3 A3 点3 C2 2008-8-27 0:05
4 A1 点1 C2 2008-8-27 0:15
5 A2 点2 C1 2008-8-27 0:15
6 A3 点3 C3 2008-8-27 0:15
7 A1 点1 C3 2008-8-27 0:35
8 A2 点2 C1 2008-8-27 0:35
9 A3 点3 C3 2008-8-27 0:35
10 A1 点1 C1 2008-8-27 0:55
其中id是自增列统计结果:
sum control realtime
4 C1 2008-8-27 0:55
3 C2 2008-8-27 0:35
3 C3 2008-8-27 0:15
统计相同control点下A点的总数,同时取得统计结果时的时间
insert @t select 1 ,'A1','点1',3 ,1 ,'2008-8-27 0:05 '
union all select 2 ,'A2','点2',4 ,2 ,'2008-8-27 0:05 '
union all select 3 ,'A3','点3',10, 6,'2008-8-27 0:05'
union all select 4 ,'A1','点1',11, 7,'2008-8-27 0:15'
union all select 5 ,'A2','点2',10, 9,'2008-8-27 0:15'
union all select 6 ,'A3','点3',10, 8,'2008-8-27 0:15'
union all select 7 ,'A1','点1',2 ,1 ,'2008-8-27 0:35 '
union all select 8 ,'A2','点2',3 ,1 ,'2008-8-27 0:35 '
union all select 9 ,'A3','点3',5 ,2 ,'2008-8-27 0:35 '
union all select 10,'A1','点1', 6, 3,'2008-8-27 0:55'select *,sum= (select count(*) from @t where a.name = name)
from @t a
where not exists(select * from @t where realtime > a.realtime and name = a.name)id point name maxV minV realtime sum
----------- ----- ----- ----------- ----------- ------------------------------------------------------ -----------
8 A2 点2 3 1 2008-08-27 00:35:00.000 3
9 A3 点3 5 2 2008-08-27 00:35:00.000 3
10 A1 点1 6 3 2008-08-27 00:55:00.000 4
Drop table [tb]
Go
Create table [tb]([id] int,[point] nvarchar(2),[name] nvarchar(2),[maxV] int,[minV] int,[realtime] datetime)
Insert tb
Select 1,'A1','点1',3,1,'2008-8-27 0:05' union all
Select 2,'A2','点2',4,2,'2008-8-27 0:05' union all
Select 3,'A3','点3',10,6,'2008-8-27 0:05' union all
Select 4,'A1','点1',11,7,'2008-8-27 0:15' union all
Select 5,'A2','点2',10,9,'2008-8-27 0:15' union all
Select 6,'A3','点3',10,8,'2008-8-27 0:15' union all
Select 7,'A1','点1',2,1,'2008-8-27 0:35' union all
Select 8,'A2','点2',3,1,'2008-8-27 0:35' union all
Select 9,'A3','点3',5,2,'2008-8-27 0:35' union all
Select 10,'A1','点1',6,3,'2008-8-27 0:55'
Go
--Select * from tb-->SQL查询如下:
select count([point]) [sum],[point] control,max(realtime ) realtime
from tb
group by [point]
/*
sum control realtime
----------- ------- -----------------------
4 A1 2008-08-27 00:55:00.000
3 A2 2008-08-27 00:35:00.000
3 A3 2008-08-27 00:35:00.000(3 行受影响)
*/
if object_id('tb') is not null drop table tb
go
create table tb(id int,point varchar(5), name varchar(5),control varchar(10),realtime datetime)
insert tb select
1, 'A1' ,'点1','C1' ,'2008-8-27 0:05' union all select
2, 'A2' ,'点2','C2','2008-8-27 0:05' union all select
3, 'A3' ,'点3', 'C2' ,'2008-8-27 0:05' union all select
4, 'A1' ,'点1', 'C2' ,'2008-8-27 0:15' union all select
5, 'A2', '点2','C1' ,'2008-8-27 0:15' union all select
6, 'A3' ,'点3', 'C3' ,'2008-8-27 0:15' union all select
7, 'A1' ,'点1', 'C3' ,'2008-8-27 0:35' union all select
8, 'A2', '点2','C1' ,'2008-8-27 0:35' union all select
9, 'A3', '点3', 'C3' ,'2008-8-27 0:35' union all select
10, 'A1', '点1','C1', '2008-8-27 0:55'
SELECT [SUM]=COUNT(*),CONTROL,REALTIME=MAX(REALTIME)FROM TB
GROUP BY CONTROLSUM CONTROL REALTIME
----------- ---------- -----------------------
4 C1 2008-08-27 00:55:00.000
3 C2 2008-08-27 00:15:00.000
3 C3 2008-08-27 00:35:00.000(3 行受影响)
统计结果:
sum control realtime
4 C1 2008-8-27 0:55
3 C2 2008-8-27 0:15
3 C3 2008-8-27 0:35
统计相同control点下A点的总数,同时取得统计结果时的时间