qq_29297291
qq_29297291
2018-11-14 07:33

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

20
  • 优化
  • mysql

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条回答