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;
主要是那个子查询 因为里面的数据量过大导致查询非常慢 求指教 谢谢
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;
主要是那个子查询 因为里面的数据量过大导致查询非常慢 求指教 谢谢
解决方案 »
- ? MYSQL字符型字段比较?
- mysql 是否可以像sql server一样,备份时生成一个.bak文件?
- 怎么设置主键ID为从0开始并且自动增长的?
- 获得mysql数据表名称
- 如何用not in 代替not exists?一题作业?
- 每日统计的一个查询,该怎么写
- 难问题.不通过程序,而直接sql语句删除掉数据库重复记录的数据.比如ID相同的就删除掉.
- MySQL 如何添加用户和设置权限?
- ------------mysql-front能导出*.sql吗?????-----------在线
- mysql查询效率优化问题
- mysql定时导入数据文件
- 【修改datetime字段值报错】如果把datetime字段修改成大于2039-01-08则报错,小于它则OK,请各位大师帮忙分析一下,谢谢!
FROM business_confirm_info bci
WHERE bci.application_id = a.application_id
ORDER BY BUSINESS_CONFIRM_INFO_ID DESC
LIMIT 1;看看什么结果
a和c没有连接条件吗 自然连接?
不是要看结果,看执行计划。注意EXPLAIN
取最大的一条的BUSINESS_CONFIRM_INFO_ID 的 内容 比如 id 1 2 3 4 5
那么 取 第四条 的内容
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;
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唯一?
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