sqlite 如何使用INNER JOIN根据每个customer_id和currency_id的日期和标识获取余额

jobtbby3 于 2022-12-13 发布在 SQLite

关注(0)|答案(2)|浏览(129)

我有一个名为transaction_table的表：

CREATE TABLE transaction_table
  (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT,
    debit REAL,
    credit REAL,
    curr_id INTEGER,
    cus_id INTEGER,
    FOREIGN KEY (curr_id) REFERENCES currencies(_id) ON DELETE CASCADE,
    FOREIGN KEY (cus_id) REFERENCES customers(_id) ON DELETE CASCADE
  )

并假定其中有这样的数据：

_id  date                       debit    credit   curr_id    cus_id
-------------------------------------------------------------------
1   2022-12-08T00:00:00.000     10.0       0.0         1         1
2   2022-12-07T00:00:00.000      0.0      20.0         1         1
3   2022-12-06T00:00:00.000      0.0      30.0         1         1
4   2022-12-07T00:00:00.000     40.0       0.0         1         1
5   2022-12-08T00:00:00.000    100.0       0.0         1         1

因此，我只做了SELECT语句，该语句将根据date和_id获得余额，但余额是错误的：

SELECT  t1._id,
    t1.date ,
    t1.description ,
    t1.debit ,
    t1.credit,
    SUM(t2.debit - t2.credit) as blnc,
    t1.curr_id,
    t1.cus_id
FROM transaction_table t1 INNER JOIN transaction_table t2
ON t2.curr_id = t1.curr_id AND t2.cus_id = t1.cus_id AND t2._id <= t1._id AND t2.date <= t1.date
GROUP BY t1._id
ORDER BY t1.date DESC, t1._id DESC;

结果是：

_id    date                        debit   credit   balance   curr_id   cus_id
  -----------------------------------------------------------------------------
   5    2022-12-08T00:00:00.000     100.0      0.0     100.0         1        1
   1    2022-12-08T00:00:00.000      10.0      0.0      10.0         1        1
   4    2022-12-07T00:00:00.000      40.0      0.0     -10.0         1        1
   2    2022-12-07T00:00:00.000       0.0     20.0     -20.0         1        1
   3    2022-12-06T00:00:00.000       0.0     30.0     -30.0         1        1

结果是错误的，应该是这样的：

_id    date                        debit   credit   balance   curr_id   cus_id
  -----------------------------------------------------------------------------
   5    2022-12-08T00:00:00.000     100.0      0.0     100.0         1        1
   1    2022-12-08T00:00:00.000      10.0      0.0       0.0         1        1
   4    2022-12-07T00:00:00.000      40.0      0.0     -10.0         1        1
   2    2022-12-07T00:00:00.000       0.0     20.0     -50.0         1        1
   3    2022-12-06T00:00:00.000       0.0     30.0     -30.0         1        1

我可以通过以下代码实现：

SELECT _id,date, description, debit, credit,
   SUM(debit - credit) OVER (PARTITION BY curr_id, cus_id ORDER BY date, _id) blnc,
   curr_id, cus_id 
FROM transaction_table
order by date desc, _id desc;

但我不需要它，因为它不工作在旧的android.我只需要使用INNER JOIN .

sqlite

来源：https://stackoverflow.com/questions/74732442/how-to-get-balance-according-date-and-id-for-every-customer-id-and-currency-id-u

2条答案

按热度按时间

qfe3c7zg1#

ON子句中有关日期和id的条件需要更正：

SELECT t1._id,
       t1.date,
       t1.description,
       t1.debit,
       t1.credit,
       SUM(t2.debit - t2.credit) AS blnc,
       t1.curr_id,
       t1.cus_id
FROM transaction_table t1 INNER JOIN transaction_table t2
ON t2.curr_id = t1.curr_id AND t2.cus_id = t1.cus_id 
AND (t2.date < t1.date OR (t2.date = t1.date AND t2._id <= t1._id))
GROUP BY t1._id
ORDER BY t1.date DESC, t1._id DESC;

请参阅demo。

赞(0）回复(0）举报 2022-12-13

piok6c0g2#

可以用相关子查询替换SUM()分析函数调用：

SELECT _id, date, description, debit, credit,
       (SELECT SUM(t2.debit - t2.credit)
        FROM transaction_table t2
        WHERE t2.curr_id = t1.curr_id AND
              t2.cus_id = t1.cus_id AND
              (t2.date <= t1.date OR
               t2.date = t1.date AND t2._id <= t1._id)) AS blnc,
       curr_id, cus_id 
FROM transaction_table t1
ORDER BY date DESC, _id DESC;

赞(0）回复(0）举报 2022-12-13

我来回答

sqlite 如何使用INNER JOIN根据每个customer_id和currency_id的日期和标识获取余额

2条答案

相关问题

热门标签

最新问答