有難度的SQL語句的問題(100分)

我現在的問題是﹐一個SQL語句﹐它包括三個匯總﹕得出格式如下﹕
維修人 發板數量 退板數量 返修次數 返修率
DDDDD 3.00 1.00 0.00 0%
D12048 1.00 1.00 0.00 0%
P0057598 1.00 0.00 0.00 0%
P66824 1.00 0.00 0.00 0%
10524 0.00 1.00 0.00 0%
SQL語句如下﹕
select distinct(rep_name) as 維修人,
((select count(serial_number) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG='0' and PRODUCT_TYPE='0' AND SEND_TIME >sysdate-20)
+(select count(serial_number) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG='0'and PRODUCT_TYPE='1' AND SEND_TIME >sysdate-20)*1.5
+(select count(serial_number) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG='0'and PRODUCT_TYPE='2' AND SEND_TIME >sysdate-20)*2)

as 發板數量,
(select count(serial_number) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG='1' AND SEND_TIME >sysdate-20) as 退板數量 ,
(select sum(PRIVILEGE-1) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG='0' AND SEND_TIME >sysdate-20) as 返修次數,
(select sum(PRIVILEGE-1) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG='0' AND SEND_TIME >sysdate-20)*100/
(select count(serial_number) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG='0' AND SEND_TIME >sysdate-20)||'%' as 返修率
from sfism4.R_REPAIR_INFO_T a where a.SEND_TIME >sysdate-20 order by 發板數量 desc
現在的問題是﹕發的板子分了三種﹐一種是查出來總數要乘1.5﹐一種是查出來總數要乘2﹐還有一種就是不乘﹐退板的數量也要按這三種退﹐發板的數量我就按它的三種每個都乘它的系數﹐這樣我學得太麻煩﹐如果發的板子有五種六種怎么辦﹐都讓它一個一個加起來太麻煩﹐而且SQL語句太長了﹐請問高手有沒有更好的方法﹗

select distinct(rep_name) as 維修人,
((select count(
case PRODUCT_TYPE when 0 then serial_number
when 1 then serial_number * 1.5
else serial_number * 2 end )
from sfism4.R_REPAIR_INFO_T
where REP_name=a.REP_name and RETURN_FLAG='0' and SEND_TIME >sysdate-20))
as 發板數量
---------
發板數量这样写一下,其他数量依次类推.

to:babibean
兄弟﹗你的思路很正確﹐但我在ORACLE動行時產生錯誤﹕missing rigth parenthesis
缺少右括號,是語法錯誤嗎

to:babibean
兄弟﹗能不能再幫幫小弟﹐你的那句動行時出錯呀﹐是什么原因呀﹗100很快就給你

sql.Add('select distinct(a.rep_name) as 維修人,(select NvL(sum(decode(product_type,1,1.5,1,2,1)),0) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG=''0'' AND SEND_TIME between to_date('''+start_time+''',''yyyy/mm/ddhh24:mi'') and to_date('''+end_time+''',''yyyy/mm/ddhh24:mi'')) as 發板數量,');
sql.Add('(select nvl(sum(decode(product_type,1,2,2,3,1)),0) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG<>''0'' AND SEND_TIME between to_date('''+start_time+''',''yyyy/mm/ddhh24:mi'') and to_date('''+end_time+''',''yyyy/mm/ddhh24:mi'')) as 退板數量, ');
sql.Add('(select sum(PRIVILEGE-1) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG=''0'' AND SEND_TIME between to_date('''+start_time+''',''yyyy/mm/ddhh24:mi'') and to_date('''+end_time+''',''yyyy/mm/ddhh24:mi'')) as 返修次數,');
sql.Add('(select sum(PRIVILEGE-1) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG=''0'' AND SEND_TIME between to_date('''+start_time+''',''yyyy/mm/ddhh24:mi'') and to_date('''+end_time+''',''yyyy/mm/ddhh24:mi''))*100/');
sql.Add('(select NvL(sum(decode(product_type,1,1.5,1,2,1)),0) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG=''0'' AND SEND_TIME between to_date('''+start_time+''',''yyyy/mm/ddhh24:mi'') and to_date('''+end_time+''',''yyyy/mm/ddhh24:mi''))||''%'' as 返修率,');
sql.Add('(select nvl(sum(decode(product_type,1,1.5,2,2,1)),0) from sfism4.R_REPAIR_INFO_T where REP_name=a.REP_name and RETURN_FLAG=''2'' AND SEND_TIME between to_date('''+start_time+''',''yyyy/mm/ddhh24:mi'') and to_date('''+end_time+''',''yyyy/mm/ddhh24:mi'')) as BGA誤判數量');
sql.Add(' from sfism4.R_REPAIR_INFO_T a where a.SEND_TIME between to_date('''+start_time+''',''yyyy/mm/ddhh24:mi'') and to_date('''+end_time+''',''yyyy/mm/ddhh24:mi'') order by 發板數量 desc');

to fusm_2000,我没用过ORACLE啊，大概就是那个思路了。你再看看，或者请同事看一下。

select distinct(rep_name) as 維修人,
((select count(
case PRODUCT_TYPE when 0 then serial_number
when 1 then serial_number * 1.5
else serial_number * 2 end )
from sfism4.R_REPAIR_INFO_T
where REP_name=a.REP_name and RETURN_FLAG='0' and SEND_TIME >sysdate-20))
as 發板數量
from sfism4.R_REPAIR_INFO_T a where a.SEND_TIME >sysdate-20 order by 發板數量 desc
--------
你看看上面这句，在我这里没有语法错误.