join - SQLite——连接两个子查询

Question

或者至少我认为它们被称为子查询(新手和在 SQLite 中自学的)。我有来自同一个数据库中两个表的两个 SELECT 语句。我想沿着 date 和 symbol 列加入这两个子查询。子查询单独工作正常，但是当我尝试 JOIN 时出现错误( error in statement: near "JOIN": syntax error )。这是我的查询字符串:

SELECT date, symbol, SUM(oi*contract_settle) AS oi_dollar
    FROM (SELECT date, symbol, oi, contract_settle
            FROM ann
            UNION
            SELECT date, symbol, oi, contract_settle
            FROM qtr) 
    GROUP BY date, symbol
    HAVING oi_dollar > 0
    JOIN
    (SELECT date, symbol, ret FROM crsp
    USING (date, symbol))

谢谢!

Answer 1

您的 JOIN 子句需要在 GROUP BY 子句之前。另外，我知道 sqlite 确实有一些不同的“可选”连接语法，但以下更标准的查询应该可以工作:

SELECT a.date, a.symbol, SUM(a.oi * a.contract_settle) AS oi_dollar
FROM (SELECT date, symbol, oi, contract_settle
        FROM ann
        UNION
        SELECT date, symbol, oi, contract_settle
        FROM qtr) a
INNER JOIN crsp c ON a.date = c.date AND a.symbol = c.symbol
WHERE a.oi * a.contract_settle > 0
GROUP BY a.date, a.symbol

如果您对 oi 和 contract_settle 列有更多的了解(例如，它们永远不会都是负数)，那么 a.oi <> 0 AND a.contract_settle <> 0 的 WHERE 子句可能具有更好的性能。