还是给出sql和plan
/* Formatted on 2005/12/01 10:57 (Formatter Plus v4.8.6) */
SELECT *
FROM (SELECT g.*,
ROW_NUMBER () OVER (PARTITION BY g.asc_code, g.series ORDER BY SYS_GUID
()) rd
FROM (SELECT t.*,
ROW_NUMBER () OVER (PARTITION BY asc_code, vin ORDER BY asc_code)
rn_vin,
ROW_NUMBER () OVER (PARTITION BY asc_code, NVL2
(deliverer_phone,
deliverer_ddd_code
|| deliverer_phone,
deliverer_mobile
) ORDER BY asc_code)
rn_phone
FROM (SELECT a.asc_code, c.asc_name, a.deliverer,
a.deliverer_ddd_code, a.deliverer_phone,
a.deliverer_mobile, a.owner_name, a.model,
a.license, a.vin, c.company_address, a.series
FROM tt_sgm_asc_repairorder a,
tm_sgm_asc_ascbasicinfo c,
tt_sgm_sys_deparasc da
WHERE a.asc_code = c.asc_code
AND a.asc_code = da.asc_code
-- and a.asc_code='2100121'
AND c.asc_status = 1
AND a.balance_time >=
TO_DATE ('20040130', 'yyyymmdd')
AND a.balance_time <
TO_DATE ('20051201', 'yyyymmdd')
AND deliverer IS NOT NULL
AND ( LENGTH (deliverer_phone) >= 7
OR deliverer_mobile IS NOT NULL
)
AND series IS NOT NULL
AND a.BRAND = 'BUICK'
AND da.DEPARTMENT_ID IN (
SELECT D.DEPARTMENT_ID
FROM TS_SGM_SYS_DEPARTMENT D
START WITH D.DEPARTMENT_ID IN (
SELECT UD.DEPARTMENT_ID
FROM TT_SGM_SYS_USERDEPARTMENT UD
WHERE UD.USER_ID IN (47))
CONNECT BY PRIOR D.DEPARTMENT_ID =
D.PARENT_DEPARTMENT)) t) g
WHERE g.rn_vin = 1 AND g.rn_phone = 1)
WHERE rd <= 7
