﻿ 讨教一个sql语句的实现 | Code Bye

讨教一个sql语句的实现

7年前 (2016-02-06) 787次浏览

id        order_id       jy_type        amount
(流水ID      (订单id)   (交易类型)     (交易金额)
1                  1                         0              200
2                  1                         1              180
3                  1                         0               50

order_id      receivable        payable
(订单id)       (应收总额)     (应付总额)
1                    300            200

1              300              250         50            200          180         20

40

```select a.order_id,b.receivable,a.received,(b.receivable-a.received),b.payable,a.pay,(b.payable-a.pay)
from (select order_id,sum(if(jy_type = 0, 1, 0))received,sum(if(jy_type = 1, 1, 0))pay from `transaction` group by order_id)a,`order` b where a.order_id = b.order_id;
```

20

@zhangbin1988，看了一下，结果有问题：

if(jy_type = 0, 1, 0)这种用法相似于三目运算符，if else的快捷写法。

```select a.order_id,b.receivable,a.received,(b.receivable-a.received),b.payable,a.pay,(b.payable-a.pay)
from (select order_id,sum(if(jy_type = 0, amount, 0))received,sum(if(jy_type = 1, amount, 0))pay from `transaction` group by order_id)a,`order` b where a.order_id = b.order_id;```

20

```SELECT a.订单号,a.应收总额,a.已收,a.未收,a.应付总额,b.已付,b.未付 FROM
(SELECT b.order_id AS 订单号,b.receivable AS 应收总额,SUM(a.amount) AS 已收,(b.receivable-SUM(a.amount)) AS 未收,b.payable AS 应付总额
FROM `transaction` a
INNER JOIN `order` b
ON b.order_id = a.order_id AND a.jy_type = 0) a
INNER JOIN
(SELECT b.order_id AS 订单号,SUM(a.amount) AS 已付,(b.payable-SUM(a.amount)) AS 未付
FROM `transaction` a
INNER JOIN `order` b
ON b.order_id = a.order_id AND a.jy_type = 1) b
ON a.订单号= b.订单号```

CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明讨教一个sql语句的实现

[1034331897@qq.com]