col1 col2
-------------------
A1 2010xx
A2 2010cx
A1 2010ddd
A2 2011dfsd
A3 2011rtrr
---------------------
需要的结果（按年统计每个项目的行数， col2的前4位是年份）
项目 2010 2011
A1 2 0
A2 1 1
A3 0 1
------解决方案--------------------
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 (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([col1] varchar(2),[col2] varchar(8))
insert [huang]
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,sum(CASE WHEN LEFT(col2,4)='2010' THEN 1 ELSE 0 END ) [2010],sum(CASE WHEN LEFT(col2,4)='2011' THEN 1 ELSE 0 END ) [2011]
from [huang]
GROUP BY col1
----------------结果----------------------------
/*
col1 2010 2011
---- ----------- -----------
A1 2 0
A2 1 1
A3 0 1
*/
------解决方案--------------------
--还有一个方法：动态sql语句
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'
declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim(col2)+']=sum(case col2 when '''+rtrim(col2)+''' then 1 else 0 end)'