解决方案 »
- 求一个SQL句子,看看能不能实现这个要求?
- 如何批量更新一个表中某一字段的值为另一表某一字段的值
- 数据存储长度不够?
- sql2005中使用SUM函数,计算结果不正确
- 为什么在运行SQL远程视图的软件中经常出现“连接错误:[Microsoft][ODBC SQL Server Driver]通讯连接失败”出错?
- 怎么删除sql server2005 的日志文件???
- 如何使具有相同栏位值的行显示相同序列号
- sqlserver7.0的text类型能容纳多少字?
- 怎样将日期转成字符 ?要求转成这种样子:'2002-5-8' 我用convert(char,'2002-5-8')却转成:Jul 12 2002 11:54AM ,怎么办?
- slqserver如何读写文件
- 连接SQL服务器前需要登录对方电脑.
- 列别名在Having子句中不能用?
sum(case when [type]='中午' then 1 else 0 end )中午,
sum(case when [type]='下午' then 1 else 0 end )下午,
sum(case when [type]='晚上' then 1 else 0 end )晚上
from tb
if object_id('t1')is not null
drop table t1
go
create table t1(
id int identity,
type char(4) check (type in('上午','中午','下午','晚上')),
time datetime
)
--插入数据
insert into t1
select '上午','2010-12-10 08:20:00' union all
select '上午','2010-12-10 11:15:00' union all
select '中午','2010-12-10 12:30:00' union all
select '下午','2010-12-10 15:00:00' union all
select '下午','2010-12-10 17:00:00' union all
select '晚上','2010-12-10 19:00:00' union all
select '晚上','2010-12-10 22:30:00'
--测试
select type ,count(*) '总记录数' from t1 group by type
type 总记录数
上午 2
晚上 2
下午 2
中午 1
sum(case when [type]='中午' then 1 else 0 end ) as 中午,
sum(case when [type]='下午' then 1 else 0 end ) as 下午,
sum(case when [type]='晚上' then 1 else 0 end ) as 晚上
from 表名
drop table tbcreate table tb(
id int identity,
type char(4) check (type in('sw','zw','xw','ws')),
time int
)
--插入数据
insert into tb values ('sw',1)
insert into tb values ('sw',2 )
insert into tb values ('zw',1)
insert into tb values ('zw',1)
insert into tb values ('zw',1)
insert into tb values ('xw',2 )
insert into tb values ('xw',3)
insert into tb values ('ws',3)
insert into tb values ('ws',4)
select * from tb
select sum(case when [type]='sw' then 1 else 0 end ) as morning,
cast( sum(case when [type]='sw' then time else 0 end ) as varchar)+' hours' as morningTimeSum ,
sum(case when [type]='zw' then 1 else 0 end ) as noon,
cast(sum(case when [type]='zw' then time else 0 end )as varchar)+' hours' as NoonTimeSum ,
sum(case when [type]='xw' then 1 else 0 end ) as afternoon,
cast(sum(case when [type]='xw' then time else 0 end )as varchar) +' hours' as afternoonTimeSum ,
sum(case when [type]='ws' then 1 else 0 end ) as night,
cast(sum(case when [type]='ws' then time else 0 end )as varchar) +' hours' as nightTimeSum
from tb