sqlite - SQLite复杂查询内部联接和求和

Question

我对Sqlite完全陌生，我有一个具有以下结构的帐户表:

创建表帐户(ID整数主键，accountName文本不为空，货币文本不为空，accountImage文本不为空)；

并具有以下结构的记录表:

创建表记录(id整数主键，数量整数，类别文本不为null，描述文本，日期日期时间，recordImage文本不为null，accountId整数，外键(accountId)引用accounts(id))；

Iam尝试执行此查询，以返回类别等于total_income的收入与类别等于total_expense的费用的记录的总金额，这是查询:

SELECT *, (
    SELECT SUM(records.amount)  AS total_income FROM records
    WHERE records.category = "income", records.date BETWEEN DATE("now", "start of month")  AND DATE("now", "start of month", "+1 month", "-1 day")
), (
    SELECT SUM(records.amount) AS total_expense FROM records
    WHERE records.category = "expense", 
records.date BETWEEN DATE("now", "start of month")  AND DATE("now", "start of month", "+1 month", "-1 day")
    ) 
    FROM accounts INNER JOIN records ON accounts.id = records.accountId;

但是我收到此错误:

错误:“，”附近:语法错误

请帮我。

Answer 1

您在2个WHERE子句中使用逗号而不是AND或OR。

而不是(请参阅注释 -- <<<<<<<<<< ... ):-

SELECT *, (
    SELECT SUM(records.amount)  AS total_income FROM records
    WHERE records.category = "income", -- <<<<<<<<<< comma not AND or OR
       records.date BETWEEN DATE("now", "start of month")  AND DATE("now", "start of month", "+1 month", "-1 day")
), (
    SELECT SUM(records.amount) AS total_expense FROM records
    WHERE records.category = "expense", -- <<<<<<<<<< comma not AND or OR
records.date BETWEEN DATE("now", "start of month")  AND DATE("now", "start of month", "+1 month", "-1 day")
    ) 
    FROM accounts INNER JOIN records ON accounts.id = records.accountId;

尽管不是语法错误，但您可能希望将AS子句放在子查询之外

尝试:-

SELECT *, (
    SELECT SUM(records.amount)  FROM records -- <<<<<<<<<<< remove the AS clause
    WHERE records.category = "income" AND -- <<<<<<<<<< AND instead of comma
       records.date BETWEEN DATE("now", "start of month")  AND DATE("now", "start of month", "+1 month", "-1 day")
) AS total_expense, -- <<<<<<<<<< Moved to name the column in the result
(
    SELECT SUM(records.amount) FROM records -- <<<<<<<<<<< remove the AS clause
    WHERE records.category = "expense" AND -- <<<<<<<<<< AND instead of comma
records.date BETWEEN DATE("now", "start of month")  AND DATE("now", "start of month", "+1 month", "-1 day")
) AS total_expense -- <<<<<<<<<< Moved to name the column in the result
FROM accounts INNER JOIN records ON accounts.id = records.accountId;

例如

请注意，每行将具有总和，因此它不是运行中的累积，而是每个结果将具有相同的总和值。

附加评论:-

as you say I am getting "repeated rows" for every record associated with an account and not an accumulation. I will pass this info to a recyclerView Adapter so it would be a problem. Now I end the query with FROM accounts and I get just one row per account with their corresponding total.

我相信以下可能是您想要的，或者可能是您想要的基础:

-- Create testing schema and data
DROP TABLE IF EXISTS accounts;
DROP TABLE If EXISTS records;
CREATE TABLE IF NOT EXISTS records (amount INTEGER, category TEXT, date TEXT, accountId INTEGER);
CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, accountname TEXT);
INSERT INTO accounts (accountname) VALUES('account1'),('account2');
INSERT INTO records (amount, category, date, accountId) VALUES 

    -- account 1
    (300,'income','2018-12-31',1),
    (25,'expense','2018-12-31',1),
    (100,'income','2019-01-01',1),
    (30,'expense','2019-01-01',1),
    (40,'expense','2019-01-02',1),
    (200,'income','2019,01-02',1),

    -- account 2
    (600,'income','2018-12-31',2),
    (325,'expense','2018-12-31',2),
    (700,'income','2019-01-01',2),
    (330,'expense','2019-01-01',2),
    (440,'expense','2019-01-02',2),
    (5200,'income','2019,01-02',2)
;

/* The query
        assumes that the records table is not a WITHOUT ROWID table
        and that the id reflects the insertion order which reflects
        the order in which transactions are considered to have happened
*/
SELECT *, 
    r.rowid AS ambiguos_recordsid, -- for demo/testing
    date('now','start of month') AS startdate, -- for demo/testing
    date('now','start of month', '+1 month', '-1 day') AS enddate, -- for demo/testing
    r.date BETWEEN date('now','start of month') AND date('now','start of month', '+1 month', '-1 day') AS resultdate, -- for demo/testing
    (
        SELECT sum(amount) 
        FROM records
        WHERE 
            records.rowid <= r.rowid AND category = 'income'
            AND (date BETWEEN date('now','start of month') AND date('now','start of month', '+1 month', '-1 day'))
            AND accountid = r.accountid
    ) AS rolling_income,
    (
        SELECT sum(amount)
        FROM records
        WHERE 
            records.rowid <= r.rowid AND category = 'expense'
            AND (date BETWEEN date('now','start of month') AND date('now','start of month', '+1 month', '-1 day'))
            AND accountid = r.accountid
    ) AS rolling_expense
FROM records AS r JOIN accounts on accountId = accounts.id
WHERE r.date BETWEEN date('now','start of month') AND date('now','start of month', '+1 month', '-1 day')
ORDER BY r.accountid, r.rowid
;

使用上面的方法，结果是:-