求出部門百分比,如何寫 |
尚未結案
|
james_yu
一般會員 發表:33 回覆:19 積分:10 註冊:2003-07-08 發送簡訊給我 |
|
Fishman
尊榮會員 發表:120 回覆:1949 積分:2163 註冊:2006-10-28 發送簡訊給我 |
Hi james_yu, Sorry !! 不知你是用何資料庫,下列語法你試試,不知是否可行?
SELECT 單位, (SUM(FINISHED) / SUM(PLANED)) * 100 AS 完成百分比 FROM (SELECT 單位, COUNT(*) AS PLANED, 0 AS FINISHED FROM YOURTABLE GROUP BY 單位 UNION ALL SELECT 單位, 0 AS PLANED, COUNT(*) AS FINISHED FROM YOURTABLE WHERE 狀態 = '完成' GROUP BY 單位) GROUP BY 單位發表人 -
------
Fishman |
james_yu
一般會員 發表:33 回覆:19 積分:10 註冊:2003-07-08 發送簡訊給我 |
|
Fishman
尊榮會員 發表:120 回覆:1949 積分:2163 註冊:2006-10-28 發送簡訊給我 |
Hi james_yu, 沒有錯啊!
資料如下:
執行結果如下:
SQL Command: SELECT DEPARTMENT, (SUM(FINISHED) / SUM(PLANED)) * 100 AS FINISH_PERCENT FROM (SELECT DEPARTMENT, COUNT(*) AS PLANED, 0 AS FINISHED FROM TABLE1 GROUP BY DEPARTMENT UNION ALL SELECT DEPARTMENT, 0 AS PLANED, COUNT(*) AS FINISHED FROM TABLE1 WHERE STATUS = 'FINISHED' GROUP BY DEPARTMENT) GROUP BY DEPARTMENT發表人 -
------
Fishman |
james_yu
一般會員 發表:33 回覆:19 積分:10 註冊:2003-07-08 發送簡訊給我 |
select units,(sum(finished)/sum(planed))*100 as 完成百分比
from
((select units,count(*) as planed,0 as finished
from train_schedule
group by units)
union all
(select units,0 as planed,count(*) as finished
from train_schedule
where state='完成'
group by units ))
group by units
/ 已經OK,感謝FISHMAN,簡直太愛你了,即使我也是個男人
|
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |