加入SQL查询问题
|
我有以下查询,该查询返回产品和找到的最低售价以及该售价的数量。在我想要获得在product_price表中没有任何价格的产品之前,一切工作正常。我怎样才能让它返回产品价格和NULL的SellPrice和数量?
SELECT p.*, MIN(pp.sellPrice) as sellPrice, pp.quantity FROM `product` as p
LEFT JOIN `product_price_group` as ppg ON ppg.productId = p.`id`
LEFT JOIN `product_price` as pp ON pp.priceGroupId = ppg.`id`
WHERE p.`id` = 1 AND p.`active` = 1
具有可用价格的产品的输出:
+----+--------------+--------+--------------+--------------+-----------+----------+
| id | name | active | sortSequence | creationDate | sellPrice | quantity |
+----+--------------+--------+--------------+--------------+-----------+----------+
| 1 | product_id_1 | 1 | 1 | 1287481220 | 22.00 | 10 |
+----+--------------+--------+--------------+--------------+-----------+----------+
没有定价的产品的输出
+----+------+--------+--------------+--------------+-----------+----------+
| id | name | active | sortSequence | creationDate | sellPrice | quantity |
+----+------+--------+--------------+--------------+-----------+----------+
| NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+------+--------+--------------+--------------+-----------+----------+
所需的输出:
+----+--------------+--------+--------------+--------------+-----------+----------+
| id | name | active | sortSequence | creationDate | sellPrice | quantity |
+----+--------------+--------+--------------+--------------+-----------+----------+
| 2 | product_id_2 | 1 | 1 | 1287481220 | NULL | NULL |
+----+--------------+--------+--------------+--------------+-----------+----------+
更新资料
似乎我在选择不存在的产品!很蠢
没有找到相关结果
已邀请:
2 个回复
渴翅吮斡撤
这是你想要的吗? 更新:修订-像其他人说的那样,
,这样在这种情况下将对您没有帮助...
誓猎贰
因此,我将按以下方式调整您的查询
此外,您可以用IFNULL(field,0)包装min()和数量,以防止显示NULL,而实际为零。