MySql选择查询（在JOIN上选择限制）

I have a 2 tables: tbl_customers, and tbl_customers_notes

我有2个表:tbl_customers和tbl_customers_notes

I'm displaying all my customers in table rows (as one does), and I want to add a column that is for displaying the last known datetime record in the related tbl_customers_notes table.

我在表行中显示所有客户(如同),我想添加一个列,用于在相关的tbl_customers_notes表中显示最后一个已知的datetime记录。

Obviously this is a one-to-many relationship where the matching recID is going to be customerid.

显然,这是一对多的关系,匹配的recID将成为customerid。

Here is what I have so far:

这是我到目前为止:

<?php
$result = mysql_query("SELECT `customername` FROM `tbl_customers` ");
while($row = mysql_fetch_array( $result )) {
?>  
    <tr>
      <td><?php echo $customername;?></td>
      <td><?php echo 'note datetime'; ?></td>
    </tr>
<? } ?>

If I do a JOIN with the notes table I get duplicates. Can I limit the tbl_customers_notes to just select the last known record for that customerid?

如果我用notes表进行JOIN,我会得到重复。我可以限制tbl_customers_notes只选择该customerid的最后一条已知记录吗?

3 个解决方案

#1

Try this:

select c.customerid, c.customername, max(cn.note_date)
from tbl_customers c
left join tbl_customers_notes cn on c.customerid = cn.customerid
group by  c.customerid, c.customername

The reason for grouping by both customerid and customername is that in case same customername could have different customerids. Outer join is to include customers that have no notes.

customerid和customername分组的原因是,如果相同的customername可能具有不同的customerid。外部联接包括没有备注的客户。

#2

SELECT `customername`, max_datetime FROM `tbl_customers` c left join 
    (select max(datetime) max_datetime, customerid 
    from tbl_customers_notes cn group by customerid) cn on 
 on c.id=cn.customerid;

#3

I think a HAVING is in order.

我认为HAVING是有序的。

SELECT c.customername, n.note_date
FROM tbl_customers c
         INNER JOIN tbl_customers_notes n ON c.customerid = n.customerid
GROUP BY c.customername
HAVING n.note_date = MAX(n.note_date)

#1