需要找到最低价格的产品详细信息

Question

 tbl_product:                             
+------+------+------------+-------+     
| id   | code | name  |brand|origin|
+------+------+------------+-------+
|    1 | 1001 | apple | X   | a    |
|    2 | 1002 | mango | V   | b    |
|    3 | 1003 | banana| Z   | a    |
+------+------+------------+-------+
tbl_product_price:
+------+------+------+
| id   | code | price|
+------+------+------+
|    1 | 1001 |  250 |
|    2 | 1001 |  220 | 
|    3 | 1002 |  175 |
|    4 | 1002 |  180 |
|    5 | 1003 |  170 |
|    6 | 1003 |  190 |  
+------+------+------+

我有一个搜索框，如果我选择任何专业人员

SELECT a.id，a.pro_code，a.pro_unit，MIN(b.pro_price) FROM tab_product a INNER JOIN tab_product_price b WHERE pro_code like('“.$search . "%') ORDER BY pro_code LIMIT 5”但在搜索框中不显示pro_code。

请使用此代码！！

Answer 1

首先要做的是JOIN这两个表，因为产品的Price属于tbl_product_price。加入后，您需要使用聚合函数MIN()和GROUP BY来获取每组中的最低价格。

SELECT  a.id, a.code, a.name, MIN(b.Price) minimumPrice
FROM    tbl_product a
        INNER JOIN tbl_product_price b
            ON a.code = b.code
GROUP   BY a.id, a.code, a.name

• SQLFiddle Demo

要进一步了解joins，请访问下面的链接：

• Visual Representation of SQL Joins

输出

╔════╦══════╦════════╦══════════════╗
║ ID ║ CODE ║  NAME  ║ MINIMUMPRICE ║
╠════╬══════╬════════╬══════════════╣
║  1 ║ 1001 ║ apple  ║          220 ║
║  2 ║ 1002 ║ mango  ║          175 ║
║  3 ║ 1003 ║ banana ║          170 ║
╚════╩══════╩════════╩══════════════╝

Answer 2

查看MySQL JOIN、MIN()和IFNULL()函数：

SELECT
    `id`, `a`.`code`, `name`, IFNULL(MIN(`price`), 'no min price') as `minimum_price`
FROM
    `tbl_product` `a` LEFT JOIN `tbl_product_price` `b` ON `a`.`code` = `b`.`code`
GROUP BY
    `id`, `a`.`code`, `name`;

如果您的产品没有指定价格，这也是可行的。

Answer 3

SELECT  tbl_product.id, tbl_product.code, tbl_product.name, MIN(tbl_product_price.Price) minimumPrice
FROM    tbl_product,tbl_product_price
        Where tbl_product.code = tbl_product_price.code
GROUP BY  tbl_product.code

SQL FIDDLE