php - MySQL Min value query group by second column not getting the proper value of third column

Question

我的表结构如下

vendor_id account_id 价格 代码

27 2 0.058 91

29 2 0.065 91

23 2 0.043 91

30 2 0.085 91

31 3 0.085 91

我必须得到代码应该等于 91 的最低价格，给定范围和 account_id 组之间的价格

我正在使用选择查询作为

从 tbl_input_values 中选择 MIN(price) 作为 min_price、account_id、vendor_id，其中 code='91' and price>=0 and price<=2 group by account_id

我得到的输出是

最低价格 account_id vendor_id

0.043 2 27

0.085 3 31

但应该是

最低价格 account_id vendor_id

0.043 2 23

0.085 3 31

Answer 1

试试这个查询-

SELECT
  t1.*
FROM tbl_input_values t1
JOIN (
  SELECT
    MIN(price) AS min_price,
    account_id
  FROM tbl_input_values
  WHERE code = '91' AND price >= 0 AND price <= 2
  GROUP BY account_id
  ) t2
    ON t1.account_id = t2.account_id AND t1.price = t2.min_price