代码之家 › 专栏 › 技术社区 › Richard Herron

SQLite-连接两个子查询

subquery sqlite join

3

Richard Herron · 技术社区 · 15 年前

或者至少我认为它们被称为子查询(新手和自学过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))

谢谢!

2 回复 | 直到 15 年前

1

13

Andrew 15 年前

您的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

a.oi <> 0 AND a.contract_settle <> 0 可能会有更好的表现。

2

1

Bryan Denny 15 年前

您需要定义如何通过on子句连接。我不知道SQLite,但是在SQL中,它是这样的(可能不运行):

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) as 'a'
    JOIN
    (SELECT date, symbol, ret FROM crsp
    USING (date, symbol)) as 'b'
    ON a.date = b.date AND a.symbol = b.symbol
    GROUP BY date, symbol
    HAVING oi_dollar > 0