SQL如何抓出錯誤資料出來進行修正

Question

OFFICE
("NO","HOURS","PASS","LNAME")DATA
("NO","HOURS","PASS","LNAME")LEN
("NO","HOURS","PASS","LNAME")TOLAE
("NO","H_O","H_D","H_T)上面為四張TABLE
NO為身分證
H_O為OFFICE資料表中每個人時數的加總(PASS需為'Y')
H_D為DATA資料表中每個人時數的加總(PASS需為'Y')
H_T為H_O+H_D+LEN資料表中每個人時數的加總(PASS需為'Y')抓出不正確資料
EX:舉一筆來說
OFFICE
"NO","HOURS","PASS","LNAME"
a123   2        Y     WORD
a123   5        Y     EXCELDATA
"NO","HOURS","PASS","LNAME"
a123   5        Y      ACCESS
a123   6        N      SQLDATA
"NO","HOURS","PASS","LNAME"
a123   4       Y      CPRTOLAE
NO    H_O    H_D   H_T
a123   3      5     9     →我該如何抓出TOLAE中時數不正確的資料
正確
TOLAE
NO    H_O    H_D   H_T
a123   7      5     12因為我怎麼抓，都還是有錯誤的資料..請求大大協助指導，謝謝.

Answer 1

select t2.* from 
(select t0.no ,nvl(t1.office_hours,0) h_O,(t2.data_hours,0)H_D,nvl(t1.office_hours,0)+(t2.data_hours,0)+nvl(t3.len_hours,0)h_t from 
(select no, sum(hours) office_hours 
from office
where pass='Y'  
group by no )t1,
(select no ,sum(hours) data_hours 
from data 
where pass='Y'  
group by no )t2 ,
(select no,SUM(hours) len_hours 
from len
WHERE PASS='Y'
group by no  ) t3, 
(select disinct  no 
from office 
union 
select distinct no 
from data 
union 
select distinct no 
from len )t0
where t0.no=t1.no(+) and t0.no=t2.no(+) and t0.no=t3.no(+)  )t1,
total t2 
where t1.no=t2.no and (t1.h_o<>t2.h_o or t1.h_d<>t2.h_d or t1.h_t<>t2.h_t