我有这样的结构: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的行)
如何仅获得
mainTable
中显示的行?因此,结果是我将只有以下几行包含以下文章:
ocm10, ocm20, ocm30
与价格的联合数据?这是小提琴:http://sqlfiddle.com/#!7/87df2/4/0
顺便说一句:我知道,相同的结构不是一个好主意...在实际的应用程序表中有所不同,这只是一个示例。而且我可以有3个以上相同的表:甚至有10个具有联接的表...
最佳答案
您应该只需要将第一个表加入其他两个表即可:
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
,则最低价格将由第一个价格确定,这是唯一可用的可靠信息。关于sql - sqlite3:如何选择仅在第一个表中呈现的行(与其他表联接),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/47261754/