select type1=sum(case userMoneyType when 'type1' then userMoney end)
,type2=sum(case userMoneyType when 'type2' then userMoney end)
,type3=sum(case userMoneyType when 'type3' then userMoney end)
,[sum]=sum(userMoney)
,[average]=avg(userMoney)
from table1
,type2=sum(case userMoneyType when 'type2' then userMoney end)
,type3=sum(case userMoneyType when 'type3' then userMoney end)
,[sum]=sum(userMoney)
,[average]=avg(userMoney)
from table1
,type2=sum(case userMoneyType when 'type2' then userMoney end)
,type3=sum(case userMoneyType when 'type3' then userMoney end)
,[sum]=sum(userMoney)
,[average]=avg(userMoney)
from table1
where username = 'David'
,type2=sum(case userMoneyType when 'type2' then userMoney end)
,type3=sum(case userMoneyType when 'type3' then userMoney end)
,[sum]=sum(userMoney)
,[average]=avg(userMoney)
from table1
where username = 'David'
group by username
type2 = table1 里 David 所有type2类型金钱的总和
type3 = table1 里 David 所有type3类型金钱的总和
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\楼上的不对
楼主是要求每个用户的type1,tupe2,type3的和
insert table1 select 'David',800.23,'type1' union all
select 'Mark',300,'type1' union all
select 'Perter',1000,'type2' union all
select 'Mark',250,'type3' union all
select 'David',800,'type2' union all
select 'David',850,'type3' union all
select 'Mark',764.23,'type1' union all
select 'Perter',81.22,'type2' union all
select 'Perter',1023.23,'type1' union all
select 'John',765.22,'type2'
declare @str varchar(8000)
set @str=''
select @str=@str+',['+Type+']=sum(case type when '''+type+''' then Money else 0 end)' from (select distinct type from table1 where name='david') a
set @str=right(@str,len(@str)-1)
set @str='select '+@str+',[sum]=sum(money)'+',[average]=avg(money)'+'from table1 where name=''david'''
execute (@str)
,type2=sum(case userMoneyType when 'type2' then userMoney end)
,type3=sum(case userMoneyType when 'type3' then userMoney end)
,[sum]=sum(userMoney)
,[average]=avg(userMoney)
from table1
where username = 'David'
,type2=sum(case userMoneyType when 'type2' then userMoney end)
,type3=sum(case userMoneyType when 'type3' then userMoney end)
,[sum]=sum(userMoney)
,[average]=avg(userMoney)
from table1
where username='David'
declare @table1 table(userName varchar(10),userMoney money,userMoneyType varchar(10))
insert into @table1
select 'David ',800.23,'type1'
union all select 'Mark',300,'type1'
union all select 'Perter',1000,'type2'
union all select 'Mark',250,'type3'
union all select 'David ',800,'type2'
union all select 'David ',850,'type3'
union all select 'Mark',764.23,'type1'
union all select 'Perter',81.22,'type2'
union all select 'Perter',1023.23,'type1'
union all select 'John',765.22,'type2'--查询
select type1=sum(case userMoneyType when 'type1' then userMoney end)
,type2=sum(case userMoneyType when 'type2' then userMoney end)
,type3=sum(case userMoneyType when 'type3' then userMoney end)
,[sum]=sum(userMoney)
,[average]=avg(userMoney)
from @table1
where username='David'/*--测试结果type1 type2 type3 sum average
--------------------- --------------------- --------------------- --------------------- ---------------------
800.2300 800.0000 850.0000 2450.2300 816.7433(所影响的行数为 1 行)--*/
select type1=sum(case userMoneyType when 'type1' then userMoney end)
,type2=sum(case userMoneyType when 'type2' then userMoney end)
,type3=sum(case userMoneyType when 'type3' then userMoney end)
,[sum]=sum(userMoney)
,[average]=avg(userMoney)
from table1
,type2=sum(case userMoneyType when 'type2' then userMoney end)
,type3=sum(case userMoneyType when 'type3' then userMoney end)
,[sum]=sum(userMoney)
,[average]=avg(userMoney)
,[NewColumn]=userMoney*0.26
from table1
where username = 'David'
group by username
,type2=sum(case userMoneyType when 'type2' then userMoney end)
,type3=sum(case userMoneyType when 'type3' then userMoney end)
,[sum]=sum(userMoney)
,[average]=avg(userMoney)
,[NewColumn]=userMoney*0.26
from table1
where username = 'David'
group by username------------------你想达到什么目的啊,这样写首先不会错的啊