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`) 

Answer 2

试试这个：

SELECT A.id_c, A.name, A.data_visited, A.note 
FROM (SELECT A.id_c, A.name, B.data_visited, B.note 
      FROM tableA A 
      INNER JOIN tableB B ON A.id_c = B.id_c 
      ORDER BY A.id_c, B.data_visited DESC) AS A 
GROUP BY A.id_c

检查SQL FIDDLE演示

输出

| ID_C |  NAME |                   DATA_VISITED |                   NOTE |
|------|-------|--------------------------------|------------------------|
|    1 | frank | October, 20 2013 00:00:00+0000 | third visited TO frank |
|    2 |  mark | October, 18 2013 00:00:00+0000 | SECOND visited TO mark |