MySQL : selecting minimum value from one table and country details from another and grouping them according to country code

Question

我的数据库中有两个表:

1. venprices
+---------+---------+--------+
| concode | rate    | vendor |
+---------+---------+--------+
|    1234 | 1.23402 | a      |
|    1234 |  1.5692 | b      |
|    1234 |   1.114 | c      |
|    1234 |     1.4 | d      |
|    1122 |    1.46 | a      |
|    1122 |     1.5 | c      |
|    1122 |     1.2 | d      |
+---------+---------+--------+

2. country
+-------+---------+
| conid | conname |
+-------+---------+
|  1122 | con2    |
|  1234 | con1    |
+-------+---------+

我想要这样的输出:

+---------+---------+-------------+--------+
| concode | conname | MIN(v.rate) | vendor |
+---------+---------+-------------+--------+
|    1122 | con2    |         1.2 | d      |
|    1234 | con1    |       1.114 | c      |
+---------+---------+-------------+--------+

我正在使用查询:SELECT v.concode,c.conname, MIN(v.rate), v.vendor FROM venprices v, country c WHERE c.conid = v.concode GROUP BY c.conid;

查询给出了结果，但供应商名称错误。它不是提供与供应商相关的费率，而是总是在结果中给我供应商 a。

我已经尝试过不同的子句和子查询，但都没有成功。有人可以告诉我实际问题和产生所需结果的查询吗？

Answer 1

你可以使用 suquery

select v.concode,c.conname, v.rate, v.vendor
FROM venprices v
inner join  country c on c.conid = v.concode 
where (v.concode, v.rate) in ( select v.concode, min(v.rate)
                                  from venprices v 
                                  group by v.concode)

如果没有与'concode'相关的'conid'，则可以使用LEFT JOIN

select v.concode,c.conname, v.rate, v.vendor
FROM venprices v
left join  country c on c.conid = v.concode 
where (v.concode, v.rate) in ( select v.concode, min(v.rate)
                                  from venprices v 
                                  group by v.concode)