exists做为where条件时,是先对where前的主查询询进行查询,然后用主查询的结果一个一个的代入exists的查询进行判断,如果为真则输出当前这一条主查询的结果,否则不输出。理解了sql
select语句的执行流程,其实这个EXISTS语句和他的执行顺序就很好理解了。下面这个例子中,到该执行where 条件的exists时,就会拿A中的数据当做exists中的查询的where条件进行筛选了。发一篇文章以备几天之后再复习一下。
SELECT A.TAX_RATE,
A.PRICE_RATE AS TAX_PRICE,
C.CLAIM_USER_NO,
E.USER_DESC AS CLAIM_USER_DESC,
C.SUPPLIER_NO,
D.SUPPLIER_DESC,
C.CLAIM_DATE,
A.CLAIM_REQUEST_NO,
A.CLAIM_REQUEST_SERIAL_NUMBER,
A.WORSE_PART_NO,
B.PART_DESC AS WORSE_PART_DESC,
A.QUANTITY,
A.PRICE,
A.WORK_HOURS_FEES,
A.MILEAGES_FEES,
A.OTHER_FEES,
A.CLAIM_RATE,
CAST(A.AMOUNT AS DECIMAL(18,2)) AS TOTAL_FEE,
A.SERVICE_REPORT_NO,
A.SERVICE_REPORT_SERIAL_NUMBER,
H.MODEL_DESC,
H.SERIAL_NO,
A.CLAIM_TYPE,
A.REMARK,
H.SRVREQUEST_NO
FROM JS_SUPPLIERS_CLAIM_DETAIL AS A
LEFT JOIN JS_COMMON_PART AS B
ON A.WORSE_PART_NO = B.PART_NO
LEFT JOIN JS_SUPPLIERS_CLAIM_LIST AS C
ON A.CLAIM_REQUEST_NO = C.CLAIM_REQUEST_NO
LEFT JOIN JS_COMMON_SUPPLIER AS D
ON C.SUPPLIER_NO = D.SUPPLIER_NO
LEFT JOIN JS_COMMON_USER AS E
ON C.CLAIM_USER_NO = E.USER_NO
LEFT JOIN
(SELECT F.REPORT_NO,
F.SERIAL_NO,
G.MODEL_DESC,
F.SRVREQUEST_NO
FROM JS_SERVICE_REPORT AS F
LEFT JOIN JS_COMMON_MODEL AS G
ON F.MODEL_NO = G.MODEL_NO) AS H
ON A.SERVICE_REPORT_NO = H.REPORT_NO
WHERE NOT EXISTS
(SELECT CBD.WORSE_PART_NO
FROM JS_SUPPLIERS_CLAIM_BILLING_DETAIL AS CBD
WHERE CBD.CLAIM_REQUEST_NO = A.CLAIM_REQUEST_NO AND CBD.CLAIM_REQUEST_SERIAL_NUMBER = A.CLAIM_REQUEST_SERIAL_NUMBER)
AND C.STATUS='40' AND A.CLAIM_TYPE!='103';