Mysql最新记录为不同列 [英] Mysql latest record for distinct column

问题描述

我有一张表：发票

  inv_id cus_id due_amt已支付total_due 
 1 71 300 0 300 
 2 71 200 0 500 
 3 71 NULL 125 375 
 4 72 50 0 50 
 5 72 150 0 200 
  

我要结果

  cus_id total_due 
 71 375 
 72 200 
  

这就是我想 total_due of unique customer 或者可以说我需要最新发票 唯一客户的详情。

$ b

SELECT cus_id，total_due FROM invoice GROUP BY cus_id ORDER BY inv_id DESC

但是这并不给出所需的结果。

请别人帮我..
<创建子查询以获取客户的最近 total_due $ b> $ b

  SELECT cus_id，（从发票中选择total_due，其中inv_id = max（a.inv_id））as total_due FROM invoice a GROUP BY cus_id ORDER BY inv_id DESC 
  

此处演示

I have a table: invoice

inv_id    cus_id    due_amt    paid      total_due
1         71        300         0        300
2         71        200         0        500
3         71        NULL        125      375
4         72        50          0        50
5         72        150         0        200

I want the result

cus_id   total_due
71       375  
72       200

That is I want the total_due of unique customer or otherwise can say I need the latest invoice details of unique customer.

What I tried:

SELECT cus_id, total_due FROM invoice GROUP BY cus_id ORDER BY inv_id DESC

But this not give the required result.

Please someone can help me..

解决方案

create a subquery to get the recent total_due of the customer

SELECT cus_id, (select total_due from invoice where inv_id=max(a.inv_id)) as total_due FROM invoice a GROUP BY cus_id ORDER BY inv_id DESC

Demo here

这篇关于Mysql最新记录为不同列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！