当前位置:主页>仓库管理软件> 列表

有難度的SQL語句的問題 找免费家庭记账软件

记账软件版1楼: 我現在的問題是﹐一個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[u])*1.5[/u]
+(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)[u]*2[/u])

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語句太長了﹐請問高手有沒有更好的方法﹗

2楼: 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 發板數量
---------
發板數量这样写一下,其他数量依次类推. 如免费家庭记账软件

3楼: to:babibean


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

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

5楼: 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'');

6楼: 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
--------
你看看上面这句,在我这里没有语法错误.