JAVA erp项目 多表联查
一张销售表(销售表),两张副表,分别为普通销售表和剪板销售表,有主外键关联,怎么获得如图所示效果
1.使用left join分别关联不同销售表和剪板销售表（两个sql）
2.使用union all 将结果汇总
希望可以帮到你
select 表中字段 from 销售表，普通销售表，剪板销售表 where 销售表与普通销售表的关联 and 销售表与剪板销售表的关联 and 其它条件 一条sql语句基本解决问题。希望可以解决你的问题
select 三张表中字段 from tableA a,tableB b,tableC c where a.id=b.bid and b.bid=c.id;
delete from 职工，工资，请假 as z,g,q INNER JOIN z.id=id and g.zId=id and q.ZId=id where z.id="222" 给楼主附带个删除的
销售表(销售表),两张副表,分别为普通销售表和剪板销售表 ，这三张表分别包含哪些字段？
select 主表.* , 附表1.* from 主表 left join 附表1 on 条件
union all
select 主表.*，附表2.* from 主表 left join 附表2 on 条件
参考了一些回答，不知道是不是你想要的。
在mysql中，不支持union all, 使用 左连接+union+有连接 方式，
其中 序号，日期，是以组为单位的，用到 group by 序号，日期，
其他的是一个合计查询，用到sum函数， 下面给个例子，请作为参考：
SELECT * FROM
(
SELECT
rm.clear_date,
rm.card_type,
rm.access_inst,
rm.rate_type,
IFNULL(rm.trans_count,0) rm_trans_count,
IFNULL(rm.trans_amount,0) rm_trans_amount,
IFNULL(rm.trans_fee,0) rm_trans_fee,
IFNULL(rm.trans_cost,0) rm_trans_cost,
IFNULL(rm.trans_profit,0) rm_trans_profit,
IFNULL(py.trans_count,0) py_trans_count,
IFNULL(py.trans_amount,0) py_trans_amount,
IFNULL(py.trans_fee,0) py_trans_fee,
IFNULL(py.trans_cost,0) py_trans_cost,
IFNULL(py.trans_profit,0) py_trans_profit,
IFNULL(py.ndef1,0) py_ndef1
FROM
(
SELECT
clear_date,
access_inst,
rate_type,
card_type,
sum(trans_count) trans_count,
sum(trans_amount) trans_amount,
sum(trans_fee) trans_fee,
sum(trans_cost) trans_cost,
sum(trans_profit) trans_profit
FROM
sys_trans_profit
WHERE
busi_type = 'aaa'
GROUP BY
clear_date,
access_inst,
rate_type,
card_type
) rm
LEFT JOIN (
select
clear_date,
access_inst,
rate_type,
card_type,
sum(trans_count) trans_count,
sum(trans_amount) trans_amount,
sum(trans_fee) trans_fee,
sum(trans_cost) trans_cost,
sum(trans_profit) trans_profit,
sum(ndef1) ndef1
FROM
sys_trans_profit
WHERE
busi_type = 'bbb'
GROUP BY
clear_date,
access_inst,
rate_type,
card_type
) py ON rm.clear_date = py.clear_date
AND rm.access_inst = py.access_inst
AND rm.rate_type = py.rate_type
AND rm.card_type = py.card_type
WHERE
1 = 1
<if test="clearDate != null and clearDate != ''">
and rm.clear_date=#{clearDate}
</if>
<if test="clearDateBeginDt != null and clearDateBeginDt != ''">
and rm.clear_date >= #{clearDateBeginDt}
</if>
<if test="clearDateEndDt != null and clearDateEndDt != ''">
and rm.clear_date <= #{clearDateEndDt}
</if>
<if test="accessInst != null and accessInst != ''">
and rm.access_inst=#{accessInst}
</if>
<if test="rateType != null and rateType != ''">
and rm.rate_type=#{rateType}
</if>
<if test="cardType != null and cardType != ''">
and rm.card_type=#{cardType}
</if>
UNION
SELECT
py.clear_date,
py.card_type,
py.access_inst,
py.rate_type,
IFNULL(rm.trans_count,0) rm_trans_count,
IFNULL(rm.trans_amount,0) rm_trans_amount,
IFNULL(rm.trans_fee,0) rm_trans_fee,
IFNULL(rm.trans_cost,0) rm_trans_cost,
IFNULL(rm.trans_profit,0) rm_trans_profit,
IFNULL(py.trans_count,0) py_trans_count,
IFNULL(py.trans_amount,0) py_trans_amount,
IFNULL(py.trans_fee,0) py_trans_fee,
IFNULL(py.trans_cost,0) py_trans_cost,
IFNULL(py.trans_profit,0) py_trans_profit,
IFNULL(py.ndef1,0) py_ndef1
FROM
(
SELECT
clear_date,
access_inst,
rate_type,
card_type,
sum(trans_count) trans_count,
sum(trans_amount) trans_amount,
sum(trans_fee) trans_fee,
sum(trans_cost) trans_cost,
sum(trans_profit) trans_profit
FROM
sys_trans_profit
WHERE
busi_type = 'bbbb'
GROUP BY
clear_date,
access_inst,
rate_type,
card_type
) rm
RIGHT JOIN (
select
clear_date,
access_inst,
rate_type,
card_type,
sum(trans_count) trans_count,
sum(trans_amount) trans_amount,
sum(trans_fee) trans_fee,
sum(trans_cost) trans_cost,
sum(trans_profit) trans_profit,
sum(ndef1) ndef1
FROM
sys_trans_profit
WHERE
busi_type = 'aaa'
GROUP BY
clear_date,
access_inst,
rate_type,
card_type
) py ON rm.clear_date = py.clear_date
AND rm.access_inst = py.access_inst
AND rm.rate_type = py.rate_type
AND rm.card_type = py.card_type
WHERE
1 = 1
<if test="clearDate != null and clearDate != ''">
and py.clear_date=#{clearDate}
</if>
<if test="clearDateBeginDt != null and clearDateBeginDt != ''">
and py.clear_date >= #{clearDateBeginDt}
</if>
<if test="clearDateEndDt != null and clearDateEndDt != ''">
and py.clear_date <= #{clearDateEndDt}
</if>
<if test="accessInst != null and accessInst != ''">
and py.access_inst=#{accessInst}
</if>
<if test="rateType != null and rateType != ''">
and py.rate_type=#{rateType}
</if>
<if test="cardType != null and cardType != ''">
and py.card_type=#{cardType}
</if>
) sc order by clear_date desc
这是一个mybatis版本，其中包括，排序，左右连接查询，union,ifnull()函数，sum()函数
select a.*,
b.*,
c.*
from 销售表 a
left join 副表1 b
on e.主键 = b.外键
left join 副表2 c
on e.主键 = c.外键