以主表为基础，如果将满足条件的多个数据细节表中最新的一条拼接到主表为基础输出

[chilli6519]

有一个主表 sys_user 是用户表，包含字段 userid, uname

有多个细节数据表， 第一个为 购买的衣服表 atable1 ,包含字段 uid , title, price, cdate
第二个表为化妆品表 btable2 ,包含字段 uid , name, count,cdate
第三个表为 鞋子 ctable3 , 包含字段 uid, name, value,cdate
等等可能还有多个

每个细节数据表中 同一个人可能有多条数据
如何才能按照主表 sys_user 为基础列出一个总表
userid uname , atable1.price , btable2.count , ctable3.value

后面的字段 atable1.price , btable2.count , ctable3.value 是在对应表按照 cdate 取最后一条拼接

用left join 不能过滤最后一条

请指导， 谢谢

[chilli6519]

sys_user 表： userid, uname 1 abc 2 bcd 3 cde 表 atable1 ： uid , title, price, cdate 1 eee 12 2017-09-21 1 ddd 14 2017-09-26 3 aaa 9 2017-09-18 表 btable2 ： uid , name, count,cdate 1 ddd 1 2017-09-21 2 ccc 2 2017-09-21 1 ccc 5 2017-09-25 表ctable3： uid, name, value ,cdate 2 oooo 3 2017-08-19 2 iiiii 5 2017-08-10 3 aaaaa 8 2017-09-21 希望检索的结果： userid uname , atable1.price , btable2.count , ctable3.value 1 abc 14 5 0 2 bcd 0 2 3 3 cde 9 0 8

[benluobo]

select u1.userid, u1.uname, ifnull(a.price,0), ifnull(b.count,0), ifnull(c.value,0) from user u1 left join (select a.* from atable1 a where 1 > (select count(*) from atable1 b where a.uid = b.uid and b.cdate > a.cdate)) a on u1.userid = a.uid left join (select a.* from btable2 a where 1 > (select count(*) from btable2 b where a.uid = b.uid and b.cdate > a.cdate)) b on u1.userid = b.uid left join (select a.* from ctable3 a where 1 > (select count(*) from ctable3 b where a.uid = b.uid and b.cdate > a.cdate)) c on u1.userid = c.uid

[zjcxc]

SELECT A.*,
	(SELECT price FROM atable1 b WHERE b.uid = A.userid ORDER BY cdate DESC LIMIT 1) as `atable1.price`,
	(SELECT `count` FROM btable2 b WHERE b.uid = A.userid ORDER BY cdate DESC LIMIT 1) as `btable2.count`,
	(SELECT value FROM ctable3 b WHERE b.uid = A.userid ORDER BY cdate DESC LIMIT 1) as `ctable3.value`
FROM sys_user A