请教一个sql语句的实现

[daviddivad]

表结构如下

一、交易流水表:transaction(一个订单可能有多笔流水)
id order_id jy_type amount
(流水ID (订单id) (交易类型) (交易金额)

1 1 0 200
2 1 1 180
3 1 0 50

交易类型：0：代表收款，1：代表付款

二、订单表：order
order_id receivable payable
(订单id) (应收总额) (应付总额)
1 300 200

需要统计出以下结果：

订单号 应收总额 已收 未收 应付总额 已付 未付

1 300 250 50 200 180 20

[ACMAIN_CHM]

http://blog.csdn.net/acmain_chm/article/details/4283943 MySQL交叉表 在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下：数据样本： create table tx( id int primary key, c1 c...

[LongRui888]

这个可以用子查询来实现的。

[卖水果的net]

嗯，两个标量子查询，就可以了；

[ayzen1988]

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;

[daviddivad]

@zhangbin1988，运行错误， You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order b where a.order_id = b.order_id' at line 4

[daviddivad]

@zhangbin1988，没错，是我表写错了，感谢

[daviddivad]

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


另外，if(jy_type = 0, 1, 0)，这种写法没看懂，是啥意思呢

[qq_17367431]

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.订单号

[iris4649]

引用 7 楼 daviddivad 的回复:

@zhangbin1988，看了一下，结果有问题： 另外，if(jy_type = 0, 1, 0)，这种写法没看懂，是啥意思呢

if(jy_type = 0, 1, 0)这种用法类似于三目运算符，if else的快捷写法。 想要正确的结果，这里应该是if(jy_type = 0, amount, 0)

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;