Query for selecting duplicates + finding lowest value

I have 3 tables in 3 different databaes; Currently the goal here is to find the duplicates unique ID in the three databases and then find the lowest price value of the duplicates unique ID.

我在3个不同的数据库中有3个表;目前，这里的目标是在三个数据库中找到重复的唯一ID，然后找到重复唯一ID的最低价格值。

Currently I'm using a INNER JOIN to query between only 2 database... Can anyone advise on how to add the third one?

目前我正在使用INNER JOIN仅在2个数据库之间进行查询...有人可以建议如何添加第三个数据库吗？

set @a = (SELECT db1.tb1.var1 from db1.tb1
INNER JOIN db2.tb1 ON db2.tb1.var1 = db1.tb1.var1
UNION  );

Also, once I have the @a variable set to the duplicate, I wanted to grab a secondary value here.

此外，一旦我将@a变量设置为副本，我想在此处获取辅助值。

SELECT price
 FROM db1.tb1
  WHERE asin=@a
UNION ALL
SELECT price
 FROM db2.tb1
  WHERE asin=@a
UNION ALL
SELECT  price
 FROM db3.tb1
  WHERE asin=@a

However, the result I'd get would return multiple rows (obviously), How do I query only for the MIN() number from this ?

但是，我得到的结果会返回多行（显然），我如何只查询MIN（）数？

Any help is appreciated.

任何帮助表示赞赏。

Thanks,

谢谢，

2 个解决方案

#1

Put your query into a subquery, and then use MIN() in the main query.

将查询放入子查询中，然后在主查询中使用MIN（）。

SELECT MIN(price)
FROM (
    SELECT price
     FROM db1.tb1
      WHERE asin=@a
    UNION ALL
    SELECT price
     FROM db2.tb1
      WHERE asin=@a
    UNION ALL
    SELECT  price
     FROM db3.tb1
      WHERE asin=@a) AS x

#2

You can use order by and limit:

您可以使用order by和limit：

SELECT price
FROM db1.tb1
WHERE asin = @a
UNION ALL
SELECT price
FROM db2.tb1
WHERE asin = @a
UNION ALL
SELECT price
FROM db3.tb1
WHERE asin = @a
ORDER BY price
LIMIT 1;

#1