没分了，帮我看看求两个数之除
有这样一张表，我现在要求
ItemCode=7的除以ItemCode=8
既ItemCode 7/8
Code ItemCode Value1
001 7 3
001 8 5
002 7 3
002 8 21
003 7 1
003 8 2
结果如下
001 0.6
002 7
002 0.5
[解决办法]
select t1.code , cast(t1.Value1 as decimal(18,2))/t2.Value1 from
(
select * from tb where ItemCode = 7
) t1,
(
select * from tb where ItemCode = 8
) t2
where t1.code = t2.code
[解决办法]
create table tb(Code varchar(10) , ItemCode int, Value1 int)
insert into tb values( '001 ', 7, 3)
insert into tb values( '001 ', 8, 5)
insert into tb values( '002 ', 7, 3)
insert into tb values( '002 ', 8, 21)
insert into tb values( '003 ', 7, 1)
insert into tb values( '003 ', 8, 2)
select t1.code , cast(cast(t1.Value1 as decimal(18,2))/t2.Value1 as decimal(18,2)) value from
(
select * from tb where ItemCode = 7
) t1,
(
select * from tb where ItemCode = 8
) t2
where t1.code = t2.code
drop table tb
/*
code value
---------- --------------------
001 .60
002 .14
003 .50
（所影响的行数为 3 行）
*/
[解决办法]
where t1.code = t2.code and t2.value1 <> 0
[解决办法]
Code ItemCode Value1
001 7 3
001 8 5
002 7 3
002 8 21
003 7 1
003 8 2
----------
select a.code,value=a.value1/b.value2 from
(select * from t where itemcode=7)a
left join
(select * from t where item_code=8)b
on a.code=b.code
[解决办法]
create table #表(Code varchar(3),ItemCode int,Value1 int)
insert #表 select
'001 ', 7, 3 insert #表 select
'001 ', 8, 5 insert #表 select
'002 ', 7, 3 insert #表 select
'002 ', 8, 21 insert #表 select
'003 ', 7, 1 insert #表 select
'003 ', 8, 2 insert #表 select
'004 ', 7, 1 insert #表 select
'005 ', 8, 3
--------------------------
select code,case when sum(case when itemcode=8 then value1 else 0 end)=0 then '被0除 '
else rtrim(cast(sum(case when itemcode=7 then value1 else 0 end)*1.0/
sum(case when itemcode=8 then value1 else 0 end) as decimal(8,2)) ) end from #表 group by code
