TABLE A 的SN  TABLE  B 的SN

A.SN 不良品 會被塞入 B.SN

假設 COUNT(A.SN) 有5筆資料
COUNT(B.SN) 有3筆資料
其中的兩筆COUNT出來,B沒有資料也就是不良

希望秀出成為

COUNT(A.SN) , COUNT(B.SN)
150 3
50 2
10 1
40 0
100 0

希望可以把0的也列出來

可是

=============================

那我下的SQL

COUNT(A.SN),COUNT(B.SN)

A.SN = B.SN( )

搜尋出來會變成



COUNT(A.SN) , COUNT(B.SN)
150 3
50 2
10 1

===============================

COUNT(B.SN) = 0 的就沒顯示出來
40 0
100 0


應該是A.SN = B.SN( ) 所造成的 (因為其中一各TABLE找不到資料)

請問...那我該如何下SQL呢?
才能達成我要的目的

------
Program : Delphi 7
DataBase : Oracle 9i
Client : ClientDataSet

編輯記錄

我的SQL


[code sql]
SELECT A.WORK_ORDER,B.PART_NO,COUNT(DISTINCT A.SERIAL_NUMBER) "COUNT",
COUNT(DISTINCT C.SERIAL_NUMBER) "ERROR COUNT"

FROM G_SN_TRAVEL A,
SYS_PART B,
G_SN_TRAVEL C



WHERE A.PROCESS_ID IN ('100020','100021','100029','100031','100045')
AND TO_CHAR(A.OUT_PROCESS_TIME,'YYYYMMDD') = '20080220'
AND A.STAGE_ID <> '10015'


AND A.MODEL_ID = B.PART_ID

AND C.WORK_ORDER = A.WORK_ORDER( ) //// <- 兩各TABLE的連結點
AND C.PROCESS_ID IN ('100020','100021','100029','100031','100045')
AND TO_CHAR(A.OUT_PROCESS_TIME,'YYYYMMDD') = '20080220'
AND A.STAGE_ID <> '10015'
AND C.CURRENT_STATUS = '1'




GROUP BY A.WORK_ORDER,B.PART_NO

[/code]

------
Program : Delphi 7
DataBase : Oracle 9i
Client : ClientDataSet

編輯記錄

===================引 用 P.D. 文 章===================
可否列出你原sql的語法


Dear 版大

你要的不就是這段嗎 ???


[code sql]
SELECT A.WORK_ORDER,B.PART_NO,
COUNT(DISTINCT A.SERIAL_NUMBER) "COUNT",
COUNT(DISTINCT C.SERIAL_NUMBER) "ERROR COUNT"

FROM G_SN_TRAVEL A,
SYS_PART B,
G_SN_TRAVEL C

WHERE A.PROCESS_ID IN ('100020','100021','100029','100031','100045')
AND TO_CHAR(A.OUT_PROCESS_TIME,'YYYYMMDD') = '20080220'
AND A.STAGE_ID <> '10015'


AND A.MODEL_ID = B.PART_ID
AND C.WORK_ORDER = A.WORK_ORDER( ) -- <- 兩各TABLE的連結點


AND C.PROCESS_ID IN ('100020','100021','100029','100031','100045')
AND TO_CHAR(A.OUT_PROCESS_TIME,'YYYYMMDD') = '20080220'
AND A.STAGE_ID <> '10015'
AND C.CURRENT_STATUS = '1'
[/code]

------
Program : Delphi 7
DataBase : Oracle 9i
Client : ClientDataSet

編輯記錄

oracle 的話應該會出現
COUNT(A.SN) , COUNT(B.SN)
150 3
50 2
10 1
40
100

改成
select COUNT(A.SN) , nvl(COUNT(B.SN),0)
from a, b
where a.sn=b.sn( )
應該可以.
請試試看

------
IS IT WHAT IT IS
我是 李慕白 請倒著唸.
又想把老話拿出來說, 請用台語發音 : 專家專家全是ROBOT CAR (滷肉腳啦);
都已接手這麼久了, 績效還是那麼爛, 講話還那麼大聲.

仔細看了一下您的 sql,
c 和 a 都選自 g_sn_travel, 而且看起來 c 的條件比 a 多, 推論 c 的筆數應該比 a 少,
join 的部份應該是筆數多的在左邊, 也就是 ( ) 要放在筆數少的 table

1. SELECT A.WORK_ORDER,B.PART_NO,
2. COUNT(DISTINCT A.SERIAL_NUMBER) "COUNT",
3. COUNT(DISTINCT C.SERIAL_NUMBER) "ERROR COUNT"
5. FROM G_SN_TRAVEL A,
6. SYS_PART B,
7. G_SN_TRAVEL C
9. WHERE A.PROCESS_ID IN ('100020','100021','100029','100031','100045')
10. AND TO_CHAR(A.OUT_PROCESS_TIME,'YYYYMMDD') = '20080220'
11. AND A.STAGE_ID <> '10015'
13. AND A.MODEL_ID = B.PART_ID
14. AND C.WORK_ORDER = A.WORK_ORDER( ) -- <- 兩各TABLE的連結點 --是否應改成 a.work_order=c.work_order( )
16. AND C.PROCESS_ID IN ('100020','100021','100029','100031','100045')
17. AND TO_CHAR(A.OUT_PROCESS_TIME,'YYYYMMDD') = '20080220' -- 是否應該是 c.out_process_time
18. AND A.STAGE_ID <> '10015' -- 是否應該是 c.stage_id
19. AND C.CURRENT_STATUS = '1'

請參考, 謝謝

------
IS IT WHAT IT IS
我是 李慕白 請倒著唸.
又想把老話拿出來說, 請用台語發音 : 專家專家全是ROBOT CAR (滷肉腳啦);
都已接手這麼久了, 績效還是那麼爛, 講話還那麼大聲.

有關
join 的部份應該是筆數多的在左邊, 也就是 ( ) 要放在筆數少的 table
這句話我說得太快了,

我習慣
筆數多的在左邊, ( ) 放右邊
但我看過有人
( )放左邊, 筆數多的在右邊
不過, ( ) 要放在筆數少的 table 應該是不錯的.

------
IS IT WHAT IT IS
我是 李慕白 請倒著唸.
又想把老話拿出來說, 請用台語發音 : 專家專家全是ROBOT CAR (滷肉腳啦);
都已接手這麼久了, 績效還是那麼爛, 講話還那麼大聲.