如何基于两个SELECT查询按symbol计算买入减卖出的总股数

需要计算每只股票的持仓总股数，规则是该股票买入订单的总股数减去卖出订单的总股数。

目前我通过两个单独的查询分别获取买入、卖出的总股数：

buy = db.execute("SELECT symbol,SUM(shares) as total FROM negocios WHERE userid = ? and operation = 'buy' GROUP BY symbol");

sell = db.execute("SELECT symbol,SUM(shares) as total FROM negocios WHERE userid = ? and operation = 'sell' GROUP BY symbol")

期望得到的结果表如下：

尝试合并查询时执行了以下SQL，却报错：

SELECT ((SELECT symbol,SUM(shares) as total FROM negocios WHERE userid = ? and operation = 'buy' GROUP BY symbol) - (SELECT symbol,SUM(shares) as total FROM negocios WHERE userid = ? and operation = 'sell' GROUP BY symbol) AS diff;

错误提示：

Parse error: sub-select returns 2 columns - expected 1

数据表结构：

CREATE TABLE negocios (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, userid TEXT NOT NULL, symbol TEXT NOT NULL, operation TEXT CHECK( operation IN ('buy', 'sell')), price DECIMAL(10,2), time TIMESTAMP, shares DECIMAL(10,2), name TEXT);

方法1：条件聚合（高效简洁） #

直接在单查询内通过CASE语句区分买卖操作，对shares做正负处理后求和，一步得到持仓数：

SELECT 
    symbol,
    SUM(CASE WHEN operation = 'buy' THEN shares ELSE -shares END) AS total
FROM negocios
WHERE userid = ?
GROUP BY symbol
-- 可选：只保留持仓为正的股票，若需要显示所有股票（包括持仓为负的）可去掉此行
HAVING total > 0;

方法2：关联子查询 #

将买入、卖出的分组结果按symbol关联，处理仅买或仅卖的股票情况：

SELECT 
    -- 取存在的symbol，处理仅买/仅卖的情况
    COALESCE(b.symbol, s.symbol) AS symbol,
    -- 卖出数为空则按0计算，避免空值导致结果异常
    COALESCE(b.total, 0) - COALESCE(s.total, 0) AS total
FROM (
    SELECT symbol, SUM(shares) AS total 
    FROM negocios 
    WHERE userid = ? AND operation = 'buy' 
    GROUP BY symbol
) b
-- 全外连接确保所有买卖过的股票都被包含
FULL OUTER JOIN (
    SELECT symbol, SUM(shares) AS total 
    FROM negocios 
    WHERE userid = ? AND operation = 'sell' 
    GROUP BY symbol
) s ON b.symbol = s.symbol
-- 可选：过滤持仓为负的记录
WHERE COALESCE(b.total, 0) - COALESCE(s.total, 0) > 0;

错误原因 #

你之前的SQL错误在于：两个子查询都返回了symbol和total两列，但SQL无法直接对多列结果执行减法运算。必须先按symbol将两组数据关联，再对单数值的total做差值计算，或者用条件聚合直接在分组内完成计算。

内容的提问来源于stack exchange，提问作者valekao