我有价格表。我想列出在我的桌子上有相同in的产品最便宜的价格。我该怎么做呢?
Table Name : prices
userID| productsID | price | stock | maks
-------------------------------------------
1 | C120221 | 100 | 3 | 1
2 | C120221 | 200 | 5 | 5
3 | BR120221 | 500 | 7 | 3
4 | BR120221 | 600 | 9 | 0
5 | BR120221 | 700 | 11 | 2SQL
SELECT
MIN(price ) AS price ,
GROUP_CONCAT(userID) AS userID
FROM prices
WHERE price > 0
GROUP BY productsID
ORDER BY price ASC在这种情况下,我想用最便宜的价格列出用户的所有信息。在当前的查询中,我可以在productsID和价格字段中获取数据,但是我不能用这些数据在用户的userID、stock和max字段中获取数据。问题出在哪里?
发布于 2022-01-14 08:05:50
你可能想找出价格最低的一条或几条线。
SELECT
prices.*
FROM
(
SELECT
MIN(price) AS price,
productsID
FROM
prices
WHERE
price > 0
GROUP BY
productsID
) AS t1
JOIN prices ON prices.price = t1.price
AND prices.productsID = t1.productsIDhttps://stackoverflow.com/questions/70707119
复制相似问题