cust_sn, writedate ,isemployee 三个字段
10023 2014-3-4 13:30 true
10023 2014-3-4 15:35 true
10025 2014-3-5 09:30 false
10077 2014-3-5 15:30 true
10078 2014-3-6 15:35 false
10099 2014-3-6 15:25 false
.
.
.
------解决思路----------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-11-26 14:35:47
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([cust_sn] int,[writedate] datetime,[isemployee] varchar(5))
insert [huang]
select 10023,'2014-3-4 13:30','true' union all
select 10023,'2014-3-4 15:35','true' union all
select 10025,'2014-3-5 09:30','false' union all
select 10077,'2014-3-5 15:30','true' union all
select 10078,'2014-3-6 15:35','false' union all
select 10099,'2014-3-6 15:25','false'
--------------开始查询--------------------------
select [cust_sn],convert(varchar(10),[writedate],23)[writedate],sum(case when [isemployee]='true' then 1 else 0 end )'会员',sum(case when [isemployee]='false' then 1 else 0 end )'非会员'
from [huang]
group by [cust_sn],convert(varchar(10),[writedate],23)
----------------结果----------------------------
/*
cust_sn writedate 会员 非会员
----------- ---------- ----------- -----------
10023 2014-03-04 2 0
10025 2014-03-05 0 1
10077 2014-03-05 1 0
10078 2014-03-06 0 1
10099 2014-03-06 0 1
*/
------解决思路----------------------
--稍微调整下大版的就行了吧
select convert(varchar(20),writedate,23),count(isemployee) as 总次数,
sum(case when isemployee='true' then 1 else 0 end) as 员工,
sum(case when isemployee='false' then 1 else 0 end) as 非员工
from table_name
group by convert(varchar(20),writedate,23)