dongshi1934
2019-05-15 07:59
浏览 376

如何在选择查询MySQL,Beego中选择特定ID的总和

I want to get a result like

result 
-------------------------------------------------------
id | uuid | user_id |created_date |    amount    | name 
-------------------------------------------------------
1  | ABC  |    1    |   2019/5/1  |      5       | xa
2  | PQR  |    2    |   2019/5/5  |      150     | xb

A query that I trying to use

SELECT(SELECT SUM(paid_amount) WHERE ID = t1.**HERE**) AS sub1,
(t1.amount - sub1) AS sub2
FROM invoice t1 CROSS JOIN
invoice_paid t2;

Table struct in my DB

table invoice_paid        
------------------------------------
id | uuid | paid_date | paid_amount
------------------------------------
1  | ABC  | 2019/5/1  | 15
2  | ABC  | 2019/5/5  | 80 

table invoice
-------------------------------------------------------
id | uuid | user_id |created_date |    amount    | name 
-------------------------------------------------------
1  | ABC  |    1    |   2019/5/1  |      100     | xa
2  | PQR  |    2    |   2019/5/5  |      150     | xb

I can use sum only 1 condition like where id = 1 but how do I combine this query in select query with a join query. I use beego(golang), MariaDB

图片转代码服务由CSDN问答提供 功能建议

我想获得类似

 结果
的结果 --------------------------------------------------  ----- 
id |  uuid |  user_id |创建日期| 金额| 名称
 -----------------------------------------------  -------- 
1 |  ABC |  1 |  2019/5/1 |  5 |  xa 
2 |  PQR |  2 |  2019/5/5 |  150 |  xb 
   
 
 

我尝试使用的查询

  SELECT(SELECT SUM(paid_amount)WHERE ID = t1  。** HERE **)AS sub1,
(t1.amount-sub1)AS sub2 
FROM发票t1 CROSS JOIN 
invoice_paid t2; 
   
 
 

表结构 在我的数据库中

 表发票已付款
 -----------------------------  ------- 
id |  uuid | 付款日期|  paid_amount 
 ------------------------------------ 
1 |  ABC |  2019/5/1 |  15 
2 |  ABC |  2019/5/5 |  80 
 
表格发票
 ------------------------------------------  ------------- 
id |  uuid |  user_id |创建日期| 金额| 名称
 -----------------------------------------------  -------- 
1 |  ABC |  1 |  2019/5/1 |  100 |  xa 
2 |  PQR |  2 |  2019/5/5 |  150 |  xb 
   
 
 

我只能使用总和1个条件,例如id = 1,但如何在选择查询中将此查询与联接查询结合使用。 我使用beego( golang),MariaDB

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douyi6168 2019-05-15 08:13
    已采纳

    You can use this query. It JOINs the invoice table to a derived table of SUMs of all the amounts paid per invoice from invoice_paid, subtracting that total from the invoice amount to get the outstanding amount:

    SELECT i.id, i.uuid, i.user_id, i.created_date, i.amount - COALESCE(p.amount, 0) AS amount, i.name
    FROM invoice i
    LEFT JOIN (SELECT uuid, SUM(paid_amount) AS amount
               FROM invoice_paid
               GROUP BY uuid) p ON p.uuid = i.uuid
    ORDER BY i.id
    

    Output:

    id  uuid    user_id created_date        name    amount
    1   ABC     1       2019-05-01 00:00:00 xa      5
    2   PQR     2       2019-05-05 00:00:00 xb      150
    

    Demo on dbfiddle

    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题