表结构
ID ArticlesID AddTime
1 1 2010-5-1
2 1 2010-5-2
3 3 2010-5-2
4 2 2010-5-2
5 1 2010-5-2
6 2 2010-5-3
7 1 2010-5-3
8 2 2010-5-3
9 1 2010-5-3我想知道 2010.5 这个月 ArticlesID 等于 1 的统计情况 效果如下ArticlesID 日期 统计数
1 2010-5-1 1
1 2010-5-2 2
1 2010-5-3 2
SQL语句怎么写? 在线等!!!
ID ArticlesID AddTime
1 1 2010-5-1
2 1 2010-5-2
3 3 2010-5-2
4 2 2010-5-2
5 1 2010-5-2
6 2 2010-5-3
7 1 2010-5-3
8 2 2010-5-3
9 1 2010-5-3我想知道 2010.5 这个月 ArticlesID 等于 1 的统计情况 效果如下ArticlesID 日期 统计数
1 2010-5-1 1
1 2010-5-2 2
1 2010-5-3 2
SQL语句怎么写? 在线等!!!
group by AddTime
试试
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[ArticlesID] int,[AddTime] datetime)
insert [tb]
select 1,1,'2010-5-1' union all
select 2,1,'2010-5-2' union all
select 3,3,'2010-5-2' union all
select 4,2,'2010-5-2' union all
select 5,1,'2010-5-2' union all
select 6,2,'2010-5-3' union all
select 7,1,'2010-5-3' union all
select 8,2,'2010-5-3' union all
select 9,1,'2010-5-3'--------------------------------查询开始------------------------------select [ArticlesID],[AddTime],count(*) as 统计数
from [tb] where [ArticlesID]=1
group by [ArticlesID],[AddTime]
/*
ArticlesID AddTime 统计数
----------- ----------------------- -----------
1 2010-05-01 00:00:00.000 1
1 2010-05-02 00:00:00.000 2
1 2010-05-03 00:00:00.000 2(3 行受影响)
*/
selct ArticlesID,AddTime,统计数=count(1) from tb where ArticlesID=1
group by ArticlesID ,AddTime
from tb
where ArticlesID = 1
group by ArticlesID,AddTime
from tb
where ArticlesID = 1
group by ArticlesID,AddTime
-- Author : htl258(Tony)
-- Date : 2010-05-12 10:41:56
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[ArticlesID] [int],[AddTime] [datetime])
INSERT INTO [tb]
SELECT '1','1','2010-5-1' UNION ALL
SELECT '2','1','2010-5-2' UNION ALL
SELECT '3','3','2010-5-2' UNION ALL
SELECT '4','2','2010-5-2' UNION ALL
SELECT '5','1','2010-5-2' UNION ALL
SELECT '6','2','2010-5-3' UNION ALL
SELECT '7','1','2010-5-3' UNION ALL
SELECT '8','2','2010-5-3' UNION ALL
SELECT '9','1','2010-5-3'--SELECT * FROM [tb]-->SQL查询如下:
select ArticlesID,日期=AddTime, 统计数=count(1)
from tb
where ArticlesID = 1
group by ArticlesID,AddTime
/*
ArticlesID 日期 统计数
----------- ----------------------- -----------
1 2010-05-01 00:00:00.000 1
1 2010-05-02 00:00:00.000 2
1 2010-05-03 00:00:00.000 2(3 行受影响)
*/
from 表名字
where ArticlesID = 1
我的时间是 2010-05-11 10:03:06.290
我改成下面这样的 但没办法知道是哪天的数据了...
select
ArticlesID,
count(*) AS Stat
from Articles_Stat
where ArticlesID=3
group by ArticlesID, cast(cast(AddTime as varchar(11)) as datetime)结果
ArticlesID Stat
3 49
3 2
3 51