我必须输出表"products"中的一些产品,以及表"product_licenses"中的最低价格,这是我在该查询中唯一需要的表中的列。
然而,当我尝试使用sql函数MIN()时,我的循环只运行一次代码,得到第一个结果,然后就停止了,所以我有点迷失了方向。
这是使用min()的查询:
$mysql->query("
SELECT pd.*, min(lc.price) AS price
FROM `products` AS pd, product_licenses AS lc
WHERE pd.`status` = '1' AND lc.product_id = pd.id
ORDER BY pd.`id` ASC
$limitQuery
");
我正在使用这个功能来获取产品,但不幸的是,它的价格最高:
public function getAllProducts($start = 0, $limit = 0, $order = '`datetime` ASC') {
global $mysql;
$limitQuery = '';
if ($limit != 0) {
$limitQuery = " LIMIT $start,$limit ";
}
**// Not working if I use min() on lc.price**
$mysql->query("
SELECT pd.*, lc.price
FROM `products` AS pd, product_licenses AS lc
WHERE pd.`status` = '1' AND lc.product_id = pd.id
ORDER BY pd.`id` ASC
$limitQuery
");
if ($mysql->num_rows() == 0) {
return false;
}
$this->usersWhere = '';
$return = array();
while ($d = $mysql->fetch_array()) {
$categories = explode(',', $d['category_id']);
unset($d['category_id']);
foreach ($categories as $c) {
$c = trim($c);
if ($c != '') {
$d['category_id'][$c] = $c;
}
}
$return[$d['id']] = $d;
}
$this->foundRows = $mysql->getFoundRows();
return $return;
}
在查询中添加GROUP BY
。您当前的查询只返回一个结果,因为您正在使用聚合函数(MIN),但没有对其进行分组。
SELECT pd.col1,
pd.col2, min(lc.price) AS PRICE
FROM `products` AS pd
INNER JOIN product_licenses AS lc
ON lc.product_id = pd.id
WHERE pd.`status` = '1'
GROUP BY pd.col1, pd.col2, pd.col3
ORDER BY pd.`id` ASC
$limitQuery
PS:发布带有记录的数据库结构。社区将清楚地理解您的问题:)
查询中没有group by
子句,因此查询只返回第一行。
SELECT pd.col1, pd.col2, pd.col3, min(lc.price) AS price
FROM `products` AS pd, product_licenses AS lc
WHERE pd.`status` = '1' AND lc.product_id = pd.id
group by pd.col1, pd.col2, pd.col3
ORDER BY pd.`id` ASC
$limitQuery
您需要一个GROUP BY
,如下所示:
SELECT pd.*, min(lc.price) AS price
FROM `products` AS pd, product_licenses AS lc
WHERE pd.`status` = '1' AND lc.product_id = pd.id
GROUP BY pd.`id`
ORDER BY pd.`id` ASC
$limitQuery
请注意,对于MySQL,即使您正在从products表中选择其他列,也只需要按id列进行分组。