内连接(inner join)
语句:
select [表字段1],[表字段2],... from [表A],[表B] where [表A.相同字段] = [表B.相同字段]
隐式内连接:
select first_name, last_name, order_date, order_amount
from customers, orders
where customers.customer_id = orders.customer_id
显示内连接:
select first_name, last_name, order_date, order_amount
from customers c
inner join orders o
on c.customer_id = o.customer_id
左外连接(left outer join,outer可省略)
语句:
select [表字段1],[表字段2],... from [表A] LEFT JOIN [表B] on [表A.预关联的字段] = [表B.预关联的字段]
select first_name, last_name, order_date, order_amount
from customers c
left join orders o
on c.customer_id = o.customer_id
SELECT
o.`id`,
o.`oil_name`,
o.`address`,
o.`longitude`,
o.`latitude`,
o.`discount`,
o.`explains`,
DATE_FORMAT (o.`adddate`, '%Y-%m-%d %H:%i') ADDDATE,
o.`status`,
a.id aid,
a.`attMiniUrl`,
a.`attType`,
a.`attUrl`,
a.`businessType`,
a.`isdefault`,
a.`name`,
DATE_FORMAT (
a.`uploadTime`,
'%Y-%m-%d %h:%i'
) uploadTime
FROM
b_oil o
LEFT JOIN b_attachment a
ON o.id = a.businessId
AND a.businessType = 1
<where>
o.isdel=0
<if test="oil_name !=null and oil_name !='' ">
and o.oil_name like concat('%',#{oil_name},'%')
</if>
<if test="address !=null and address !='' ">
and o.address like concat('%',#{address},'%')
</if>
<if test="status!=null and status!=''">
and o.status=#{status}
</if>
</where>
<if test="begin!=null">
limit #{begin},#{pageSize}
</if>
右外连接(right outer join,outer可省略
select first_name, last_name, order_date, order_amount
from customers c
right join orders o
on c.customer_id = o.customer_id