求一个sql统计行数的语句

javapopo 2013-10-30 01:37:33
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
...全文
265 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
Yole 2013-10-30
  • 打赏
  • 举报
回复
--建表
create table table1(col1 varchar(50),col2 varchar(50))
insert into table1
select 'A1','2010xx' union all
select 'A2','2010cx' union all
select 'A1','2010ddd' union all
select 'A2','2011dfsd' union all
select 'A3','2011rtrr'

--select * from table1
--处理表
select col1,LEFT(col2,4) col2,COUNT(*) as con into table2
from table1 
group by col1,LEFT(col2,4)

--select * from table2
--横转纵查询处理
declare @sql varchar(500)
set @sql='select col1'
select @sql=@sql+',max(case col2 when '''+ col2+''' then con else 0 end) ['+col2+']'
from (select distinct col2 from table2) a
set @sql=@sql + ' from table2 group by col1'
exec(@sql)
drop table table1
drop table table2
(5 行受影响) (4 行受影响) col1 2010 2011 -------------------------------------------------- ----------- ----------- A1 2 0 A2 1 1 A3 0 1 (3 行受影响)
  • 打赏
  • 举报
回复

if OBJECT_ID('tb') is not null 
   drop  table tb
go

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 nvarchar(3000);  
  
set @sql = '';  
  
select @sql =   
   @sql + ',count(case when left(col2,4)=''' + left(col2,4) + 
          ''' then 1 else null end) as ['+left(col2,4)+ ']'     
from tb  
group by left(col2,4)
  
select @sql = 'select col1 as 项目'+@sql +  
              ' from tb group by col1'  

--输出语句  
select @sql         

exec(@sql)         
/*
项目 	2010	2011
A1	    2	    0
A2	    1	    1
A3	    0	    1
*/
chen357313771 2013-10-30
  • 打赏
  • 举报
回复
CREATE TABLE #Temp 
(
	Col1	CHAR(2)
	,Col2	VARCHAR(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

/*
Col1	2010	2011
A1	2	0
A2	1	1
A3	0	1
*/
Andy__Huang 2013-10-30
  • 打赏
  • 举报
回复
--还有一个方法:动态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' )

/*
col1	2010	2011
------------------------
A1	2	0
A2	1	1
A3	0	1
*/
發糞塗牆 2013-10-30
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- 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
*/
-Tracy-McGrady- 2013-10-30
  • 打赏
  • 举报
回复

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'

--查询语句

select col1 as '项目'
,SUM(case when LEFT(col2,4)='2010' then 1 else 0 end) as '2010'
,SUM(case when LEFT(col2,4)='2011' then 1 else 0 end) as '2011'
from tb
group by col1

--结果
项目    2010        2011
----- ----------- -----------
A1    2           0
A2    1           1
A3    0           1

(3 行受影响)

drop table tb
Andy__Huang 2013-10-30
  • 打赏
  • 举报
回复

;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
*/
-Tracy-McGrady- 2013-10-30
  • 打赏
  • 举报
回复

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

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧