时间戳

首页 > 解决方案 > mysql内部连接3个表从最大日期获取最后一条记录

mysql - mysql内部连接3个表从最大日期获取最后一条记录

问题描述

我有 3 个我想加入的表，但是除了对某些列求和之外，我还需要显示数据库中的每个 user_id 和用户名，但还需要通过一些添加和基于日期的查询来加入他们相应的帐户表。这是我想说的一个例子。

表用户

user_id || user_name || user_dob    
    301 || john doe   ||  1955-01-01    
    312 || Bill Gates || 1976-01-01

表帐户

 id  || child_id ||          inv_date     || inv_total || inv_funding    
 38  ||   301    ||  2018-05-03 12:56:38  ||    486.5  ||   45.55     
 39  ||   301    ||  2018-08-03 14:56:38  ||    222.5  ||   118.5     
 40  ||   312    ||  2018-04-03 11:56:38  ||    26.23  ||   318.5     
 41  ||   312    ||  2018-05-03 12:56:38  ||    223.22 ||   238.5     
 42  ||   312    ||  2018-06-03 13:56:38  ||    486.5  ||   258.5

表accounts_balance

id || child_id || balance    
1  ||    301   || 302.00    
2  ||    312   || 43.33

对于按 user_name 排序的 Table Users 中的每个用户，生成的表需要看起来像这样

User Name || Last Invoiced Date || Last Funding Amount || Last Invoice Amount || Total Funding To Date || Total Invoiced To Date || Balance
john doe  || 2018-08-03 14:56:38||      118.5          ||        222.5        ||        164.05         ||        712.00          ||    302.00

这是我的一些代码，因为 c# 格式显然不能正常工作。

            invoiceTable.Query = "select  " +
                "(" +
                "max(users.user_id), " +
                "users.user_name, " +
                "max(accounts.inv_date), " +
                "max(accounts.inv_funding) as Last_funding, " +
                "max(accounts.inv_total) as Last_Invoice, " +
                "sum(accounts.inv_funding) as Total_Funding, " +
                "sum(accounts.inv_total) as Total_Invoiced from users " +
                ")" +
                "left join accounts on accounts.child_id = users.user_id group by users.user_name";

标签： mysql

解决方案

你可以尝试使用sub-query 

          select t1.*,t2.inv_funding as Last_funding,t2.inv_total as 
          Last_Invoice,ab.balance  from
               (
                select users.user_id, 
                users.user_name, 
                max(accounts.inv_date) as inv_date,
                sum(inv_funding) as Total_Funding,
                sum(inv_total) as Total_Invoiced 
                from users                    
                left join accounts on accounts.child_id = users.user_id
                group by users.user_name,users.user_id 
             ) as t1
         left join accounts t2 on t1.inv_date=t2.inv_date
         left join accounts_balance ab on t1.user_id =ab.user_id

推荐阅读