SELECT CONVERT(varchar(10),dt,120) AS dt,
SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) AS type1Count,
SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) AS type2Count,
SUM(CASE WHEN type IN(1,2) THEN 1 ELSE 0 END) AS totalCount
FROM tb
GROUP BY CONVERT(varchar(10),dt,120)
SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) AS type1Count,
SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) AS type2Count,
SUM(CASE WHEN type IN(1,2) THEN 1 ELSE 0 END) AS totalCount
FROM tb
GROUP BY CONVERT(varchar(10),dt,120)
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-12 14:58:27
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (phone BIGINT,dt DATETIME,type INT)
INSERT INTO @tb
SELECT 13867111111,'2009-07-28 22:00:45.000',0 UNION ALL
SELECT 13621111111,'2009-07-28 21:40:00.000',1 UNION ALL
SELECT 13262222222,'2009-07-27 21:34:13.000',1 UNION ALL
SELECT 13933333333,'2009-07-27 21:25:14.000',1 UNION ALL
SELECT 15967321111,'2009-07-27 20:36:02.000',0 UNION ALL
SELECT 13888888888,'2009-07-26 20:19:46.000',0 UNION ALL
SELECT 13357098898,'2009-07-26 19:13:33.000',1--SQL查询如下:SELECT CONVERT(varchar(10),dt,120) AS dt,
SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) AS type1Count,
SUM(CASE WHEN type = 0 THEN 1 ELSE 0 END) AS type2Count,
COUNT(*) AS totalCount
FROM @tb
GROUP BY CONVERT(varchar(10),dt,120)/*dt type1Count type2Count totalCount
---------- ----------- ----------- -----------
2009-07-26 1 1 2
2009-07-27 2 1 3
2009-07-28 1 1 2(3 行受影响)
*/
convert(varchar(10),dt,120) as 日期,
sum(case type when 1 then 1 else 0 end) as type1Count,
sum(case type when 2 then 1 else 0 end) as type2Count,
sum(case when type in(1,2) then 1 else 0 end) as totalCount
from
tb
group by
convert(varchar(10),dt,120)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-09-12 15:03:28
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([phone] bigint,[dt] datetime,[type] int)
insert [tb]
select 13867111111,'2009-07-28 22:00:45.000',0 union all
select 13621111111,'2009-07-28 21:40:00.000',1 union all
select 13262222222,'2009-07-27 21:34:13.000',1 union all
select 13933333333,'2009-07-27 21:25:14.000',1 union all
select 15967321111,'2009-07-27 20:36:02.000',0 union all
select 13888888888,'2009-07-26 20:19:46.000',0 union all
select 13357098898,'2009-07-26 19:13:33.000',1
--------------开始查询--------------------------
select
convert(varchar(10),dt,120) as 日期,
sum(case type when 1 then 1 else 0 end) as type1Count,
sum(case type when 2 then 1 else 0 end) as type2Count,
sum(case when type in(1,2) then 1 else 0 end) as totalCount
from
tb
group by
convert(varchar(10),dt,120)----------------结果----------------------------
/* 日期 type1Count type2Count totalCount
---------- ----------- ----------- -----------
2009-07-26 1 0 1
2009-07-27 2 0 2
2009-07-28 1 0 1(3 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-09-12 15:03:28
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([phone] bigint,[dt] datetime,[type] int)
insert [tb]
select 13867111111,'2009-07-28 22:00:45.000',0 union all
select 13621111111,'2009-07-28 21:40:00.000',1 union all
select 13262222222,'2009-07-27 21:34:13.000',1 union all
select 13933333333,'2009-07-27 21:25:14.000',1 union all
select 15967321111,'2009-07-27 20:36:02.000',0 union all
select 13888888888,'2009-07-26 20:19:46.000',0 union all
select 13357098898,'2009-07-26 19:13:33.000',1
--------------开始查询--------------------------
select
convert(varchar(10),dt,120) as 日期,
sum(case type when 1 then 1 else 0 end) as type1Count,
sum(case type when 2 then 1 else 0 end) as type2Count,
count(1) as totalCount
from
tb
group by
convert(varchar(10),dt,120)----------------结果----------------------------
/* 日期 type1Count type2Count totalCount
---------- ----------- ----------- -----------
2009-07-26 1 0 2
2009-07-27 2 0 3
2009-07-28 1 0 2(3 行受影响)
*/
(phone varchar(16),
dt datetime,
type char(4)
)
goinsert into #mytest
select '123','2009-09-01','0' union all
select '234','2009-09-01','1' union all
select '456','2009-08-31','0' union all
select '567','2009-08-31','1' union all
select '678','2009-08-31','1' union all
select '123','2009-08-30','0' union all
select '123','2009-08-30','1'
goselect dt,type0count=sum(case when type='0' then 1 else 0 end),type1count=sum(case when type='1' then 1 else 0 end),totalcount=count(*) from #mytest group by dt order by dt desc
这个是什么意思,格式化处理?
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-09-12 15:03:28
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([phone] bigint,[dt] datetime,[type] int)
insert [tb]
select 13867111111,'2009-07-28 22:00:45.000',0 union all
select 13621111111,'2009-07-28 21:40:00.000',1 union all
select 13262222222,'2009-07-27 21:34:13.000',1 union all
select 13933333333,'2009-07-27 21:25:14.000',1 union all
select 15967321111,'2009-07-27 20:36:02.000',0 union all
select 13888888888,'2009-07-26 20:19:46.000',0 union all
select 13357098898,'2009-07-26 19:13:33.000',1
--------------开始查询--------------------------
select
convert(varchar(10),dt,120) as 日期,
sum(case type when 1 then 1 else 0 end) as type1Count,
sum(case type when 0 then 1 else 0 end) as type2Count,
count(1) as totalCount
from
tb
group by
convert(varchar(10),dt,120)----------------结果----------------------------
/* 日期 type1Count type2Count totalCount
---------- ----------- ----------- -----------
2009-07-26 1 1 2
2009-07-27 2 1 3
2009-07-28 1 1 2(3 行受影响)*/