select good=sum(case value when 'good' then 1 else 0 end)
,better=sum(case value when 'better' then 1 else 0 end)
,best=sum(case value when 'best' then 1 else 0 end)
from 表
,better=sum(case value when 'better' then 1 else 0 end)
,best=sum(case value when 'best' then 1 else 0 end)
from 表
昨天我见到一个SQL语句就是用union解决的,只是我没有记住是怎么写的。
我觉得很好啊!如果用union直接用是不可以实现的!它只能纵向接!
declare @s varchar(8000)
set @s=''''
select @s=@s+'',[''+rtrim(value)+'']=sum(case value when ''''''+rtrim(value)+'''''' then 1 else 0 end)''
from 表 group by value
set @s=stuff(@s,1,1,'''')
exec(''select ''+@s+'' from 表'')')
……
do while xxx.eof=false
strsql=strsql+xxx.fields(0)+"……"
xxx.movenext
loop
create table 表(id int,value varchar(10))
insert 表 select 1,'good'
union all select 2,'good'
union all select 3,'better'
union all select 4,'best'
union all select 5,'good'
union all select 6,'better'
go--取巧的一句
exec('set nocount on
declare @s varchar(8000)
set @s=''''
select @s=@s+'',[''+rtrim(value)+'']=sum(case value when ''''''+rtrim(value)+'''''' then 1 else 0 end)''
from 表 group by value
set @s=stuff(@s,1,1,'''')
exec(''select ''+@s+'' from 表'')')
go--删除测试
drop table 表/*--测试结果best better good
----------- ----------- -----------
1 2 3
--*/
declare @s varchar(800),@str varchar(100)
set @str='(select count(*) from abc where value='
set @s=''
select @s=@s+','+value+'='+@str+''''+value+''''+')' from abc group by value
select @s=stuff (@s,1,1,'')
exec ('select '+@s)
sum(decode(value,'better',a)) as better,
sum(decode(value,'best',a)) as best
from (select count(*) as a,value from test.hzwtest group by value) 不过想想看,没有 zjcxc(邹建) 兄弟的好。
我觉得他的方法应该是标准的行转列一句话的方法了。我的sql从本质上和他的一样,只不过复杂了很多:)
pfpf
,better=sum(case value when 'better' then 1 else 0 end)
,best=sum(case value when 'best' then 1 else 0 end)
from 表
这样很好啊,又简洁又明了!
from yourtable
insert @a select 1,'good'
union all select 2,'good'
union all select 3,'better'
union all select 4,'best'
union all select 5,'good'
union all select 6,'better'select count(case when value='good' then id end) as good
,count(case when value='better' then id end) as better
,count(case when value='best' then id end ) as best
from @a
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[table1]
create table table1(id int not null identity primary key ,value varchar(20) not null)goinsert into table1
select 'good' union all
select 'good' union all
select 'better' union all
select 'best' union all
select 'good' union all
select 'better'
goselect * from table1
/*
id value
1 good
2 good
3 better
4 best
5 good
6 better*/
----------方法一-------
select sum(good) good,sum(better) better,sum(best) best from
(
select good=count(1),better=0,best=0 from table1 where value='good'
union all
select good=0,better=count(1),best=0 from table1 where value='better'
union all
select good=0,better=0,best=count(1) from table1 where value='best'
)a
/*
good better best
3 2 1*/
----------方法二-------
select good=sum(case value when 'good' then 1 end),better=sum(case value when 'better' then 1 end),best=sum(case value when 'best' then 1 end) from table1
/*
good better best
3 2 1*/