日期:2014-05-17 浏览次数:20485 次
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-12-05 10:57:19
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
go
create table [test]
(
[id] int,
[state] varchar(6),
[time] datetime
)
insert [test]
select 1,'在线','2012-12-04 02:02:02' union all
select 2,'不在线','2012-12-04 04:02:02' union all
select 3,'不在线','2012-12-04 08:02:02' union all
select 4,'不在线','2012-12-05 10:02:02' union all
select 5,'不在线','2012-12-05 12:02:02' union all
select 6,'不在线','2012-12-05 15:02:02'
go
with t
as(
select
convert(varchar(10),[time],120) as [time],
sum(case [state] when '在线' then 1 else 0 end) as 在线,
sum(case [state] when '不在线' then 1 else 0 end) as 不在线,
count(1) as counts
from
test
group by
convert(varchar(10),[time],120)
)
select
[time],
case when 在线>0 then '今日状态为在线' else '今日状态为不在线' end as 今日状态
from
t
/*
time 今日状态
-------------------------------------
2012-12-04 今日状态为在线
2012-12-05 今日状态为不在线
*/
USE test
go
---->生成表tb
--if object_id('tb') is not null