# 没分了，帮小弟我看看求两个数之除

www.myexceptions.net  网友分享于：2013-01-21  浏览：2次

ItemCode=7的除以ItemCode=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