优化查询 求高手 在线等谢谢

[hujiefei]

sql 如下
select a.xx,
a.yy,
a.vv,
c.name,
(SELECT bci.DEBIT_ACCOUNT_BALANCE
FROM business_confirm_info bci
WHERE bci.application_id = a.application_id
ORDER BY BUSINESS_CONFIRM_INFO_ID DESC
LIMIT 1)
debitAccountBalance
from application a ,CUSTOMER C,
ORDER BY a.CLEARING_DATE DESC;
主要是那个子查询 因为里面的数据量过大导致查询非常慢 求指教 谢谢

[珠海-天堂]

explain SELECT bci.DEBIT_ACCOUNT_BALANCE
           FROM business_confirm_info bci
          WHERE bci.application_id = a.application_id
         ORDER BY BUSINESS_CONFIRM_INFO_ID DESC
          LIMIT 1;

看看什么结果

[hujiefei]

[rucypli]

from application a ,CUSTOMER C, a和c没有连接条件吗 自然连接？

[珠海-天堂]

引用 1 楼 zuoxingyu2012 的回复:

SQL code?12345explain SELECT bci.DEBIT_ACCOUNT_BALANCE FROM business_confirm_info bci WHERE bci.application_id = a.application_id ORDER BY BUSINESS_CONFIRM_INFO……

不是要看结果，看执行计划。注意EXPLAIN

[hujiefei]

a，c 有连接的忘记写了

[hujiefei]

引用 4 楼 zuoxingyu2012 的回复:

引用 1 楼 zuoxingyu2012 的回复: SQL code?12345explain SELECT bci.DEBIT_ACCOUNT_BALANCE FROM business_confirm_info bci WHERE bci.application_id = a.application_id ORDER BY BUSINES……

取最大的一条的BUSINESS_CONFIRM_INFO_ID 的 内容 比如 id 1 2 3 4 5 那么 取 第四条 的内容

[珠海-天堂]

desc select a.xx, a.yy, a.vv, c.name, (SELECT bci.DEBIT_ACCOUNT_BALANCE FROM business_confirm_info bci WHERE bci.application_id = a.application_id ORDER BY BUSINESS_CONFIRM_INFO_ID DESC LIMIT 1) debitAccountBalance from application a ,CUSTOMER C, ORDER BY a.CLEARING_DATE DESC;

[hujiefei]

居然直接有copy回来了、

[qq4540213]

把(SELECT bci.DEBIT_ACCOUNT_BALANCE FROM business_confirm_info bci WHERE bci.application_id = a.application_id ORDER BY BUSINESS_CONFIRM_INFO_ID DESC LIMIT 1) 拆开 之后用两个查询 把第一个的结果集 获取id 放到第二个去查绝对快

[hujiefei]

没人会嘛？求高手赐教

[WWWWA]

SELECT bci.DEBIT_ACCOUNT_BALANCE FROM business_confirm_info bci where not exists(select 1 from business_confirm_info where bci.application_id = application_id and bci.BUSINESS_CONFIRM_INFO_ID<BUSINESS_CONFIRM_INFO_ID) 需要多长时间，business_confirm_info在application_id、BUSINESS_CONFIRM_INFO_ID上建立复合索引没有？ BUSINESS_CONFIRM_INFO_ID唯一？

[hujiefei]

Lookup Error - MySQL Database Error: Subquery returns more than 1 row 建立了索引的、

[WWWWA]

引用 11 楼 hujiefei 的回复:

Lookup Error - MySQL Database Error: Subquery returns more than 1 row 建立了索引的、

你的SQL语句是什么？10楼？

[hujiefei]

SELECT a.application_no,
       C.CUSTOMER_NO,
       C.NAME,
       (SELECT DEPOSITE_ACCOUNT
          FROM contract_info
         WHERE application_id = a.APPLICATION_ID)
          DEPOSITE_ACCOUNT,
       RPD.CYCLE_NUM,
       (SELECT CYCLES
          FROM repayment_plan
         WHERE REPAYMENT_PLAN_ID = RPD.REPAYMENT_PLAN_ID)
          TOTAL_CYCLES,
       RPD.CLEARING_DATE,
       RPD.TOTAL,
       RPD.PRINCIPAL,
       RPD.INTEREST,
       RPD.EXTEND_MONTH,
       l.advance_no,
       (SELECT if(c.type = 3, company_phone, mobile_phone)
          FROM contact_info
         WHERE contact_info_id = c.contact_info_id)
          phone_num,
       (SELECT name
          FROM branch
         WHERE BRANCH_ID = a.BRANCH_ID)
          BRANCH_NAME,
       (SELECT chinese_name
          FROM users
         WHERE user_id = a.CREDITOR_ID)
          CREDITOR_NAME,
       (SELECT p.PRODUCT_NAME
          FROM product p
         WHERE p.PRODUCT_ID = a.PRODUCT_ID)
          PRODUCT_NAME,
       (SELECT bci.DEBIT_ACCOUNT_BALANCE
          FROM business_confirm_info bci
         WHERE bci.application_id = a.application_id
        ORDER BY BUSINESS_CONFIRM_INFO_ID DESC
         LIMIT 1)
          debitAccountBalance
  FROM REPAYMENT_PLAN_DETAIL RPD,
       LOAN L,
       CUSTOMER C,
       application a,
       repayment r
 WHERE     r.application_id = l.application_id
       AND r.REPAYMENT_PLAN_DETAIL_id = rpd.REPAYMENT_PLAN_DETAIL_id
       AND RPD.application_id = L.APPLICATION_ID
       AND a.CUSTOMER_ID = C.CUSTOMER_ID
       AND L.APPLICATION_ID = a.APPLICATION_ID
       AND a.BANK_ID = 1
       AND RPD.CLEARING_DATE >= DATE('2012-01-01')
       AND RPD.CLEARING_DATE < DATE(adddate('2012-12-31', 1))
       AND a.APPLICATION_STATUS_ID = 13
ORDER BY RPD.CLEARING_DATE DESC