一个table中有2列 一列type 一列value
形式可能如下
type value
1 10
2 20
2 30
3 40
1 50
现在我想生成一张表 求得每个类型的和,value1,value2,value3
我想在一个select语句中完成3个统计,可以吗?
比如说形式如下:
select sum(value) value1,sun(value) value2,sum(value) value3 from table where .....
可以实现吗?如果可以在where中怎么写条件?
3q
形式可能如下
type value
1 10
2 20
2 30
3 40
1 50
现在我想生成一张表 求得每个类型的和,value1,value2,value3
我想在一个select语句中完成3个统计,可以吗?
比如说形式如下:
select sum(value) value1,sun(value) value2,sum(value) value3 from table where .....
可以实现吗?如果可以在where中怎么写条件?
3q
select type,sum([value]) [value]
from [table]
group by type
create table tb(type int,value int)
insert into tb select 1, 10
insert into tb select 2, 20
insert into tb select 2, 30
insert into tb select 3, 40
insert into tb select 1, 50
go
select type,1 as value1,sum(case when type=1 then value else 0 end)sv1,
2 as value2,sum(case when type=2 then value else 0 end)sv2,
3 as value3,sum(case when type=3 then value else 0 end)sv3
from tb group by type
/*
type value1 sv1 value2 sv2 value3 sv3
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 60 2 0 3 0
2 1 0 2 50 3 0
3 1 0 2 0 3 40(3 行受影响)*/go
drop table tb
select [type],sum(value) value
from [table]
group by [type]
type是保留字,values是,value不是保留字。
create table tb(type int,value int)
insert into tb select 1, 10
insert into tb select 2, 20
insert into tb select 2, 30
insert into tb select 3, 40
insert into tb select 1, 50
go
select sum(case when type=1 then [value] else 0 end) values1,
sum(case when type=2 then [VALUE] else 0 end)values2,
sum(case when type=3 then [VALUE] else 0 end)values3
from tb
select type,sum(value) as total from tb group by type
create table #tb
(type nvarchar(10), value int)
insert #tb
select 1, 10 union all
select 2, 20 union all
select 2, 30 union all
select 3, 40 union all
select 1, 50--靜態
select
sum(case when [type]=1 then value else 0 end) as sum1,
sum(case when [type]=2 then value else 0 end) as sum2,
sum(case when [type]=3 then value else 0 end) as sum3
from #tb--動態
declare @sql nvarchar(4000)
set @sql=''
select @sql=@sql+',sum(case when [type]='+ltrim([type])+' then value else 0 end) as sum'+ltrim([type])
from (select distinct [type] from #tb) as a
set @sql='select '+stuff(@sql,1,1,'')+' from #tb'
exec(@sql)--sum1 sum2 sum3
------------- ----------- -----------
--60 50 40
sum(case when [type]=1 then value else 0 end) as value1,
sum(case when [type]=2 then value else 0 end) as value2,
sum(case when [type]=3 then value else 0 end) as value3
from tb