SQLite——连接两个子查询

发布于 2024-10-01 05:28:33 字数 599 浏览 4 评论 0原文

或者至少我认为它们被称为子查询(SQLite 新手和自我训练者)。我有来自同一数据库中两个表的两个 SELECT 语句。我想沿着 datesymbol 列加入这两个子查询。子查询单独工作正常,但是当我尝试JOIN时,我收到错误(语句中的错误:靠近“JOIN”:语法错误)。这是我的查询字符串:

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

谢谢!

Or at least I think they're called subqueries (newbie and self-trained in SQLite). I have two SELECT statements from two tables in the same database. I would like to join these two subqueries along the columns date and symbol. The subqueries work fine separately, but when I try to JOIN I get an error (error in statement: near "JOIN": syntax error). Here's my query string:

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

Thanks!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

风为裳 2024-10-08 05:28:33

您的 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 <> 的 WHERE 子句0 和 a.contract_settle <> 0 可能有更好的性能。

Your JOIN clause needs to be before the GROUP BY clause. Also, I know sqlite does has a few different "optional" syntaxes for joins, but the following more standard query should work:

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

If you know slightly more about the oi and contract_settle columns (like, that they can never both be negative), a WHERE clause of a.oi <> 0 AND a.contract_settle <> 0 might have better performance.

灵芸 2024-10-08 05:28:33

您需要通过 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

You need to define how you are joining by an on clause. I don't know about SQLite, but in SQL, it would be like this (may not run):

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
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文