日期:2014-05-17 浏览次数:20604 次
if OBJECT_ID('tb') is not null drop table tb
create table tb(col1 varchar(5),col2 varchar(10))
insert into tb
select 'A1','2010xx' union all
select 'A2','2010cx' union all
select 'A1','2010ddd' union all
select 'A2','2011dfsd' union all
select 'A3','2011rtrr'
--查询语句
;with cte as(
select col1,LEFT(col2,4) col2,COUNT(*) as sm
from tb
group by col1,LEFT(col2,4)
)
select col1 as '项目'
,MAX(case when col2='2010' then sm else 0 end) as '2010'
,MAX(case when col2='2011' then sm else 0 end) as '2011'
from cte group by col1
--结果
项目 2010 2011
----- ----------- -----------
A1 2 0
A2 1 1
A3 0 1
(3 行受影响)
drop table tb
;with cte(col1,col2) as
(
select 'A1','2010xx'
union all select 'A2','2010cx'
union all select 'A1','2010ddd'
union all select 'A2','2011dfsd'
union all select 'A3','2011rtrr'
)
select col1,[2010]=sum(case when col2='2010' then 1 else 0 end)
,[2011]=sum(case when col2='2011' then 1 else 0 end)
from (select col1,col2=LEFT(col2,4) from cte)t
group by col1
/*
col1 2010 2011
A1 2 0
A2 1 1
A3 0 1
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-30 13:50:03
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright