EXPLAIN SELECT * FROM
( SELECT tb_all.xx45_xx57 hscodes, hsdetail, tb_all.xx60, COALESCE(unitNameTH,tb_all.xx60) units
, SUM(CASE WHEN (tb_all.xx160='2557') THEN tb_all.xx194 END) 'value2557'
, SUM(CASE WHEN (tb_all.xx160='2557') THEN tb_all.xx166 END) 'weight2557'
, SUM(CASE WHEN (tb_all.xx160='2557') THEN tb_all.xx180 END) 'quantity2557'
, SUM(CASE WHEN (tb_all.xx160='2557') THEN tb_all.xx194 END)
/SUM(CASE WHEN (tb_all.xx160='2557') AND (tb_all.xx60='KGM')
THEN tb_all.xx166 WHEN (tb_all.xx160='2557') AND (tb_all.xx60<>'KGM') THEN tb_all.xx180 END) 'p/u2557'
, SUM(CASE WHEN (tb_all.xx160='2556') THEN tb_all.xx194 END) 'value2556'
, SUM(CASE WHEN (tb_all.xx160='2556') THEN tb_all.xx166 END) 'weight2556'
, SUM(CASE WHEN (tb_all.xx160='2556') THEN tb_all.xx180 END) 'quantity2556'
, SUM(CASE WHEN (tb_all.xx160='2556') THEN tb_all.xx194 END)
/SUM(CASE WHEN (tb_all.xx160='2556') AND (tb_all.xx60='KGM')
THEN tb_all.xx166 WHEN (tb_all.xx160='2556') AND (tb_all.xx60<>'KGM') THEN tb_all.xx180 END) 'p/u2556'
, SUM(CASE WHEN (tb_all.xx160='2555') THEN tb_all.xx194 END) 'value2555'
, SUM(CASE WHEN (tb_all.xx160='2555') THEN tb_all.xx166 END) 'weight2555'
, SUM(CASE WHEN (tb_all.xx160='2555') THEN tb_all.xx180 END) 'quantity2555'
, SUM(CASE WHEN (tb_all.xx160='2555') THEN tb_all.xx194 END)
/SUM(CASE WHEN (tb_all.xx160='2555') AND (tb_all.xx60='KGM')
THEN tb_all.xx166 WHEN (tb_all.xx160='2555') AND (tb_all.xx60<>'KGM') THEN tb_all.xx180 END) 'p/u2555'
, SUM(CASE WHEN (tb_all.xx160='2554') THEN tb_all.xx194 END) 'value2554'
, SUM(CASE WHEN (tb_all.xx160='2554') THEN tb_all.xx166 END) 'weight2554'
, SUM(CASE WHEN (tb_all.xx160='2554') THEN tb_all.xx180 END) 'quantity2554'
, SUM(CASE WHEN (tb_all.xx160='2554') THEN tb_all.xx194 END)
/SUM(CASE WHEN (tb_all.xx160='2554') AND (tb_all.xx60='KGM')
THEN tb_all.xx166 WHEN (tb_all.xx160='2554') AND (tb_all.xx60<>'KGM') THEN tb_all.xx180 END) 'p/u2554'
, SUM(CASE WHEN (tb_all.xx160='2553') THEN tb_all.xx194 END) 'value2553'
, SUM(CASE WHEN (tb_all.xx160='2553') THEN tb_all.xx166 END) 'weight2553'
, SUM(CASE WHEN (tb_all.xx160='2553') THEN tb_all.xx180 END) 'quantity2553'
, SUM(CASE WHEN (tb_all.xx160='2553') THEN tb_all.xx194 END)
/SUM(CASE WHEN (tb_all.xx160='2553') AND (tb_all.xx60='KGM')
THEN tb_all.xx166 WHEN (tb_all.xx160='2553') AND (tb_all.xx60<>'KGM') THEN tb_all.xx180 END) 'p/u2553'
FROM (
SELECT xx21,xx24,xx45_xx57,xx60,xx160,xx166,xx180,xx194 FROM import_57 tb_2557
INNER JOIN tariff_group_match tgm ON tb_2557.xx45_xx57=tgm.hscodeid
WHERE tgm.groupID=6
UNION ALL
SELECT xx21,xx24,xx45_xx57,xx60,xx160,xx166,xx180,xx194 FROM import_56 tb_2556
INNER JOIN tariff_group_match tgm ON tb_2556.xx45_xx57=tgm.hscodeid
WHERE tgm.groupID=6
UNION ALL
SELECT xx21,xx24,xx45_xx57,xx60,xx160,xx166,xx180,xx194 FROM import_55 tb_2555
INNER JOIN tariff_group_match tgm ON tb_2555.xx45_xx57=tgm.hscodeid
WHERE tgm.groupID=6
UNION ALL
SELECT xx21,xx24,xx45_xx57,xx60,xx160,xx166,xx180,xx194 FROM import_54 tb_2554
INNER JOIN tariff_group_match tgm ON tb_2554.xx45_xx57=tgm.hscodeid
WHERE tgm.groupID=6
UNION ALL
SELECT xx21,xx24,xx45_xx57,xx60,xx160,xx166,xx180,xx194 FROM import_53 tb_2553
INNER JOIN tariff_group_match tgm ON tb_2553.xx45_xx57=tgm.hscodeid
WHERE tgm.groupID=6
) tb_all
LEFT JOIN units u ON tb_all.xx60=u.unitID
LEFT JOIN tariff t ON tb_all.xx45_xx57=t.hscodeid
GROUP BY tb_all.xx45_xx57, units WITH ROLLUP
) tbALL1
WHERE (hscodes IS NOT NULL AND units IS NOT NULL) OR (hscodes IS NULL AND units IS NULL)
ในส่วนของการ union all
ให้ทำแบบนี้ดีกว่าครับ ลดขนาด tmporary ลง
select 53 y, sum(.. ) s from t53 n, tb where n.xx = tb.xx group by n.xx, n.yy
unionall
select 54, sum(.. ) from t54 n, tb where n.xx = tb.xx group by n.xx, n.yy
unionall
select 55, sum(.. ) from t55 n, tb where n.xx = tb.xx group by n.xx, n.yy
unionall
select 56, sum(.. ) from t56 n, tb where n.xx = tb.xx group by n.xx, n.yy
Load balance : Server 00
