sql in的优化
由于项目是个老项目,忽然发现页面加载好慢。需要进行优化:
Sql 如下:
SELECT
PKID_C_FARM_PRODUCTS,
CATEGORY_CODE,
CATEGORY_NAME,
VARIETY_NAME
FROM
C_FARM_PRODUCTS A
WHERE
A.CATEGORY_CODE = ‘2’
AND A.PKID_C_FARM_PRODUCTS in (SELECT DISTINCT
A.FKID_C_FARM_PRODUCTS
FROM
T_FARM_PRODUCTS_TABLE A,
T_COLLECTION_TASK B
WHERE
A.FKID_T_COLLECT_TASK = B.PKID_T_COLLECTION_TASK
AND A.PRICE > 0
AND B.FKID_S_COLLECTION_PLACE in (‘11111’,‘2222’,‘3333’,‘3333’))
本来想用EXISTS语句解决。弄了半天不对,后来就用 join 解决了
SELECT
PKID_C_FARM_PRODUCTS,
CATEGORY_CODE,
CATEGORY_NAME,
VARIETY_NAME
FROM
C_FARM_PRODUCTS A
right JOIN (
SELECT DISTINCT
A.FKID_C_FARM_PRODUCTS
FROM
T_FARM_PRODUCTS_TABLE A,
T_COLLECTION_TASK B
WHERE
A.FKID_T_COLLECT_TASK = B.PKID_T_COLLECTION_TASK
AND A.PRICE > 0
AND B.FKID_S_COLLECTION_PLACE in (11111’,‘2222’,‘3333’,‘3333’))
) c ON A.PKID_C_FARM_PRODUCTS = c.FKID_C_FARM_PRODUCTS
WHERE
A.CATEGORY_CODE = ‘2’