sqlite 如何从两个不同的SELECT中减去两行？

luaexgnf 于 2022-11-15 发布在 SQLite

关注(0)|答案(1)|浏览(152)

我试着从卖单和买单中减去每个符号的股票总数，这样我就可以得到拥有的股票总数。

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")

我想要一个这样的表，其中的值已经被减去：
Symbol|合计

CSCO|25
GOOG|6
NFLX|32
我在试着：

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;

但我收到了一个错误：
分析错误：子选择返回%2列-应为%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);

sqlite

来源：https://stackoverflow.com/questions/73547576/how-to-subtract-two-rows-from-two-different-selects

1条答案

按热度按时间

bbmckpt71#

如果您使用条件聚合，则可以使用单个查询完成此操作：

userid = 100 #variable that stores the user's id 
sql = """
SELECT symbol, 
       SUM(shares * CASE operation WHEN 'buy' THEN 1 WHEN 'sell' THEN -1 END) AS total 
FROM negocios 
WHERE userid = ? 
GROUP BY symbol
"""
result = db.execute(sql, (userid,))

赞(0）回复(0）举报 2022-11-15

我来回答

sqlite 如何从两个不同的SELECT中减去两行？

1条答案

相关问题

热门标签

最新问答