目的就是要计算各个length的长度,并且根据key_id分组。相关sql如下:
======================================================================================
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([key_id] varchar(100),[length] int)
insert [tb] select 'aa',10
union all select 'aa',10
union all select 'aa',10
union all select 'aa',20
union all select 'bb',15
union all select 'cc',15
union all select 'dd',20select * from [tb]select id=identity(int),* into # from tbselect [key_id],sum(case length when 10 then 1 else 0 end) '10',
sum(case length when 20 then 1 else 0 end) '20',
sum(case length when 15 then 1 else 0 end) '15',
sum(case length when 25 then 1 else 0 end) '25' from #
group by [key_id]
drop table #=============================================================================
我现在碰到的问题是:length如果是固定的话,可以一句一句写case,但是length字段的值是不确定的。需要怎么写? 请各位大侠帮帮忙
======================================================================================
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([key_id] varchar(100),[length] int)
insert [tb] select 'aa',10
union all select 'aa',10
union all select 'aa',10
union all select 'aa',20
union all select 'bb',15
union all select 'cc',15
union all select 'dd',20select * from [tb]select id=identity(int),* into # from tbselect [key_id],sum(case length when 10 then 1 else 0 end) '10',
sum(case length when 20 then 1 else 0 end) '20',
sum(case length when 15 then 1 else 0 end) '15',
sum(case length when 25 then 1 else 0 end) '25' from #
group by [key_id]
drop table #=============================================================================
我现在碰到的问题是:length如果是固定的话,可以一句一句写case,但是length字段的值是不确定的。需要怎么写? 请各位大侠帮帮忙
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html?33238(总结帖子)
http://topic.csdn.net/u/20090912/14/25d2e1b2-f352-4713-8618-d3433ba27bef.html?99104(经典帖子)
go
create table [tb]([key_id] varchar(100),[length] int)
insert [tb] select 'aa',10
union all select 'aa',10
union all select 'aa',10
union all select 'aa',20
union all select 'bb',15
union all select 'cc',15
union all select 'dd',20 declare @sql varchar(MAX);select @sql = isnull(@sql+',','')+'['+rtrim([length])+']'
from tb
group by [length]
exec('select * from tb pivot(count([length]) for [length] in('+@sql+')) as pvt')
go
create table [tb]([key_id] varchar(100),[length] int)
insert [tb] select 'aa',10
union all select 'aa',10
union all select 'aa',10
union all select 'aa',20
union all select 'bb',15
union all select 'cc',15
union all select 'dd',20 select id=identity(int),* into # from tb
declare @sql varchar(8000)
set @sql = 'select key_id '
select @sql = @sql + ' , sum(case [length] when ''' + ltrim([length]) + ''' then 1 else 0 end) [' + ltrim([length]) + ']'
from (select distinct [length] from tb) as a
set @sql = @sql + ' from # group by key_id'
exec(@sql)
drop table #/*key_id 10 15 20
---------------------------------------------------------------------------------------------------- ----------- ----------- -----------
aa 3 0 1
bb 0 1 0
cc 0 1 0
dd 0 0 1(4 行受影响)*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([key_id] varchar(100),[length] int)
insert [tb] select 'aa',10
union all select 'aa',10
union all select 'aa',10
union all select 'aa',20
union all select 'bb',15
union all select 'cc',15
union all select 'dd',20 select id=identity(int),* into # from tb
declare @sql varchar(8000)
set @sql = 'select key_id '
select @sql = @sql + ' , sum(case [length] when ''' + ltrim([length]) + ''' then [length] else 0 end) [' + ltrim([length]) + ']'
from (select distinct [length] from tb) as a
set @sql = @sql + ' from # group by key_id'
exec(@sql)
drop table #/*key_id 10 15 20
---------------------------------------------------------------------------------------------------- ----------- ----------- -----------
aa 30 0 20
bb 0 15 0
cc 0 15 0
dd 0 0 20(4 行受影响)
*/