34,575
社区成员
发帖
与我相关
我的任务
分享
create table liu_lang
(
id varchar(8),
Ip_Address varchar(16),
Ip_Num int,
Ip_DateTime varchar(16)
)
go
insert into liu_lang
select '246', '127.0.0.2', 1, '2009-11-15' union all
select '247', '127.0.0.1', 1, '2009-11-15' union all
select '248', '127.0.0.0', 10, '2009-11-15' union all
select '249', '127.0.0.1', 1, '2009-11-16' union all
select '250', '127.0.0.9', 2, '2009-11-16' union all
select '251', '127.0.0.7', 8, '2009-11-17' union all
select '252', '127.0.0.1', 1, '2009-11-17'
go
select Ip_DateTime,Count(Ip_Num),sum(Ip_Num) from liu_lang group by Ip_DateTime order by Ip_DateTime desc
--测试数据
if object_id('uu') is not null drop table uu
go
create table uu(id int,IP_address varchar(20),IP_num int,IP_datetime smalldatetime)
insert uu
select 246,'127.0.0.2', 1, '2009-11-15' union all
select 247,'127.0.0.1',1,'2009-11-15' union all
select 248,'127.0.0.0',10,'2009-11-15' union all
select 249,'127.0.0.1',1, '2009-11-16' union all
select 250,'127.0.0.9', 2, '2009-11-16' union all
select 251,'127.0.0.7',8,'2009-11-17' union all
select 252,'127.0.0.1',1,'2009-11-17'
--查询
select IP_datetime ,count(*) as x,sum(IP_num) from uu group by IP_datetime order by IP_datetime desc
declare @TB1 table([id] int,[IP_address] varchar(9),[IP_num] int,[IP_datetime] datetime)
insert @TB1
select 246,'127.0.0.2',1,'2009-11-15' union all
select 247,'127.0.0.1',1,'2009-11-15' union all
select 248,'127.0.0.0',10,'2009-11-15' union all
select 249,'127.0.0.1',1,'2009-11-16' union all
select 250,'127.0.0.9',2,'2009-11-16' union all
select 251,'127.0.0.7',8,'2009-11-17' union all
select 252,'127.0.0.1',1,'2009-11-17'
select distinct [IP_datetime], COUNT([IP_address]) OVER (PARTITION BY [IP_datetime]) as X
, sum([IP_num]) OVER (PARTITION BY [IP_datetime]) as Y
from @TB1
/*
IP_datetime X Y
----------------------- ----------- -----------
2009-11-15 00:00:00.000 3 12
2009-11-16 00:00:00.000 2 3
2009-11-17 00:00:00.000 2 9
(3 row(s) affected)
--> Title : Generating test data [tb]
--> Author :
--> Date : 2009-11-18 07:55:32
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (id int,IP_address nvarchar(18),IP_num int,IP_datetime datetime)
insert into [tb]
select 246,'127.0.0.2',1,'2009-11-15' union all
select 247,'127.0.0.1',1,'2009-11-15' union all
select 248,'127.0.0.0',10,'2009-11-15' union all
select 249,'127.0.0.1',1,'2009-11-16' union all
select 250,'127.0.0.9',2,'2009-11-16' union all
select 251,'127.0.0.7',8,'2009-11-17' union all
select 252,'127.0.0.1',1,'2009-11-17'
select IP_datetime,
count(*)X,
sum(IP_num)Y
from tb
group by IP_datetime
/*
IP_datetime X Y
----------------------- ----------- -----------
2009-11-15 00:00:00.000 3 12
2009-11-16 00:00:00.000 2 3
2009-11-17 00:00:00.000 2 9
(3 個資料列受到影響)
*/
SELECT IP_DATETIME,COUNT(1) AS X,SUM(IP_NUM) AS Y
FROM IP_LIULANG
GROUP BY IP_DATETIME
ORDER BY IP_DATETIME DESC