Mysql的关联查询语句查询优化 20C

SELECT
a.org_no orgNo,
a.rcvbl_ym rcvblYm,
SUM(IFNULL(a.rcvbl_amt, 0)) rcvblAmtTotal,
SUM(IFNULL(a.rcvbl_inprice_amt, 0)) rcvblInpriceAmtTotal,
SUM(a.city) city,
SUM(a.agriculturalLoan) agriculturalLoan,
SUM(a.renewableEnergy) renewableEnergy,
SUM(a.lowVoltagePower) lowVoltagePower,
SUM(a.nationalMajor) nationalMajor,
SUM(a.powerConstruction) powerConstruction,
SUM(a.smallReservoir) smallReservoir,
SUM(a.largeReservoirs) largeReservoirs
FROM
(SELECT
a.org_no,
a.release_date,
a.calc_id,
a.t_pq,
a.rcvbl_ym,
a.rcvbl_amt,
a.rcvbl_inprice_amt,
SUM(
CASE
WHEN b.item_code = '03'
THEN IFNULL(b.pl_amt, 0)
ELSE 0
END
) city,
SUM(
CASE
WHEN b.item_code = '04'
THEN IFNULL(b.pl_amt, 0)
ELSE 0
END
) agriculturalLoan,
SUM(
CASE
WHEN b.item_code = '07'
THEN IFNULL(b.pl_amt, 0)
ELSE 0
END
) renewableEnergy,
SUM(
CASE
WHEN b.item_code = '08'
THEN IFNULL(b.pl_amt, 0)
ELSE 0
END
) lowVoltagePower,
SUM(
CASE
WHEN b.item_code = '11'
THEN IFNULL(b.pl_amt, 0)
ELSE 0
END
) nationalMajor,
SUM(
CASE
WHEN b.item_code = '9901'
THEN IFNULL(b.pl_amt, 0)
ELSE 0
END
) powerConstruction,
SUM(
CASE
WHEN b.item_code = '9902'
THEN IFNULL(b.pl_amt, 0)
ELSE 0
END
) smallReservoir,
SUM(
CASE
WHEN b.item_code = '9904'
THEN IFNULL(b.pl_amt, 0)
ELSE 0
END
) largeReservoirs
FROM
m_a_rcvbl_flow a
LEFT JOIN m_a_rcvbl_pl_flow b ON a.id = b.rcvbl_amt_id
WHERE a.org_no IN
(SELECT
a.id
FROM
ac_org a
WHERE a.id_path LIKE
(SELECT
CONCAT(id_path, '%')
FROM
ac_org
WHERE id = "14416"))) a

            数据量大的时候很慢,有什么方法能稍微加快查询吗

2个回答

1.索引优化
2.业务分表
3.拆sql用java逻辑实现

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问