查詢命令

from v_TestRec_AS where WoNo=:query_p and Result=0

group by WoNo )b on a.WoNo=b.WoNo

");

oci_bind_by_name($stid, ":query_p", $query_p);

$time_start = microtime(true);

oci_execute($stid);

$time_end = microtime(true);

$time = $time_end - $time_start;

$time=sprintf("%9.2f",$time)."\n";

$File = "elapsed1.txt";

$Handle = fopen($File, 'a+');

fwrite($Handle, $time);

fclose($Handle);

$nrows=oci_fetch_all($stid,$results);

for ($i=0 ; $i < $nrows ; $i++) {

echo $results["RESULT"][$i]."\n";

}

?>

Select convert(real,DefectQty*1.0/InputQty*1.0) from ( Select WoNo,count(distinct PcbaNo) as InputQty

from v_TestRec_AS where WoNo='A09465001' group byWoNo )a join ( Select WoNo,count(distinct PcbaNo) as DefectQty

from v_TestRec_AS where WoNo='A09465001' and Result=0 group by WoNo )b on a.WoNo=b.WoNo

Vertical Select convert(real,DefectQty*1.0/InputQty*1.0) from ( Select WoNo,count(distinct PcbaNo) as InputQty from TestRecord where WoNo='A09465001'

group by WoNo )a join

( Select WoNo,count(distinct PcbaNo) as DefectQty from TestRecord where WoNo='A09465001'

and Result=0 group byWoNo )b on a.WoNo=b.WoNo

Hybrid Select convert(real,DefectQty*1.0/InputQty*1.0) from ( Select WoNo,count(distinct PcbaNo) as InputQty

from TestResult where WoNo='A09465001' group by WoNo )a join ( Select WoNo,count(distinct PcbaNo) as DefectQty

from TestResult where WoNo='A09465001'

and Result=0 group by WoNo )b on a.WoNo=b.WoNo 2 Horizontal Select convert(real,DefectQty*1.0/InputQty*1.0)

From ( Select WoNo,count(distinct PcbaNo) as InputQty from TestResult where WoNo='A09465001'

and TestStationID='A04001195FF76B4' group byWoNo

)a join ( Select WoNo,count(distinct PcbaNo) as DefectQty from TestResult where WoNo='A09465001'

and TestStationID='A04001195FF76B4' and Result=0 group by WoNo )b on a.WoNo=b.WoNo

Vertical Select (DefectQty*1.00)/(InputQty*1.00)

From ( Select WoNo,count(distinct PcbaNo) as InputQty from TestRecord where WoNo='A09465001'

and TestStationID='A04001195FF76B4' group byWoNo )a join

( Select WoNo,count(distinct PcbaNo) as DefectQty from TestRecord where WoNo='A09465001'

and TestStationID='A04001195FF76B4' and Result=0 group by WoNo )b on a.WoNo=b.Wono

Hybrid Select (DefectQty*1.00)/(InputQty*1.00)

From ( Select WoNo,count(distinct PcbaNo) as InputQty from TestResult where WoNo='A09465001'

and TestStationID='A04001195FF76B4' group by WoNo )a join

( Select WoNo,count(distinct PcbaNo) as DefectQty from TestResult where WoNo='A09465001'

and TestStationID='A04001195FF76B4' and Result=0 group by WoNo )b on a.WoNo=b.Wono

3 Horizontal Select count(distinct PcbaNo)

from TestResult where WoNo='A09465001'

and TestStationID='A04001195FF76B4' and Result=1

and CDT between convert(datetime,'2008-07-03 15:00:00') and convert(datetime,'2008-07-03 17:00:00')

Vertical Select count(distinct PcbaNo)

from TestRecord where WoNo='A09465001'

and TestStationID='A04001195FF76B4'and Result=1 and CDT between convert(smalldatetime,'2008-07-03 15:00:00') and convert(smalldatetime,'2008-07-03 17:00:00') Hybrid Select count(distinct PcbaNo)

from TestResult where WoNo='A09465001'

and TestStationID='A04001195FF76B4'and Result=1 and CDT between convert(smalldatetime,'2008-07-03 15:00:00') and convert(smalldatetime,'2008-07-03 17:00:00') 4 Horizontal Select * from TestResult,TestRecord

where TestResult.Uid=Testrecord.Uid and TestResult.PcbaNo='1.2S8250C5D0'

Vertical Select * from TestRecord where PcbaNo='1.2S8250C5D0' Hybrid Select * from TestRecord where PcbaNo='1.2S8250C5D0' 5 Horizontal Select * from TestResult,TestRecord

where TestResult.Uid=TestRecord.Uid

and TestResult.TestStationID='A040019D1DDE4A2' and TestResult.PcbaNo='1.2S8250C5D0'

Vertical Select * from TestRecord where PcbaNo='1.2S8250C5D0' and TestStationID='A040019D1DDE4A2'

Hybrid Select * from TestRecord where PcbaNo='1.2S8250C5D0' and TestStationID='A040019D1DDE4A2'

6 Horizontal Select * from TestResult,TestRecord where TestResult.Uid=TestRecord.Uid

and TestResult.WoNo='A09465001'and TestResult.result=0 Vertical Select * from TestRecord where Result=0 and WoNo='A09465001' Hybrid Select * from TestRecord where Result=0 and WoNo='A09465001' 7 Horizontal Select count(distinct PcbaNo)

from TestResult where WoNo='A09465001'

and TestStationID='A04001195FF76B4' and Result=1 and Tester='22666' and CDT between

convert(datetime,'2008-07-03 15:00:00') and convert(datetime,'2008-07-03 17:00:00')

Vertical Select count(distinct PcbaNo) from TestRecord where WoNo='A09465001' and Tester='22666' and Result=1

and CDT between convert(datetime,'2008-07-03 15:00:00') and convert(datetime,'2008-07-03 17:00:00')

Hybrid Select count(distinct PcbaNo) from TestResult where WoNo='A09465001' and Tester='22666' and Result=1

and CDT between convert(datetime,'2008-07-03 15:00:00') and convert(datetime,'2008-07-03 17:00:00')

8 Horizontal Select top 10 TestError.ErrorCode,count(distinct TestError.PcbaNo) as QEC from TestResult,TestError,WoInfo

where TestResult.PcbaNo=TestError.PcbaNo and TestResult.WoNo=WoInfo.WoNo

and WoInfo.PartNo='0YKM0500FN5'

group by TestError.ErrorCode order by QEC desc Vertical Select top 10 TestRecord.ErrorCode,Count(distinct

TestRecord.PcbaNo) as QEC from TestRecord,WoInfo

where TestRecord.WoNo=WoInfo.WoNo and TestRecord.Result=0 and WoInfo.PartNo='0YKM0500FN5'

group by TestRecord.ErrorCode order by QEC desc;

Hybrid Select top 10 TestRecord.ErrorCode,Count(distinct TestRecord.PcbaNo) as QEC from TestRecord,WoInfo where TestRecord.WoNo=WoInfo.WoNo

and TestRecord.Result=0 and WoInfo.PartNo='0YKM0500FN5' group by TestRecord.ErrorCode order by QEC desc;

9 Horizontal Select count(distinct PcbaNo) from TestResult Where WoNo='A09465001'and Line='A01'and Result=1

and CDT between convert(datetime,'2008-07-03 15:00:00') and convert(datetime,'2008-07-03 17:00:00')

Vertical Select count(distinct PcbaNo)

from TestRecord where WoNo='A09465001' and Line='A01'and Result=1 and CDT between convert(datetime,'2008-07-03 15:00:00')

and convert(datetime,'2008-07-03 17:00:00') Hybrid Select count(distinct TestResult.PcbaNo)

from TestResult,TestStation where

TestResult.TestStationID=TestStation.TestStationID and TestResult.WoNo='A09465001'

and TestStation.Line='A01' and TestResult.Result=1

and TestResult.CDT between convert(datetime,'2008-07-03 15:00:00') and convert(datetime,'2008-07-03 17:00:00')

10 Horizontal Select top 1 a.PartNo,

convert(real,DefectQty*1.0/InputQty*1.0) as DefectRate, InputQty,DefectQty from (

Select WoInfo.PartNo as PartNo,count(distinct PcbaNo) as InputQty from TestResult,WoInfo where TestResult.WoNo=WoInfo.WoNo and TestResult.CDT between convert(datetime,'2008-07-01 00:00:00') and convert(datetime,'2008-07-31 23:59:59')

group byWoInfo.PartNo )a join

( Select WoInfo.PartNo as PartNo,count(distinct PcbaNo) as DefectQty from TestResult,WoInfo

where TestResult.WoNo=WoInfo.WoNo

and TestResult.Result=0 and TestResult.CDT between convert(datetime,'2008-07-01 00:00:00')

and convert(datetime,'2008-07-31 23:59:59') group byWoInfo.PartNo )b on a.PartNo=b.PartNo order by DefectRate desc

Vertical Select TOP 1 a.PartNo,InputQty,DefectQty,

(DefectQty*1.00/InputQty*1.00) as DefectRate from ( Select PartNo,count(distinct PcbaNo) as InputQty

from TestRecord,WoInfo where TestRecord.WoNo=WoInfo.WoNo and TestRecord.CDT between convert(datetime,'2008-07-03 15:00:00') and convert(datetime,'2008-07-03 17:00:00')

group byWoInfo.PartNo )a join

( Select PartNo,count(distinct PcbaNo) as DefectQty from TestRecord,WoInfo where TestRecord.WoNo=WoInfo.WoNo and TestRecord.Result=0 and TestRecord.CDT between convert(datetime,'2008-07-03 15:00:00')

and convert(datetime,'2008-07-03 17:00:00')

group byWoInfo.PartNo )b on a.PartNo=b.PartNo order by DefectRate desc;

Hybrid Select TOP 1

a.PartNo,InputQty,DefectQty,(DefectQty*1.00/InputQty*1.00) as DefectRate from

( Select PartNo,count(distinct PcbaNo) as InputQty from TestResult,WoInfo where TestResult.WoNo=WoInfo.WoNo

and TestResult.CDT between convert(datetime,'2008-07-03 15:00:00') and convert(datetime,'2008-07-03 17:00:00')

group byWoInfo.PartNo )a join

( Select PartNo,count(distinct PcbaNo) as DefectQty from TestResult,WoInfo

where TestResult.WoNo=WoInfo.WoNo and TestResult.Result=0 and TestResult.CDT between convert(datetime,'2008-07-03 15:00:00') and convert(datetime,'2008-07-03 17:00:00')

group byWoInfo.PartNo )b on a.PartNo=b.PartNo order by DefectRate desc;

11 Horizontal Select Line,count(distinct PcbaNo) as Performance from TestResult where CDT between

convert(datetime,'2008-07-03 15:00:00') and convert(datetime,'2008-07-03 17:00:00')

and result=1 group by Line order by Performance desc Vertical Select Line,count(distinct PcbaNo) as Performance

from TestRecord where Result=1

and CDT between convert(datetime,'2008-07-03 15:00:00') and convert(datetime,'2008-07-03 17:00:00')

group by Line order by Performance desc;

Hybrid Select TestStation.Line,count(distinct TestResult.PcbaNo) as Performance from TestResult,TestStation where

TestResult.TestStationID=TestStation.TestStationID and TestResult.Result=1

and TestResult.CDT between convert(datetime,'2008-07-03 15:00:00') and convert(datetime,'2008-07-03 17:00:00')

group by TestStation.Line order by Performance desc;

12 Horizontal Select top 10 a.WoNo,convert(real,DefectQty*1.0/InputQty*1.0) as DefectRate from

( Select WoNo,count(distinct PcbaNo) as InputQty from TestResult where CDT between

convert(datetime,'2008-07-01 00:00:00') and convert(datetime,'2008-07-31 23:59:59')

group byWoNo )a join ( Select WoNo,count(distinct PcbaNo) as DefectQty from TestResult where Result=0

and CDT between convert(datetime,'2008-07-01 00:00:00') and convert(datetime,'2008-07-31 23:59:59')

group byWoNo )b on a.WoNo=b.WoNo order by DefectRate desc Vertical Select top 10

a.WoNo,InputQty,DefectQty,(DefectQty*1.00/InputQty*1.00) as DefectRate from

( Select WoNo,count(distinct PcbaNo) as InputQty

from TestRecord where CDT between convert(datetime,'2008-07-01 00:00:00')

and convert(datetime,'2008-07-31 23:59:59') group byWoNo )a join

( Select WoNo,count(distinct PcbaNo) as DefectQty from TestRecord Where Result=0

and CDT between convert(datetime,'2008-07-01 00:00:00') and convert(datetime,'2008-07-31 23:59:59')

group byWoNo )b on a.WoNo=b.WoNo order by DefectRate desc;

Hybrid Select top 10

a.WoNo,a.InputQty,b.DefectQty,(DefectQty*1.00/InputQty*1.00) as DefectRate

From ( Select WoNo,count(distinct PcbaNo) as InputQty from TestResult where CDT between

convert(datetime,'2008-07-01 00:00:00') and convert(datetime,'2008-07-31 23:59:59')

group byWoNo )a join ( Select WoNo,count(distinct PcbaNo) as DefectQty from TestResult Where Result=0

and CDT between convert(datetime,'2008-07-01 00:00:00') and convert(datetime,'2008-07-31 23:59:59')

group byWoNo )b on a.WoNo=b.WoNo order by DefectRate desc;

13 Horizontal Select TestPrg.TestPrgName,AVG(TestResult.Duration) from TestResult,WoInfo,TestPrg

where TestResult.WoNo=WoInfo.WoNo

and TestResult.TestPrgID=TestPrg.TestPrgID and WoInfo.PartNo='0YKM0500FN5'

and TestResult.CDT between convert(datetime,'2008-07-01 00:00:00')

and convert(datetime,'2008-07-31 23:59:59') group by TestPrg.TestPrgName

Vertical Select b.TestPrgName,avg(a.Duration) from

( Select TestRecord.TestPrgID,TestRecord.Duration from TestRecord,WoInfo

where TestRecord.WoNo=WoInfo.WoNo

and WoInfo.PartNo='0YKM0500FN5' and TestRecord.Result=1

and TestRecord.CDT between convert(datetime,'2008-07-01 15:00:00') and convert(datetime,'2008-07-31 23:59:59')

)a left join ( Select TestPrgID,TestPrgName from TestPrg

group by TestPrgID,TestPrgName )b on a.TestPrgID=b.TestPrgID group by b.TestPrgName

Hybrid Select TestPrg.TestPrgName,avg(TestResult.Duration) from TestResult,WoInfo,TestPrg

where TestResult.WoNo=WoInfo.WoNo

and TestResult.TestPrgID=TestPrg.TestPrgID

and WoInfo.PartNo='0YKM0500FN5' and TestResult.Result=1

and TestResult.CDT between convert(datetime,'2008-07-01 15:00:00') and convert(datetime,'2008-07-31 23:59:59')

group by TestPrg.TestPrgName

14 Horizontal Select PcbaNo,TestPrg.TestPrgName,COUNT(TestRecord.PcbaNo) as TestCount from TestRecord,TestPrg

where TestRecord.TestPrgID=TestPrg.TestPrgID and TestRecord.PcbaNo='1.2S8250C5D0'

group by PcbaNo,TestPrg.TestPrgName

Vertical Select a.PcbaNo,b.TestPrgName,COUNT(a.PcbaNo) as TestCount from ( Select PcbaNo,TestPrgID,CDT from TestRecord

where PcbaNo='1.2S8250C5D0'

group by PcbaNo,TestPrgID,CDT )a left join ( Select distinct TestPrgID,TestPrgName from TestPrg )b on a.TestPrgID=b.TestPrgID group by a.PcbaNo,b.TestPrgName

Hybrid Select TestResult.PcbaNo,TestPrg.TestPrgName,TestResult.ReTest+1 as TestCount from TestResult,TestPrg

where TestResult.TestPrgID=TestPrg.TestPrgID and TestResult.PcbaNo='1.2S8250C5D0'

15 Horizontal Select * from TestRecord,TestPrg

where TestRecord.TestPrgID=TestPrg.TestPrgID

and TestPrg.TestPrgName='Assign' and TestRecord.PcbaNo='1.2S8250C5D0' Vertical Select b.TestPrgName,a.*

From ( Select * from TestRecord

where PcbaNo='1.2S8250C5D0' )a left join ( Select TestPrgID,TestPrgName

from TestPrg group by TestPrgID,TestPrgName

)b on a.TestPrgID=b.TestPrgID where b.TestPrgName='Assign' Hybrid Select TestPrg.TestPrgName,TestRecord.*

from TestRecord,TestPrg

where TestRecord.TestPrgID=TestPrg.TestPrgID and TestRecord.PcbaNo='1.2S8250C5D0'

and TestPrg.TestPrgName='Assign' 16 Horizontal Select * from TestRecord,TestPrg

where TestRecord.TestPrgID=TestPrg.TestPrgID and TestRecord.PcbaNo='1.2S8250C5D0'

and TestPrg.TestPrgName='Download'

Vertical Select b.TestPrgName,a.* from ( Select * from TestRecord where PcbaNo='1.2S8250C5D0')a left join

( Select TestPrgID,TestPrgName from TestPrg

group by TestPrgID,TestPrgName )b on a.TestPrgID=b.TestPrgID where b.TestPrgName='Download'

Hybrid Select TestPrg.TestPrgName,TestRecord.*

from TestRecord,TestPrg

where TestRecord.TestPrgID=TestPrg.TestPrgID and TestRecord.PcbaNo='1.2S8250C5D0'

and TestPrg.TestPrgName='Download' 17 Horizontal Select * from TestRecord,TestPrg

where TestRecord.TestPrgID=TestPrg.TestPrgID

and PcbaNo in ( Select PcbaNo from TestRecord where MAC='001CDFC660BA' )

and TestPrg.TestPrgName='Assign' Vertical Select b.TestPrgName,a.*

From (Select * from TestRecord where PcbaNo in (Select PcbaNo from TestRecord

where TestItemID='MAC' and Value='001CDFC660BA'))a left join ( Select TestPrgID,TestPrgName from TestPrg

group by TestPrgID,TestPrgName )b on a.TestPrgID=b.TestPrgID

where b.TestPrgName='Assign'

Hybrid Select TestPrg.TestPrgName,TestRecord.*

from TestRecord,PcbaMapping,TestPrg

where TestRecord.PcbaNo=PcbaMapping.PcbaNo and TestRecord.TestPrgID=TestPrg.TestPrgID and PcbaMapping.MAC='001CDFC660BA'

and TestPrg.TestPrgName='Assign' 18 Horizontal Select * from TestRecord,TestPrg

where TestRecord.TestPrgID=TestPrg.TestPrgID and PcbaNo in ( Select PcbaNo from TestRecord

where MAC='001CDFC660BA') and TestPrg.TestPrgName='Download' Vertical Select b.TestPrgName,a.*

From (Select * from TestRecord where PcbaNo in (Select PcbaNo from TestRecord

where TestItemID='MAC' and Value='001CDFC660BA') )a left join (Select TestPrgID,TestPrgName from TestPrg

group by TestPrgID,TestPrgName )b on a.TestPrgID=b.TestPrgID where b.TestPrgName='Download'

Hybrid Select TestPrg.TestPrgName,TestRecord.*

from TestRecord,PcbaMapping,TestPrg

where TestRecord.PcbaNo=PcbaMapping.PcbaNo and TestRecord.TestPrgID=TestPrg.TestPrgID and PcbaMapping.MAC='001CDFC660BA'

and TestPrg.TestPrgName='Download'

19 Horizontal Select TestPrg.TestPrgName,avg(TestResult.Duration) from TestResult,TestPrg

where TestResult.TestPrgID=TestPrg.TestPrgID and TestResult.WoNo='A09465001'

and TestPrg.TestPrgName='Final Test' and TestResult.Result=1

group by TestPrg.TestPrgName

Vertical Select b.TestPrgName,avg(a.Duration)

From (Select TestPrgID,Duration from TestRecord whereWoNo='A09465001' and Result=1)a left join (Select TestPrgID,TestPrgName from TestPrg

group by TestPrgID,TestPrgName )b on a.TestPrgID=b.TestPrgID where b.TestPrgName='Final Test'

group by b.TestPrgName

Hybrid Select TestPrg.TestPrgName,avg(TestResult.Duration) from TestResult,TestPrg

where TestResult.TestPrgID=TestPrg.TestPrgID and TestResult.WoNo='A09465001'

and TestResult.Result=1

and TestPrg.TestPrgName='Final Test' group by TestPrg.TestPrgName

20 Horizontal Select TestError.ErrorCode,count(distinct TestError.PcbaNo) as QEC from TestResult,TestError,TestPrg

where TestResult.Pcbano=TestError.PcbaNo and TestResult.TestPrgID=TestError.TestPrgID and TestError.TestPrgID=TestPrg.TestPrgID and TestResult.WoNo='A09465001'

and TestPrg.TestPrgName='Assign' group by TestError.ErrorCode;

Vertical Select a.Errorcode,count(distinct a.PcbaNo)as QEC From (Select TestPrgID,ErrorCode,PcbaNo from TestRecord whereWoNo='A09465001' and Result=0 )a left join

(Select TestPrgID,TestPrgName from TestPrg

group by TestPrgID,TestPrgName )b on a.TestPrgID=b.TestPrgID where b.TestPrgName='Assign' group by a.ErrorCode

Hybrid Select TestRecord.Errorcode,count(distinct TestRecord.PcbaNo)as QEC from TestRecord,TestPrg where

TestRecord.TestPrgID=TestPrg.TestPrgID

and TestRecord.WoNo='A09465001' and TestPrg.TestPrgName='Assign' group by TestRecord.ErrorCode

21 Horizontal Select TestError.ErrorCode,count(TestError.ErrorCode)as TEC from TestResult,TestError,TestPrg

where TestResult.PcbaNo=TestError.PcbaNo and TestResult.TestPrgID=TestError.TestPrgID and TestError.TestPrgID=TestPrg.TestPrgID

and TestResult.CDT between convert(datetime,'2008-07-01 00:00:00') and convert(datetime,'2008-07-31 23:59:59')

and TestPrg.TestPrgName='Final Test'

group by TestError.ErrorCode order by TEC desc;

Vertical Select a.Errorcode,count(a.ErrorCode)as TEC from

( Select TestPrgID,ErrorCode from TestRecord where Result=0 and CDT between convert(datetime,'2008-07-01 00:00:00') and convert(datetime,'2008-07-31 23:59:59'))a left join (Select TestPrgID,TestPrgName from TestPrg

group by TestPrgID,TestPrgName )b on a.TestPrgID=b.TestPrgID where b.TestPrgName='Final Test' group by a.ErrorCode

order by TEC desc

Hybrid Select TestRecord.Errorcode,count(TestRecord.ErrorCode)as TEC from TestRecord,TestPrg

where TestRecord.TestPrgID=TestPrg.TestPrgID and Result=0 and TestRecord.CDT between

convert(datetime,'2008-07-01 00:00:00') and convert(datetime,'2008-07-31 23:59:59')

and TestPrg.TestPrgName='Final Test' group by TestRecord.ErrorCode order by TEC desc

22 Horizontal Select distinct

TestResult.PcbaNo,TestPrg.TestPrgName,TestResult.Result from TestResult,WoInfo,Routing,TestPrg

where TestResult.WoNo=WoInfo.WoNo and WoInfo.PartNo=Routing.PartNo

and Routing.TestPrgID=TestPrg.TestPrgID

and TestResult.PcbaNo='1.2S8250C5D0'and Routing.Sequence=3 Vertical Select a.PcbaNo,b.TestPrgName,a.Result

from

( Select TestRecord.PcbaNo,TestRecord.TestPrgID,TestRecord.Result from TestRecord,WoInfo,Routing,TestDetail

where TestRecord.WoNo=WoInfo.WoNo and WoInfo.PartNo=Routing.PartNo

and Routing.TestRoutingID=TestDetail.TestRoutingID and TestRecord.TestPrgID=TestDetail.TestPrgID and TestRecord.PcbaNo='1.2S8250C5D0'

and TestDetail.Sequence=3)a left join (Select TestPrgID,TestPrgName from TestPrg

group by TestPrgID,TestPrgName)b on a.TestPrgID=b.TestPrgID Hybrid Select TestResult.PcbaNo,TestPrg.TestPrgName,TestResult.Result

from TestResult,WoInfo,Routing,TestPrg where TestResult.WoNo=WoInfo.WoNo and WoInfo.PartNo=Routing.PartNo

and TestResult.TestPrgID=TestPrg.TestPrgID and TestResult.PcbaNo='1.2S8250C5D0' and Routing.Sequence=3

23 Horizontal Select distinct TestRecord.MAC from TestRecord,TestResult,TestPrg where TestRecord.Uid=TestResult.Uid

and TestRecord.TestPrgID=TestPrg.TestPrgID and TestResult.WoNo='A09465001'

and TestPrg.TestPrgName='Assign' and TestRecord.PcbaNo not in (Select TestRecord.PcbaNo

from TestRecord,TestResult,TestPrg where TestRecord.Uid=TestResult.Uid

and TestRecord.TestPrgID=TestPrg.TestPrgID and TestResult.WoNo='A09465001'

and TestPrg.TestPrgName='Download')

Vertical Create view v_TestRecord_join_TestPrg as Select

a.PcbaNo,b.TestPrgName,a.TestItemID,a.Value,a.Duration,a.CDT from

(Select PcbaNo,TestPrgID,TestItemID,[Value],Result,Duration,CDT from TestRecord)a left join

(Select TestPrgID,TestPrgName from TestPrg

group by TestPrgID,TestPrgName )b on a.TestPrgID=b.TestPrgID Select [Value] from v_TestRecord_join_TestPrg

where TestItemID='MAC' and PcbaNo in

(Select PcbaNo from v_TestRecord_join_TestPrg where TestPrgName='Assign' and PcbaNo not in

(Select PcbaNo from v_TestRecord_join_TestPrg where TestPrgName='Download'))

Hybrid Select PcbaMapping.MAC

from TestResult,PcbaMapping,TestPrg

where TestResult.PcbaNo=PcbaMapping.PcbaNo and TestResult.TestPrgID=TestPrg.TestPrgID and TestResult.WoNo='A09465001'

and TestPrg.TestPrgName='Assign' and TestResult.PcbaNo not in (Select TestResult.PcbaNo from TestResult,TestPrg

在文檔中 中 華 大 學 (頁 74-87)