表:
addtime msgtype
2006-11-06 11:12:00 4
2006-11-06 11:12:00 1
2006-11-07 11:12:00 4
2006-11-07 11:12:00 4
2006-11-08 11:12:00 2
2006-11-09 11:12:00 4要得到如下结果:
在2006-11-06至2006-11-09时间内把msgtype=4的统计出来,并列出当天msgtype=4的累计个数。如下:
addtime countmsgtype
2006-11-06 1
2006-11-07 2
2006-11-09 1
还要显示在2006-11-06至2006-11-09时间内总的个数:
msgtypeZS
4
addtime msgtype
2006-11-06 11:12:00 4
2006-11-06 11:12:00 1
2006-11-07 11:12:00 4
2006-11-07 11:12:00 4
2006-11-08 11:12:00 2
2006-11-09 11:12:00 4要得到如下结果:
在2006-11-06至2006-11-09时间内把msgtype=4的统计出来,并列出当天msgtype=4的累计个数。如下:
addtime countmsgtype
2006-11-06 1
2006-11-07 2
2006-11-09 1
还要显示在2006-11-06至2006-11-09时间内总的个数:
msgtypeZS
4
解决方案 »
- sql查询问题
- 关于SQL2005中无法打开DTS包的问题。
- sql server 2000的查询分析器可以连接sql server 2005吗?
- sql安装在xp pro版上是不是客户端连接数就被限制为10个了!
- 存储过程游标问题
- 无法执行该操作,因为链接服务器 "link" 的 OLE DB 访问接口 "SQLNCLI" 无法启动分布式事务
- 关于SQL Server does not exist or access denied的问题
- 急,小弟刚学,请各位大虾帮忙,我用ADO连SQL SERVER 要实现查询的功能,我该怎么实现啊?表结构如下
- group by 相关--非常急!!
- 在pb7开发环境中怎样连接sql server2000?
- 如何join表查询,取最新的价格
- 快来帮我看看,已经修改n遍了,怎么还不行啊
from 表
where msgtype=4 and addtime between '2006-11-06' and '2006-11-09'
from 表
where msgtype=4 and addtime between '2006-11-06' and '2006-11-09'
group by addtime
from
(select addtime, count(msgtype) as countmsgtype
from 表
where msgtype=4 and addtime between '2006-11-06' and '2006-11-09'
group by addtime
) as x
group by x.addtime
这个显示的结果是这样的,我不要这样的结果.
addtime countmsgtype
2006-11-06 1
2006-11-07 1
2006-11-07 1
2006-11-09 1
你再试试,我试了是正确的啊select addtime, count(msgtype) as countmsgtype
from 表
where msgtype=4 and addtime between '2006-11-06' and '2006-11-09'
group by addtime
from 表
where msgtype=4 and addtime between '2006-11-06' and '2006-11-09'
group by convert(char,addtime,120)
from 表
where msgtype=4 and addtime between '2006-11-06' and '2006-11-09'
group by convert(char,addtime,102)
addtime countmsgtype
2006-11-07 1
2006-11-07 1我现在要这样的
addtime countmsgtype
2006-11-07 2
insert into @t select '2006-11-06 11:12:00',4
union all select '2006-11-06 11:12:00',1
union all select '2006-11-07 11:12:00',4
union all select '2006-11-07 11:12:00',4
union all select '2006-11-08 11:12:00',2
union all select '2006-11-09 11:12:00',4
select addtime=convert(varchar(10),addtime,120),countmsgtype=count(1) from @t
where msgtype=4
group by convert(varchar(10),addtime,120)
union all
select '合计',count(*)
from @t
where msgtype=4/*(所影响的行数为 6 行)addtime countmsgtype
---------- ------------
2006-11-06 1
2006-11-07 2
2006-11-09 1
合计 4(所影响的行数为 4 行)*/
msgtypeZS
4
Sum(case when addtime = '2006-11-07' and msgtype=4 then 1) as count2,
Sum(case when addtime = '2006-11-08' and msgtype=4 then 1) as count3,
Sum(case when addtime = '2006-11-09' and msgtype=4 then 1) as count4,
Sum(case when addtime between '2006-11-06' and '2006-11-09' and msgtype=4 then 1) as count5
From table
'all'
else
addtime
end as addtime,
sum(num) as countmsgtype
from (
select convert(varchar(10),addtime,120) as addtime, 1 as num
from table
where msgtype=4
and addtime between '2006-11-06 00:00:00' and '2006-11-09 23:59:59'
) a
group by addtime rollup
--------------------------------------------------------------
注:rollup 为汇总计算,生成汇总数据;
grouping()用于判断是否为汇总记录
convert为数据转换函数
convert(varchar(10),addtime,120)是将时间类型addtime转换为‘yyyy-MM-dd hh:mm:ss.fff’格式,再转换成varchar(10)类型。
from 表
where msgtype=4 and addtime between '2006-11-06' and '2006-11-09'
group by convert(char,addtime,102)
union all
select '累计',count(*) as countmsgtype
from 表
where msgtype=4
from 表
where msgtype=4 and addtime between '2006-11-06' and '2006-11-09'
group by convert(char,addtime,102)
union all
select '累计',count(*) as countmsgtype
from 表
where msgtype=4 and addtime between '2006-11-06' and '2006-11-09'
set @sql=''
select @sql=@sql+'select ''' + cast(addtime as nvarchar) + ''' as 日期,count(*) as 总和 from 表 where addtime =''' + cast(addtime as nvarchar) + ''' where msgtype='''+msgtype+''''+' union ' from 表 order by addtime desc
set @sql=+left(@sql,len(@sql)-6)
print @sql
exec(@sql)
给分:)
declare @sql nvarchar(max)
set @sql=''select @sql=@sql+'select ''' + cast(addtime as nvarchar) + ''' as 日期,count(*) as 总和 from 表 where addtime =''' + cast(addtime as nvarchar)+'''',@sql=@sql + ' and msgtype='''+msgtype+''' union ' from 表 order by addtime desc
set @sql=+left(@sql,len(@sql)-6)
exec(@sql)
declare @sql nvarchar(max)
set @sql=''
select @sql=@sql+'select ''' + cast(addtime as nvarchar) + ''' as 日期,',@sql=@sql+''''+msgtype+''' as 类别,count(*) as 总和 '+ 'from 表 where addtime =''' ,@sql=@sql+ cast(addtime as nvarchar)+'''',@sql=@sql + ' and msgtype='''+msgtype+''' union ' from 表 order by addtime desc
print @sql
set @sql=+left(@sql,len(@sql)-6)
set @sql='select distinct * from('+@sql+')a'
exec(@sql)
and type=4 group by addtime