工资的总和:select sum(account) as 工资 from 表A where name='工资',
其它的总和:select sum(account) as 工资 from 表A where name<>'工资',
你的我试过了,很满意。不过,出来的结果是2行,能否实现这样的效果:
工资 其他费用
xx xxx
解:
declare @qita real,@gongzi real
select @qita=(select sum(account) as 其它 from 表A where name<>'工资'),
@gongzi==(select sum(account) as 工资 from 表A where name='工资'),
from 表A
create table 表B
{
ID int not null primary key,
工资 real,
其它费用 real,
}
insert into 表B(ID,工资,其它费用)values ('1',@gongzi,@qita)
create table aaa(id int,name char(20),accont int)
insert into aaa
select 1,'车费',20 union all
select 1,'餐费',50 union all
select 2,'津贴',100 union all
select 3,'工资',900
select distinct (select sum(accont) from aaa where name<>'工资')as '工资外的其他所有费用总和',
(select sum(accont) from aaa where name='工资')as '工资总和'
from aaa
use aa
create table test
(
id int,
name varchar(30),
account int
)
insert into test select 1, '车费', 20
insert into test select 2, '津贴', 100
insert into test select 3, '工资', 900
select '工资'= case [name] when '工资' then '工资' else '其它' end,
'cont'= sum(account)
from test
-- where id = '100'
group by case [name] when '工资' then '工资' else '其它' end
drop table test
select [name]=case when [name]='工资' then '工资' else '其他费用' end,
amount=sum(account)
from 表a
--where id=100
group by case when [name]='工资' then '工资' else '其他费用' end