急急急急急，在线等....

[wlong567]

创建一张表table_c如下含三列，一列为fname(varchar,80),一列为ftype(varchar,80),一列为fvalue(float)，用代码逐条插入如下表内容:

表table_c:

fname ftype fvalue
张三 奖金 500
李四 奖金 400
张三 应发工资 2500
李四 应发工资 2400

请写一条语句实现插入计算每个人的奖金占工资的比例，执行后该表增加如下两行：

张三 百分比 0.20
李四 百分比 0.17

结果：
fname ftype fvalue
张三 奖金 500.0
李四 奖金 400.0
张三 工资 2500.0
李四 工资 2400.0
李四 百分比 0.17
张三 百分比 0.2

[sanny_txx]

if object_id('tb')is not nulll
    drop table tb
create table tb
(
    fname varchar(20),
    ftype nvarchar(20),
    fvalue int
)

insert into tb
select '张三', '奖金', 500
union all
select '李四', '奖金', 400
union all
select '张三', '应发工资', 2500
union all
select '李四', '应发工资', 2400

select * from tb 
union all
select a.fname,'百分比' as ftype,cast(a.fvalue*1.0/b.fvalue*1.0 as decimal(19,2))as fvalue 
from tb a, tb b 
where a.ftype='奖金' and b.ftype='应发工资' and a.fname=b.fname

[中国风]

select * from c
union all
select
a.fname,a.ftype,cast(b.fvalue*1.0/a.fvalue as numeric(18,2)) as fvalue
from c as a
inner join c as b on a.fname=b.fname where a.ftype='应发工资' and b.ftype='奖金'

[Andy__Huang]

insert into tc(.....)
select a.fname,'百分比',a.奖金/b.工资
from (select fnamme,ftype,奖金 from tb where ftype='奖金')a
inner join (select fnamme,ftype,工资 from tb where ftype='工资') b
on a.fname=b.fname

[hao1hao2hao3]

if object_id('tb')>0
	drop table tb
create table tb
(
	fname varchar(20),
	ftype nvarchar(20),
	fvalue int
)

insert into tb
select '张三', '奖金', 500
union all
select '李四', '奖金', 400
union all
select '张三', '应发工资', 2500
union all
select '李四', '应发工资', 2400

select fname,ftype,cast(fvalue as numeric(10,2)) from tb
union all
select fname,'百分比',
cast(sum(case when ftype ='奖金' then fvalue else 0 end)*1.0/sum(fvalue) as numeric(10,2))
from tb
group by fname

[ws_hgo]

if object_id('tb')>0
    drop table tb
create table tb
(
    fname varchar(20),
    ftype nvarchar(20),
    fvalue int
)

insert into tb
select '张三', '奖金', 500
union all
select '李四', '奖金', 400
union all
select '张三', '应发工资', 2500
union all
select '李四', '应发工资', 2400

select * from tb
union all
select b1.fname,'百分比',cast(b1.fvalue*1.0/b2.fvalue*1.0 as decimal(19,2)) fvalue
from tb b1
join tb b2
on b1.fname=b2.fname
where b1.ftype='奖金' and b2.ftype='应发工资'

fname                ftype                fvalue
-------------------- -------------------- ---------------------------------------
张三                   奖金                   500.00
李四                   奖金                   400.00
张三                   应发工资                 2500.00
李四                   应发工资                 2400.00
张三                   百分比                  0.20
李四                   百分比                  0.17

(6 行受影响)

[zhangle861010]

create table table_c
(
fname varchar(80),
ftype varchar(80),
fvalue float
)
insert into table_c values('张三','奖金','500')
insert into table_c values('李四','奖金','400')
insert into table_c values('张三','应发工资','2500')
insert into table_c values('李四','应发工资','2400')


select fname,ftype,fvalue from table_c
union all
select a.fname,ftype='百分比',fvalue=round(b.fvalue/a.fvalue,3) from
(select fname,ftype,fvalue from table_c where ftype='应发工资') a
inner join
(select fname,ftype,fvalue from table_c where ftype='奖金') b on a.fname=b.fname