求一个sql统计行数的语句
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
/*
col120102011
A120
A211
A301
*/
----------------------------------------------------------------
-- 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)'
from (select col1,col2=LEFT(col2,4) from #tb)t group by col2
exec('select col1'+@sql+'from (select col1,col2=LEFT(col2,4) from #tb)t group by col1' )
/*
col120102011
------------------------
A120
A211
A301
*/
CREATE TABLE #Temp
(
Col1CHAR(2)
,Col2VARCHAR(20)
)
INSERT INTO #Temp
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(MAX),@Sql1 VARCHAR(MAX)
SELECT @Sql=ISNULL(@Sql+',','')+'['+LEFT(Col2,4)+']' FROM #Temp GROUP BY LEFT(Col2,4)
SELECT @Sql1=ISNULL(@Sql1+',','')+'ISNULL('+'['+LEFT(Col2,4)+']'+',0) AS '+'['+LEFT(Col2,4)+']' FROM #Temp GROUP BY LEFT(Col2,4)
SELECT @Sql='SELECT Col1,'+@Sql1+'
FROM (
SELECT Col1
,LEFT(Col2,4) AS Years
,COUNT(*) Cnt
FROM #Temp
GROUP BY Col1
,LEFT(Col2,4) ) AS A
PIVOT (Max(Cnt) FOR Years IN('+@Sql+')) p'
EXEC(@Sql)
DROP TABLE #Temp
/*
Col120102011
A120
A211
A301
*/
