select INFO,convert(varchar(13),TIME,120),count(1) shuliang from tb where TIME between @TIME1 and @TIME2 group by INFO,convert(varchar(13),TIME,120) order by 3
select INFO,datepart(hh,TIME) 时间,count(1) shuliang from tb where TIME between @TIME1 and @TIME2 group by INFO,datepart(hh,TIME) order by 3
Select * from 表A Order by SUBSTRING([TIME],12,9) asc
select INFO,count(1) from tb where TIME between TIME1 and TIME2 group by info order by 排序字段
CREATE TABLE A( [ID] VARCHAR(20), [INFO] VARCHAR(20), [TIME] datetime )INSERT INTO A select '0' ,'Read','2011-04-28 13:00:54' UNION ALL SELECT '1' ,'Read','2011-04-28 13:00:54' UNION ALL SELECT '2' ,'Read','2011-04-28 14:00:54' UNION ALL SELECT '3' ,'Read','2011-04-28 14:00:54' UNION ALL SELECT '4' ,'Read','2011-04-28 15:00:54'SELECT HH,SUM(NUM) FROM ( SELECT DATEPART(HH,TIME) AS HH,1 AS NUM FROM A) AS T GROUP BY HH
SELECT HH,SUM(NUM) FROM ( SELECT DATEPART(HH,TIME) AS HH,1 AS NUM FROM A) AS T WHERE HH>14--需要过滤的话 在这里添加条件 GROUP BY HH
INSERT INTO #tb1 SELECT 1,'Read','2011-02-28 15:33:33' UNION ALL SELECT 2,'Read','2011-02-14 09:33:33' UNION ALL SELECT 3,'Read','2011-02-01 23:33:33'
SELECT * FROM #tb1 ORDER BY SUBSTRING(CONVERT(varchar(50),[TIME],120), 12, 2) asc
select a.* from tb a, (select datepart(hh,[time]) [hh],count(1) shuliang group by datepart(hh,[time])) b where a.datepart(hh,[time])=b.[hh] order by b.shuliang
select a.* from tb a, (select datepart(hh,[time]) [hh],count(1) shuliang group by datepart(hh,[time])) b where a.datepart(hh,[time])=b.[hh] order by b.shuliang desc
INSERT INTO #tb1 SELECT 1 , 'Read' , '2011-02-28 15:33:33' UNION ALL SELECT 2 , 'Read' , '2011-02-14 09:33:33' UNION ALL SELECT 3 , 'Read' , '2011-02-01 23:33:33' UNION ALL SELECT 4 , 'Read' , '2011-02-01 23:34:33'
SELECT SUBSTRING(CONVERT(VARCHAR(50), [TIME], 120), 12, 2) , COUNT(1) AS [Count] FROM #tb1 WHERE 1 = 1 GROUP BY SUBSTRING(CONVERT(VARCHAR(50), [TIME], 120), 12, 2) ORDER BY SUBSTRING(CONVERT(VARCHAR(50), [TIME], 120), 12, 2) ASC DROP TABLE #tb1
INSERT INTO #tb1 SELECT 1,'Read','2011-02-28 15:33:33' UNION ALL SELECT 2,'Read','2011-02-14 09:33:33' UNION ALL SELECT 3,'Read','2011-02-01 23:33:33' select a.* from #tb1 a, (select datepart(hh,[time]) [hh],count(1) shuliang from #tb1 group by datepart(hh,[time])) b where datepart(hh,a.[TIME])=b.[hh] order by b.shuliang desc
INSERT INTO #tb1 SELECT 1 , 'Read' , '2011-02-28 15:33:33' UNION ALL SELECT 2 , 'Read' , '2011-02-14 09:33:33' UNION ALL SELECT 3 , 'Read' , '2011-02-01 23:33:33' UNION ALL SELECT 4 , 'Read' , '2011-02-01 23:34:33' select a.* from #tb1 a, (select datepart(hh,[time]) [hh],count(1) shuliang from #tb1 group by datepart(hh,[time])) b where datepart(hh,a.[TIME])=b.[hh] order by b.shuliang desc/* ID INFO TIME ----------- -------------------------------------------------- ----------------------- 3 Read 2011-02-01 23:33:33.000 4 Read 2011-02-01 23:34:33.000 2 Read 2011-02-14 09:33:33.000 1 Read 2011-02-28 15:33:33.000
INSERT INTO #tb1 SELECT 1 , 'Read' , '2011-02-28 15:33:33' UNION ALL SELECT 2 , 'Read' , '2011-02-14 09:33:33' UNION ALL SELECT 3 , 'Read' , '2011-02-01 23:33:33' UNION ALL SELECT 4 , 'Read' , '2011-02-01 23:34:33' UNION ALL SELECT 5 , 'Read' , '2011-02-01 01:34:33' UNION ALL SELECT 6 , 'Read' , '2011-02-01 02:34:33'
SELECT SUBSTRING(CONVERT(VARCHAR(50), [TIME], 120), 12, 2)+N'点--'+CONVERT (VARCHAR(10),CONVERT(INT ,SUBSTRING(CONVERT(VARCHAR(50),[TIME], 120), 12, 2))+1)+N'点' AS [时], COUNT(1) AS [访问量] FROM #tb1 WHERE 1 = 1 GROUP BY SUBSTRING(CONVERT(VARCHAR(50), [TIME], 120), 12, 2) ORDER BY SUBSTRING(CONVERT(VARCHAR(50), [TIME], 120), 12, 2) ASC DROP TABLE #tb1
group by year(TIME),month(TIME),day(TIME),datepart(hh,TIME)
order by 2
[ID] [INFO] [TIME]
0 Read '2011-04-28 13:00:54'
1 Read '2011-04-28 13:00:54'
2 Read '2011-04-28 14:00:54'
3 Read '2011-04-28 14:00:54'
4 Read '2011-04-28 15:00:54'
...
. Read '2011-04-28 13:00:54'
. Read '2011-04-28 13:00:54'
. Read '2011-04-28 14:00:54'
. Read '2011-04-28 14:00:54'
. Read '2011-04-28 15:00:54'
...
. Read '2011-04-28 13:00:54'
. Read '2011-04-29 13:00:54'
. Read '2011-04-29 13:00:54'
. Read '2011-04-29 13:00:54'
. Read '2011-04-29 13:00:54'
...
. Read '2011-04-29 13:00:54'
. Read '2011-04-29 13:00:54'
. Read '2011-04-29 14:00:54'
. Read '2011-04-29 14:00:54'
. Read '2011-04-29 15:00:54'
from tb where TIME between @TIME1 and @TIME2
group by INFO,convert(varchar(13),TIME,120)
order by 3
from tb where TIME between @TIME1 and @TIME2
group by INFO,datepart(hh,TIME)
order by 3
Select * from 表A Order by SUBSTRING([TIME],12,9) asc
where TIME between TIME1 and TIME2
group by info
order by 排序字段
CREATE TABLE A(
[ID] VARCHAR(20),
[INFO] VARCHAR(20),
[TIME] datetime
)INSERT INTO A
select
'0' ,'Read','2011-04-28 13:00:54'
UNION ALL SELECT
'1' ,'Read','2011-04-28 13:00:54'
UNION ALL SELECT
'2' ,'Read','2011-04-28 14:00:54'
UNION ALL SELECT
'3' ,'Read','2011-04-28 14:00:54'
UNION ALL SELECT
'4' ,'Read','2011-04-28 15:00:54'SELECT HH,SUM(NUM) FROM (
SELECT DATEPART(HH,TIME) AS HH,1 AS NUM FROM A) AS T
GROUP BY HH
SELECT HH,SUM(NUM) FROM (
SELECT DATEPART(HH,TIME) AS HH,1 AS NUM FROM A) AS T
WHERE HH>14--需要过滤的话 在这里添加条件
GROUP BY HH
输出例如
[ID] [INFO] [TIME]
.. Read 00:00:00 // 0 ~ 1点内数据 例如数据有3000条
.. Read 00:00:00
.. Read 00:00:00
.. Read 00:00:00
.. Read 00:00:00
...
.. Read 05:00:00 // 5 ~ 6点内数据例如数据有2000条
.. Read 05:00:00
.. Read 05:00:00
.. Read 05:00:00
..
.. Read 03:00:00 // 3 ~ 4点内数据例如数据有1000条
.. Read 05:00:00
.. Read 05:00:00
CREATE TABLE #tb1
(
[ID] INT ,
[INFO] VARCHAR(50) ,
[TIME] DATETIME
)
INSERT INTO #tb1
SELECT 1,'Read','2011-02-28 15:33:33'
UNION ALL
SELECT 2,'Read','2011-02-14 09:33:33'
UNION ALL
SELECT 3,'Read','2011-02-01 23:33:33'
SELECT * FROM #tb1 ORDER BY SUBSTRING(CONVERT(varchar(50),[TIME],120), 12, 2) asc
(select datepart(hh,[time]) [hh],count(1) shuliang group by datepart(hh,[time])) b
where a.datepart(hh,[time])=b.[hh]
order by b.shuliang
(select datepart(hh,[time]) [hh],count(1) shuliang group by datepart(hh,[time])) b
where a.datepart(hh,[time])=b.[hh]
order by b.shuliang desc
(
[ID] INT ,
[INFO] VARCHAR(50) ,
[TIME] DATETIME
)
INSERT INTO #tb1
SELECT 1 ,
'Read' ,
'2011-02-28 15:33:33'
UNION ALL
SELECT 2 ,
'Read' ,
'2011-02-14 09:33:33'
UNION ALL
SELECT 3 ,
'Read' ,
'2011-02-01 23:33:33'
UNION ALL
SELECT 4 ,
'Read' ,
'2011-02-01 23:34:33'
SELECT SUBSTRING(CONVERT(VARCHAR(50), [TIME], 120), 12, 2) ,
COUNT(1) AS [Count]
FROM #tb1
WHERE 1 = 1
GROUP BY SUBSTRING(CONVERT(VARCHAR(50), [TIME], 120), 12, 2)
ORDER BY SUBSTRING(CONVERT(VARCHAR(50), [TIME], 120), 12, 2) ASC
DROP TABLE #tb1
(
[ID] INT ,
[INFO] VARCHAR(50) ,
[TIME] DATETIME
)
INSERT INTO #tb1
SELECT 1,'Read','2011-02-28 15:33:33'
UNION ALL
SELECT 2,'Read','2011-02-14 09:33:33'
UNION ALL
SELECT 3,'Read','2011-02-01 23:33:33'
select a.* from #tb1 a,
(select datepart(hh,[time]) [hh],count(1) shuliang from #tb1 group by datepart(hh,[time])) b
where datepart(hh,a.[TIME])=b.[hh]
order by b.shuliang desc
(
[ID] INT ,
[INFO] VARCHAR(50) ,
[TIME] DATETIME
)
INSERT INTO #tb1
SELECT 1 ,
'Read' ,
'2011-02-28 15:33:33'
UNION ALL
SELECT 2 ,
'Read' ,
'2011-02-14 09:33:33'
UNION ALL
SELECT 3 ,
'Read' ,
'2011-02-01 23:33:33'
UNION ALL
SELECT 4 ,
'Read' ,
'2011-02-01 23:34:33'
select a.* from #tb1 a,
(select datepart(hh,[time]) [hh],count(1) shuliang from #tb1 group by datepart(hh,[time])) b
where datepart(hh,a.[TIME])=b.[hh]
order by b.shuliang desc/*
ID INFO TIME
----------- -------------------------------------------------- -----------------------
3 Read 2011-02-01 23:33:33.000
4 Read 2011-02-01 23:34:33.000
2 Read 2011-02-14 09:33:33.000
1 Read 2011-02-28 15:33:33.000
CREATE TABLE #tb1
(
[ID] INT ,
[INFO] VARCHAR(50) ,
[TIME] DATETIME
)
INSERT INTO #tb1
SELECT 1 ,
'Read' ,
'2011-02-28 15:33:33'
UNION ALL
SELECT 2 ,
'Read' ,
'2011-02-14 09:33:33'
UNION ALL
SELECT 3 ,
'Read' ,
'2011-02-01 23:33:33'
UNION ALL
SELECT 4 ,
'Read' ,
'2011-02-01 23:34:33'
UNION ALL
SELECT 5 ,
'Read' ,
'2011-02-01 01:34:33'
UNION ALL
SELECT 6 ,
'Read' ,
'2011-02-01 02:34:33'
SELECT SUBSTRING(CONVERT(VARCHAR(50), [TIME], 120), 12, 2)+N'点--'+CONVERT (VARCHAR(10),CONVERT(INT ,SUBSTRING(CONVERT(VARCHAR(50),[TIME], 120), 12, 2))+1)+N'点' AS [时],
COUNT(1) AS [访问量]
FROM #tb1
WHERE 1 = 1
GROUP BY SUBSTRING(CONVERT(VARCHAR(50), [TIME], 120), 12, 2)
ORDER BY SUBSTRING(CONVERT(VARCHAR(50), [TIME], 120), 12, 2) ASC
DROP TABLE #tb1