id 主键 int not null
mobile 手机号码 Varchar(20) not null
time 时间 datetime not null
msg 短信内容 Varchar(400) not null统计一段时间内一个手机发了多少条短信
mobile 手机号码 Varchar(20) not null
time 时间 datetime not null
msg 短信内容 Varchar(400) not null统计一段时间内一个手机发了多少条短信
where time between 时间1 and 时间2
create table t_test(id int not null,
mobile Varchar(20) not null,
time datetime not null,
msg Varchar(400) not null )--灌数据
declare @i int,@j int
set @i = 1
set @j = 1
while @i<=20
begin
set @j = 1
while @j<=20
begin
insert into t_test(id,mobile,time,msg) values(@i*10+@j,1111*@j,getdate()+@j,'__')
set @j=@j+1
end
set @i=@i+1
endselect mobile,count(*) from t_test where time between getdate() and getdate()+10 group by mobile
GO
CREATE TABLE TB
(
id int identity,
mobile Varchar(20) not null,
time DATETIME not null,
msg Varchar(400) not null
)
INSERT INTO TB
SELECT '1111111111111','2011-1-1','a' UNION ALL
SELECT '2222222222222','2011-1-4','a' UNION ALL
SELECT '2222222222222','2011-1-4','a' UNION ALL
SELECT '1111111111111','2011-1-1','a'SELECT isnull(mobile,'ALL') AS mobile,COUNT(*) AS NUM
FROM TB WHERE time BETWEEN '2011-1-1' AND '2011-1-4' GROUP BY mobile with cube
/*
mobile NUM
1111111111111 2
2222222222222 2
ALL 4
*/
GO
CREATE TABLE TB
(
id int identity,
mobile Varchar(20) not null,
time DATETIME not null,
msg Varchar(400) not null
)
INSERT INTO TB
SELECT '1111111111111','2011-1-1','a' UNION ALL
SELECT '2222222222222','2011-1-4','a' UNION ALL
SELECT '2222222222222','2011-1-4','a' UNION ALL
SELECT '1111111111111','2011-1-1','a'SELECT isnull(mobile,'ALL') AS mobile,COUNT(*) AS NUM
FROM TB WHERE time BETWEEN '2011-1-1' AND '2011-1-4' GROUP BY mobile with cube
/*
mobile NUM
1111111111111 2
2222222222222 2
ALL 4
*/