说起mysql数据库,我们可以很熟练的使用left join
经常说的小表驱动大表,以左边的表的数据为基准
今天要说是的leftjoin如何只取得右表中的一条数据
有人说我可以用php来实现
但是有点麻烦,看看单纯用sql语句能不能实现
select o.order_sn,from_unixtime(o.add_time) from user u left join order o on u.username=o.username where u.username='13812345678';
以上为查询所有的订单
SELECT
o.order_sn,from_unixtime(o.add_time)
FROM
user as u
LEFT JOIN (
SELECT order_sn,username,add_time
FROM
order
WHERE
1 and username in('13812345678','13112345678')
ORDER BY
add_time ASC
) as o ON o.username = u.username
WHERE
1 and u.username in('13812345678','13112345678')
GROUP BY
u.username
以上sql为查询时间最早的数据
SELECT
a.username,from_unixtime(a.create_time),b.order_sn,from_unixtime(b.add_time)
FROM
user a
LEFT JOIN
(SELECT *FROM order WHERE id IN (SELECT max(id) FROM order where username in('13812345678','13112345678') GROUP BY username ) ) b ON b.`username` = a.`username` where b.username in('13812345678','13112345678')
以上sql为查询时间最新数据
如有问题,可以QQ搜索群1028468525加入群聊,欢迎一起研究技术
转载请注明:mysql中如何用left join只取得右表中一条记录 出自老鄢博客 | 欢迎分享