以下多表联合查询的思路是：先对其中两张（也可为更多张）数据表做联合查询，得到几个联合查询的中间结果，再对这几个中间结果进行二次联合查询，以简化代码及表之间的数据显示关系，方便进行中间查询结果的调试。
一，商品表与分类表（AAA表）：
SELECT jxc_bm_sp.bm, jxc_bm_sp.bm_lb, jxc_bm_sp.mc, jxc_bm_sp.gg, jxc_bm_sp.dw, jxc_bm_spfl.mc AS bm_lb_name
FROM jxc_bm_sp LEFT OUTER JOIN jxc_bm_spfl ON jxc_bm_sp.bm_lb = jxc_bm_spfl.bm
二，附加表与储值卡总表（查询条件为：卡的状态；门店范围）（BBB表）：
SELECT jxc_bm_czk_MoteAdd.*, jxc_bm_czk.zt, jxc_bm_czk.je_cz FROM jxc_bm_czk_MoteAdd LEFT OUTER JOIN jxc_bm_czk
ON jxc_bm_czk_MoteAdd.czk_bm = jxc_bm_czk.bm
WHERE (jxc_bm_czk.zt = 0) AND (jxc_bm_czk_MoteAdd.czk_ck LIKE '8%')
三，对充值表做分组统计（查询条件为：时间期间范围）（CCC表）：
SELECT jxc_bm_czk_cz.bm_czk, SUM(jxc_bm_czk_cz.je_cz) AS je_cz_sum FROM jxc_bm_czk_cz
WHERE (jxc_bm_czk_cz.jxc_yyyymm LIKE '201411%') GROUP BY jxc_bm_czk_cz.bm_czk
四，对以上AAA、BBB、CCC三表结果再做联合查询：
SELECT * FROM (SELECT jxc_bm_czk_MoteAdd.*, jxc_bm_czk.zt, jxc_bm_czk.je_cz FROM jxc_bm_czk_MoteAdd LEFT OUTER JOIN
jxc_bm_czk ON jxc_bm_czk_MoteAdd.czk_bm = jxc_bm_czk.bm
WHERE (jxc_bm_czk.zt = 0) AND (jxc_bm_czk_MoteAdd.czk_ck LIKE '8%'))
DERIVEDTBL LEFT OUTER JOIN
(SELECT jxc_bm_sp.bm, jxc_bm_sp.bm_lb, jxc_bm_sp.mc, jxc_bm_sp.gg, jxc_bm_sp.dw, jxc_bm_spfl.mc AS bm_lb_name FROM jxc_bm_sp LEFT OUTER JOIN jxc_bm_spfl ON jxc_bm_sp.bm_lb = jxc_bm_spfl.bm)
DERIVEDTBL_1 ON DERIVEDTBL.czk_sp = DERIVEDTBL_1.bm LEFT OUTER JOIN (SELECT jxc_bm_czk_cz.bm_czk, SUM(jxc_bm_czk_cz.je_cz) AS je_cz_sum FROM jxc_bm_czk_cz WHERE (jxc_bm_czk_cz.jxc_yyyymm LIKE '201411%') GROUP BY jxc_bm_czk_cz.bm_czk)
DERIVEDTBL_2 ON DERIVEDTBL.czk_bm = DERIVEDTBL_2.bm_czk
五，先分组，再查询每组中时间最早的记录行（两表联合查询）：
SELECT *
FROM (SELECT bm, je_cz, user_name, zt
FROM jxc_bm_czk
WHERE je_cz > 0 AND user_name = '' AND zt = 0) tb_a INNER JOIN
(SELECT bm_czk, MIN(yyyymmdd) AS yyyymmdd, MIN(dh) AS dh
FROM jxc_bm_czk_cz
GROUP BY bm_czk) tb_b ON tb_a.bm = tb_b.bm_czk |
|
