1、UPDATE 表1 a, 表2 b SET a.字段1 = b.字段1, a.字段2 = b.字段2 WHERE 条件1
例:
update income_invoice_profit_center a,income_invoice_collection o
set a.profit_center_id = o.profit_center_id,a.profit_center_name=o.profit_center_name
where a.invoice_code=o.invoice_code and a.invoice_num=o.invoice_num and
a.profit_center_id is null
2、if(exp,值1,值2)
SELECT
DISTINCT t1.id,
t1.main_id,
t1.name,
if(t2.id is null,0,1) finish -- '1处理,0未处理'
from tax_partner t1
left join tax_declaration_report_partner t2
on t1.id =t2.partner_id
and t2.main_id=#{mainId} and t2.`month`=#{authMonth}
3、多表关联,一对多查询返回结果使用GROUP_CONCAT关键字,并GROUP BY
SELECT
a.id,
a.code,
GROUP_CONCAT(config.num) classification_list,
a.parent_name,
a.name,
a.tax_item,
a.simple_tax_rate,
a.commonly_tax_rate,
a.no_tax_method,
a.created_by,
a.created_date,
a.accounting_entries,
b.username as last_modified_by,
a.last_modified_date
FROM income_and_tax_rate_correspondence a
LEFT JOIN sys_user b on a.last_modified_by = b.id
LEFT JOIN subjects_classification_relation relation ON relation.subjects_id = a.id
LEFT JOIN sys_tax_classification_coding config ON relation.classification_id = config.id
<where>
<if test="code !=null and code != '' ">
a.code = #{code,jdbcType=VARCHAR}
</if>
</where>
GROUP BY a.id
order by a.last_modified_date desc
4、Decode的使用
select t.*,d.name,DECODE(1,t.qy_bj,‘启用’,‘禁用’) qyZt
from a t
LEFT JOIN b d ON d.code = t2.ssyt