MySQL Sum + Inner Query в той же таблице

Question

Это моя таблица 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 |
+----+---------+----------+---------+-------+

Приобретенный актив - от типа "Банк" до типа "Актив".
И активы проданы это визы визы.

Как я могу отобразить таблицу, как показано ниже.

+---------+----------+-----------+---------+
| 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

Answer 1

Я предполагаю, что в последней строке вашего примера выходной баланс должен быть -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 - это банк, в противном случае - 0, и то же самое, что и наоборот, для fromtype = asset. Разумеется, все это группируется по ассембле.

А затем во внешнем запросе мы выбираем все из внутреннего запроса и строим разницу (что во внутреннем запросе напрямую невозможно, поскольку имена столбцов с псевдонимами там не доступны). Вуаля!

Смотрите это в действии здесь: http://sqlfiddle.com/

Answer 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;