android - 查找不同用户的列总和的差异

我想找出用户的借贷交易之和的差异，并把它们列出来。
以下是我尝试过的查询：

SELECT *, (val1 - val2)
FROM (SELECT *, sum(transactionAmount) AS val1
      FROM tableTransaction
      WHERE creditedToNum = My_Number
      GROUP BY debtToNum) db
JOIN (SELECT *, sum(transactionAmount) AS val2
      FROM tableTransaction
      WHERE debtToNum = My_Number
      GROUP BY creditedToNum) Cr
WHERE db.transactionStatus = 1
  AND db.isActive = 1;

它返回0行，但实际输出应该如下
这是我的表格结构：

| Amount | Credit_to | Debit_to  |
|--------|-----------|-----------|
| 2000   | My_Number | Number_1  |
| 5000   | My_Number | Number_2  |
| 3000   | Number_1  | My_Number |
| 4000   | Number_2  | My_number |
| 2000   | My_Number | Number_2  |

我想要的是：

| Name     | Amount |
|----------|--------|
| Number_1 | 1000   |
| Number_2 | 3000   |

最佳答案

查询中没有联接条件。
与其加入，不如使用compound query将数据转换为有用的形式：

SELECT Credit_to AS Name, Amount
FROM tableTransaction
WHERE Debit_to = @My_Number
UNION ALL
SELECT Debit_to, -Amount
FROM tableTransaction
WHERE Credit_to = @My_Number;

| Name     | Amount |
|----------|--------|
| Number_1 |  3000  |
| Number_2 |  4000  |
| Number_1 | -2000  |
| Number_2 | -5000  |
| Number_2 | -2000  |

Then just group it:

SELECT Name, SUM(Amount) AS Total
FROM (SELECT Credit_to AS other, Amount
      FROM tableTransaction
      WHERE Debit_to = @My_Number
      UNION ALL
      SELECT Debit_to AS other, -Amount
      FROM tableTransaction
      WHERE Credit_to = @My_Number)
GROUP BY Name
ORDER BY Name;

|姓名合计|
|———————————---|
|数字1 1000|
|编号2-3000|