select nian,yue,zhonglei, sum(case shuxing when 't' then zhi else 0.0 end)/sum(case shuxing when 'f' then zhi else 0.0 end) as bizhi from 表 group by nian,yue,zhonglei
select m.nian , m.yue , m.zhonglei , m.zhi*1.0/n.zhi from tb m , tb n where m.shuxing = 't' and n.shuxing = 'f' and m.nian = n.nian and m.yue = n.yue and m.zhonglei = n.zhonglei
declare @t table(nian varchar(4),yue varchar(2),zhonglei varchar(10),shuxing varchar(10),zhi int) insert into @t select 2009,01,'a','t',80 insert into @t select 2009,01,'a','f',100 insert into @t select 2009,01,'b','t',50 insert into @t select 2009,01,'b','f',100 insert into @t select 2009,02,'a','t',40 insert into @t select 2009,02,'a','f',100 insert into @t select 2009,02,'b','t',30 insert into @t select 2009,02,'b','f',100 select nian,yue,zhonglei, cast(sum(case shuxing when 't' then zhi else 0.0 end)/sum(case shuxing when 'f' then zhi else 0.0 end) as numeric(2,1)) as bizhi from @t group by nian,yue,zhonglei/* nian yue zhonglei bizhi ---- ---- ---------- ----- 2009 1 a .8 2009 1 b .5 2009 2 a .4 2009 2 b .3 */
create table tb(nian int, yue varchar(10), zhonglei varchar(10), shuxing varchar(10), zhi int) insert into tb values(2009 , '01' , 'a' , 't' , 80 ) insert into tb values(2009 , '01' , 'a' , 'f' , 100) insert into tb values(2009 , '01' , 'b' , 't' , 50 ) insert into tb values(2009 , '01' , 'b' , 'f' , 100) insert into tb values(2009 , '02' , 'a' , 't' , 40) insert into tb values(2009 , '02' , 'a' , 'f' , 100 ) insert into tb values(2009 , '02' , 'b' , 't' , 30 ) insert into tb values(2009 , '02' , 'b' , 'f' , 100 ) goselect m.nian , m.yue , m.zhonglei , cast(m.zhi*1.0/n.zhi as decimal(18,2)) bizhi from tb m , tb n where m.shuxing = 't' and n.shuxing = 'f' and m.nian = n.nian and m.yue = n.yue and m.zhonglei = n.zhongleidrop table tb/* nian yue zhonglei bizhi ----------- ---------- ---------- -------------------- 2009 01 a .80 2009 01 b .50 2009 02 a .40 2009 02 b .30(所影响的行数为 4 行) */
如果每组只有两行,用乌龟的方法比较简单,如果是多行,可以这样---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([nian] int,[yue] varchar(2),[zhonglei] varchar(1),[shuxing] varchar(1),[zhi] int) insert [tb] select 2009,'01','a','t',80 union all select 2009,'01','a','f',100 union all select 2009,'01','b','t',50 union all select 2009,'01','b','f',100 union all select 2009,'02','a','t',40 union all select 2009,'02','a','f',100 union all select 2009,'02','b','t',30 union all select 2009,'02','b','f',100
---查询--- select nian, yue, zhonglei, cast(sum( case [shuxing] when 't' then [zhi] else 0 end)*1.0/case sum(case [shuxing] when 'f' then [zhi] else 0 end) when 0 then 1 else sum(case [shuxing] when 'f' then [zhi] else 0 end) end as dec(18,2)) as [t/f] from tb group by nian,yue,zhonglei ---结果--- nian yue zhonglei t/f ----------- ---- -------- -------------------- 2009 01 a .80 2009 01 b .50 2009 02 a .40 2009 02 b .30(所影响的行数为 4 行)
from tb m , tb n
where m.shuxing = 't' and n.shuxing = 'f'
and m.nian = n.nian and m.yue = n.yue and m.zhonglei = n.zhonglei
declare @t table(nian varchar(4),yue varchar(2),zhonglei varchar(10),shuxing varchar(10),zhi int)
insert into @t select 2009,01,'a','t',80
insert into @t select 2009,01,'a','f',100
insert into @t select 2009,01,'b','t',50
insert into @t select 2009,01,'b','f',100
insert into @t select 2009,02,'a','t',40
insert into @t select 2009,02,'a','f',100
insert into @t select 2009,02,'b','t',30
insert into @t select 2009,02,'b','f',100 select
nian,yue,zhonglei,
cast(sum(case shuxing when 't' then zhi else 0.0 end)/sum(case shuxing when 'f' then zhi else 0.0 end) as numeric(2,1)) as bizhi
from
@t
group by
nian,yue,zhonglei/*
nian yue zhonglei bizhi
---- ---- ---------- -----
2009 1 a .8
2009 1 b .5
2009 2 a .4
2009 2 b .3
*/
insert into tb values(2009 , '01' , 'a' , 't' , 80 )
insert into tb values(2009 , '01' , 'a' , 'f' , 100)
insert into tb values(2009 , '01' , 'b' , 't' , 50 )
insert into tb values(2009 , '01' , 'b' , 'f' , 100)
insert into tb values(2009 , '02' , 'a' , 't' , 40)
insert into tb values(2009 , '02' , 'a' , 'f' , 100 )
insert into tb values(2009 , '02' , 'b' , 't' , 30 )
insert into tb values(2009 , '02' , 'b' , 'f' , 100 )
goselect m.nian , m.yue , m.zhonglei , cast(m.zhi*1.0/n.zhi as decimal(18,2)) bizhi
from tb m , tb n
where m.shuxing = 't' and n.shuxing = 'f'
and m.nian = n.nian and m.yue = n.yue and m.zhonglei = n.zhongleidrop table tb/*
nian yue zhonglei bizhi
----------- ---------- ---------- --------------------
2009 01 a .80
2009 01 b .50
2009 02 a .40
2009 02 b .30(所影响的行数为 4 行)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([nian] int,[yue] varchar(2),[zhonglei] varchar(1),[shuxing] varchar(1),[zhi] int)
insert [tb]
select 2009,'01','a','t',80 union all
select 2009,'01','a','f',100 union all
select 2009,'01','b','t',50 union all
select 2009,'01','b','f',100 union all
select 2009,'02','a','t',40 union all
select 2009,'02','a','f',100 union all
select 2009,'02','b','t',30 union all
select 2009,'02','b','f',100
---查询---
select
nian,
yue,
zhonglei,
cast(sum(
case [shuxing]
when 't'
then [zhi]
else 0
end)*1.0/case sum(case [shuxing] when 'f' then [zhi] else 0 end)
when 0
then 1
else sum(case [shuxing] when 'f' then [zhi] else 0 end)
end
as dec(18,2)) as [t/f]
from tb
group by nian,yue,zhonglei
---结果---
nian yue zhonglei t/f
----------- ---- -------- --------------------
2009 01 a .80
2009 01 b .50
2009 02 a .40
2009 02 b .30(所影响的行数为 4 行)