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

发布于 2024-12-01 05:20:27 字数 697 浏览 0 评论 0原文

我有 2 个表：tbl_customers 和 tbl_customers_notes

我在表行中显示所有客户（就像一个表一样），并且我想添加一列显示相关 tbl_customers_notes 表中最后一个已知的datetime 记录。

显然，这是一个一对多的关系，其中匹配的recID将是customerid。

这是我到目前为止所得到的：

<?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>
<? } ?>

如果我与注释表进行 JOIN 操作，我会得到重复项。我可以限制 tbl_customers_notes 仅选择该 customerid 的最后一条已知记录吗？

原文

I have a 2 tables: tbl_customers, and 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.

Obviously this is a one-to-many relationship where the matching recID is going to be 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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风渺 2024-12-08 05:20:27

试试这个：

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

按 customerid 和 customername 进行分组的原因是，以防相同的 customername 可能有不同的 customerid。外连接是包含没有备注的客户。

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.

回复收藏 0 原文

黑色毁心梦 2024-12-08 05:20:27

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;

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;

回复收藏 0 原文

慈悲佛祖 2024-12-08 05:20:27

我认为 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)

I think a HAVING is in order.

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)

回复收藏 0 原文

~没有更多了~