一行一对多的mysql

Question

我有两个表:

id_c    name    surname
 1      frank   pulio
 2      mark    adam
 3      peter   norton


id_v   id_c    data_visited    note
  1      1     2013-10-12      first visited to frank
  2      1     2013-10-16      second visited to frank
  3      2     2013-10-15      first visited to mark
  4      1     2013-10-20      third visited to frank
  5      2     2013-10-18      second visited to mark

我会获取带有上次访问数据的客户名称并注明。

例子:

id_c   name   data_visited   note
  1    frank   2013-10-20    third visited to frank   
  2    mark    2013-10-18    second visited to mark 

Answer 1

 SELECT t1.`id_c`,c.`name`,t1.latest,t1.`note` FROM `tablea`c

 INNER JOIN
 (
 SELECT t2.`id_c`,t2.latest,t3.`note` from `tableb` t3 

 INNER JOIN

 (SELECT `id_c`,MAX(`data_visited`) AS latest from
`tableb` 
GROUP BY(`id_c`))t2
 ON (t3.`id_c`=t2.`id_c`) AND (t3.`data_visited`=t2.latest)

)t1
ON(c.`id_c`=t1.`id_c`)