sqlite3:如何选择仅在第一个表中显示的行(与其他表连接) [英] sqlite3: how to select row's presented only in first table (with other table join)

问题描述

我有这样的结构:

mainTable

article | brand      | price
ocm10   | someBrand1 | 100
ocm20   | someBrand1 | 200
ocm30   | someBrand2 | 300

secondTable

article | brand      | price
ocm30   | someBrand1 | 320
ocm10   | someBrand1 | 120

thirdTable

article | brand      | price
ocm20   | someBrand1 | 230
ocm40   | someBrand1 | 430
ocm50   | someBrand3 | 530

现在我有这样的查询:

SELECT article,
       mainTable.brand AS priceBrand,
       mainTable.price AS priceTableMain,
       secondTable.price AS priceTableSecond,
       thirdTable.price AS priceTableThird,
       min(ifnull(mainTable.price, 'inf'),
           ifnull(secondTable.price, 'inf'),
           ifnull(thirdTable.price, 'inf')) AS minPrice
FROM (SELECT article FROM mainTable
      UNION
      SELECT article FROM secondTable
      UNION
      SELECT article FROM thirdTable)
LEFT JOIN mainTable USING (article)
LEFT JOIN secondTable USING (article)
LEFT JOIN thirdTable USING (article);

我想看到的结果:

article | brand      | priceMainTable | priceSecond | priceThird | minPrice
ocm10   | someBrand1 | 100            |   null      | null       | 100
ocm20   | someBrand1 | 200            |   null      | 230        | 200
ocm30   | someBrand2 | 300            |   320       | null       | 300

(所以，没有像 ocm40、ocm50 这样的行，它们没有出现在第一个表中)

(so, without rows like ocm40, ocm50 which aren't presented in first table)

如何只获取行，呈现在 mainTable 中?

所以结果我只会有文章的行:ocm10, ocm20, ocm30 与价格的联合数据?

so in result i will have only rows with articles: ocm10, ocm20, ocm30 with joined data of prices?

这里是小提琴:http://sqlfiddle.com/#!7/87df2/4/0

顺便说一句:我知道，相同的结构不是一个好主意……在实际应用程序表中是不同的，这只是一个示例.而且我可以有超过 3 个相同的表:甚至 10 个带有连接等...

btw: I know, that the same structure is not a good idea... in real app tables are different, this is only a sample. And I can have more than 3 same tables: even 10 with joins etc...

推荐答案

您应该能够将第一个表与其他两个表保持连接:

You should be able to just left join the first table to the other two:

SELECT
    t1.article,
    t1.brand,
    t1.price AS priceMainTable,
    t2.price AS priceSecond,
    t3.price AS priceThird,
    MIN(t1.price,
        COALESCE(t2.price, t1.price),
        COALESCE(t3.price, t1.price)) AS minPrice
FROM mainTable t1
LEFT JOIN secondTable t2
    ON t1.article = t2.article
LEFT JOIN thirdTable t3
    ON t1.article = t3.article;

注意这里我们使用了 MIN 的标量版本，它接受多个参数.COALESCE 逻辑是第二个和第三个价格推迟"回到第一个价格应该是 NULL.因此，例如，如果第二个和第三个价格都是 NULL，那么最低价格将由第一个价格确定，这是唯一可靠的可用信息.

Note here that we use the scalar version of MIN which accepts multiple arguments. The COALESCE logic is that the second and third price "defer" back to the first price should either be NULL. So if, for example, both the second and third price were NULL, then the minimum price would be determined by the first price, which is the only reliable piece of information available.

这篇关于sqlite3:如何选择仅在第一个表中显示的行(与其他表连接)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！