这是我的MySQL表布局
+----+---------+----------+---------+-------+
| id | AssetId | FromType | ToType | Amount |
+----+---------+----------+---------+-------+
| 1 | 1 | Bank | Asset | 10000 |
+----+---------+----------+---------+-------+
| 2 | 2 | Bank | Asset | 5000 |
+----+---------+----------+---------+-------+
| 3 | 2 | Asset | Bank | 4000 |
+----+---------+----------+---------+-------+
| 4 | 3 | Asset | Bank | 3000 |
+----+---------+----------+---------+-------+
| 5 | 3 | Asset | Bank | 2000 |
+----+---------+----------+---------+-------+
购买的资产为FromType"Bank"到ToType"asset"
资产出售是签证。
我怎样才能像下面这样显示表格。
+---------+----------+-----------+---------+
| AssetId | Purchase | Sale | Balance |
+---------+----------+-----------+---------+
| 1 | 10000 | 0 | 10000 |
+---------+----------+-----------+---------+
| 2 | 5000 | 4000 | 1000 |
+---------+----------+-----------+---------+
| 3 | 0 | 5000 | 5000 |
+---------+----------+-----------+---------+
提前谢谢。
我尝试过这个查询。但是不能正常工作
SELECT id as AssetId, debit, credit, 'Asset' AS tb_name
FROM ( (
SELECT id, SUM( `Amount`) AS debit, '0' AS credit
FROM `erp_assets`
WHERE FromType = 'Asset'
GROUP BY AssetId
) UNION ALL (
SELECT id, SUM( `Amount` ) AS credit, '0' AS debit
FROM `erp_assets`
WHERE ToType = 'Asset'
GROUP BY AssetId
) ) AS tb1
我假设在示例的最后一行中,输出平衡应该是-5000,而不是5000,对吗?
SELECT *, purchase - sale AS balance FROM (
SELECT
assetid,
sum(if(fromtype='bank', amount, 0)) AS purchase,
sum(if(fromtype='asset', amount, 0)) AS sale
FROM foo f1
GROUP BY assetid
) f2
在内部查询中,首先我们汇总金额,其中fromtype是bank,否则为0,而fromtype=asset也是如此。当然,整件事都是按资产ID分组的。
然后在外部查询中,我们从内部查询中选择所有内容,并构建差异(这在内部查询中是不可能的,因为别名在那里不直接可用)。瞧!
请在此处查看其实际操作:http://sqlfiddle.com/#!2/05652/2
我想你的意思是购买-销售=余额,你想在最后一行中有-5000
CREATE TABLE bla
(
id int AUTO_INCREMENT,
AssetId int,
FromType varchar(255),
ToType varchar(255),
Ammount int,
PRIMARY KEY(id)
) ENGINE = MyISAM;
INSERT INTO bla(AssetId,FromType,ToType,Ammount) VALUES
(1,'Bank','Asset',10000),
(2,'Bank','Asset',5000),
(2,'Asset','Bank',4000),
(3,'Asset','Bank',3000),
(3,'Asset','Bank',2000);
SELECT
a.AssetId,a.Purchase as Purchase,a.Sale, a.Purchase-a.Sale as Balance
FROM (
SELECT a.AssetId,
(SELECT IFNULL(SUM(b.Ammount),0) FROM bla as b
WHERE b.AssetId=a.AssetId AND FromType='Bank' AND ToType='Asset') as Purchase,
(SELECT IFNULL(SUM(b.Ammount),0) FROM bla as b
WHERE b.AssetId=a.AssetId AND FromType='Asset' AND ToType='Bank') as Sale
FROM bla as a
Group By a.AssetId) as a;