如题。比如有下面的数据
reciveTime GSMNum CH
2011-07-15 11:25:42 5 77
2011-07-15 11:25:36 2 60
2011-07-15 11:23:14 2 60
2011-07-15 11:19:39 2 60
2011-07-15 11:18:07 2 60
2011-07-15 11:11:45 2 60
2011-07-15 11:05:40 2 60
2011-07-15 11:04:09 2 60
2011-07-15 10:49:16 2 60
2011-07-15 10:19:37 2 60
2011-07-15 10:12:10 2 60
2011-07-13 17:45:53 2 77
2011-07-13 17:44:40 2 77
2011-07-13 17:13:56 2 77
2011-07-13 16:42:03 2 77
2011-07-13 16:19:10 2 77
我要统计最近一天的CH值的分布,并统计这几个值各有多少个数。
比如举例如
2011-07-15的CH的值分别是
日期 CH 个数
2011-07-15 77 3
2011-07-15 60 12
就想得到这样的结果。谢谢。
reciveTime GSMNum CH
2011-07-15 11:25:42 5 77
2011-07-15 11:25:36 2 60
2011-07-15 11:23:14 2 60
2011-07-15 11:19:39 2 60
2011-07-15 11:18:07 2 60
2011-07-15 11:11:45 2 60
2011-07-15 11:05:40 2 60
2011-07-15 11:04:09 2 60
2011-07-15 10:49:16 2 60
2011-07-15 10:19:37 2 60
2011-07-15 10:12:10 2 60
2011-07-13 17:45:53 2 77
2011-07-13 17:44:40 2 77
2011-07-13 17:13:56 2 77
2011-07-13 16:42:03 2 77
2011-07-13 16:19:10 2 77
我要统计最近一天的CH值的分布,并统计这几个值各有多少个数。
比如举例如
2011-07-15的CH的值分别是
日期 CH 个数
2011-07-15 77 3
2011-07-15 60 12
就想得到这样的结果。谢谢。
select convert(varchar(10),receivetime,120) date,CH,count(*) cnt
from tb
where convert(varchar(10),receivetime,120) =
convert(varchar(10),(select max(receivetime) from tb),120)
group by convert(varchar(10),receivetime,120)
CH=(select top 1 CH from tb
where convert(varchar(10),reciveTime,120)=convert(varchar(10),a.reciveTime,120)
order by reciveTime desc),
个数=count(*)
from tb a group by convert(varchar(10),reciveTime,120)
declare @t table (reciveTime datetime,GSMNum int,CH int)
insert into @t
select '2011-07-15 11:25:42',5,77 union all
select '2011-07-15 11:25:36',2,60 union all
select '2011-07-15 11:23:14',2,60 union all
select '2011-07-15 11:19:39',2,60 union all
select '2011-07-15 11:18:07',2,60 union all
select '2011-07-15 11:11:45',2,60 union all
select '2011-07-15 11:05:40',2,60 union all
select '2011-07-15 11:04:09',2,60 union all
select '2011-07-15 10:49:16',2,60 union all
select '2011-07-15 10:19:37',2,60 union all
select '2011-07-15 10:12:10',2,60 union all
select '2011-07-13 17:45:53',2,77 union all
select '2011-07-13 17:44:40',2,77 union all
select '2011-07-13 17:13:56',2,77 union all
select '2011-07-13 16:42:03',2,77 union all
select '2011-07-13 16:19:10',2,77select 日期=convert(varchar(10),reciveTime,120),
CH,个数=count(1) from @t a where convert(varchar(10),reciveTime,120)=
(
select max(convert(varchar(10),reciveTime,120)) from @t
)
group by convert(varchar(10),reciveTime,120),CH
/*
日期 CH 个数
---------- ----------- -----------
2011-07-15 60 10
2011-07-15 77 1
*/
where convert(varchar(10),reciveTime,120)=(select top 1 convert(varchar(10),reciveTime,120) from tb order by recivetime desc)
group by CH
系统提示:CH在列表中无效,既不包含在聚合函数中,也不包含在Group by语句中.
Group by加个CH就运行正确了
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-08-09 15:14:16
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([reciveTime] datetime,[GSMNum] int,[CH] int)
insert [tb]
select '2011-07-15 11:25:42',5,77 union all
select '2011-07-15 11:25:36',2,60 union all
select '2011-07-15 11:23:14',2,60 union all
select '2011-07-15 11:19:39',2,60 union all
select '2011-07-15 11:18:07',2,60 union all
select '2011-07-15 11:11:45',2,60 union all
select '2011-07-15 11:05:40',2,60 union all
select '2011-07-15 11:04:09',2,60 union all
select '2011-07-15 10:49:16',2,60 union all
select '2011-07-15 10:19:37',2,60 union all
select '2011-07-15 10:12:10',2,60 union all
select '2011-07-13 17:45:53',2,77 union all
select '2011-07-13 17:44:40',2,77 union all
select '2011-07-13 17:13:56',2,77 union all
select '2011-07-13 16:42:03',2,77 union all
select '2011-07-13 16:19:10',2,77
--------------开始查询--------------------------
select
CONVERT(varchar(10),reciveTime,120) as reciveTime,ch,COUNT(1) as 个数
from
tb t
where
CONVERT(varchar(10),reciveTime,120)=(select CONVERT(varchar(10),max(reciveTime),120) from tb)
group by
CONVERT(varchar(10),reciveTime,120),CH----------------结果----------------------------
/* reciveTime ch 个数
---------- ----------- -----------
2011-07-15 60 10
2011-07-15 77 1
*/