英文:
is there a way to select only records with ingredient numbers of 3 or more
问题
我只想选择具有3个或更多成分的产品，结果应该如下所示：
| 产品 | 成分号 |
|---|---|
| C | 1 |
| C | 2 |
| C | 3 |
| D | 1 |
| D | 2 |
| D | 3 |
| D | 4 |
希望这样清楚明了！
我尝试了使用Row_Number partition by product，但无法使其工作。
我尝试了以下代码，它可以独立工作：
( SELECT product FROM Ingreds
GROUP BY product HAVING COUNT(*) > 3 ) y
ON y.product = x.product))
但是，当我将其用在 WHERE 子句中时：
select product,ingredno
from Ingreds
where exists ( SELECT product FROM Ingreds
GROUP BY product HAVING COUNT(*) > 3 ) y
ON y.product = x.product))
我仍然会得到所有的记录，而不是具有超过3个成分的记录。
英文:
I have a table of compound ingredients. depending on the product each product may have more than one ingredient number:
| Product | Ingred No |
|---|---|
| A | 1 |
| B | 1 |
| B | 2 |
| C | 1 |
| C | 2 |
| C | 3 |
| D | 1 |
| D | 2 |
| D | 3 |
| D | 4 |
I only want to select products with 3 or more ingredients so the result would be:
| Product | IngredNo |
|---|---|
| C | 1 |
| C | 2 |
| C | 3 |
| D | 1 |
| D | 2 |
| D | 3 |
| D | 4 |
Hope this makes sense!
i did try Row_Number partition by product, but i cannot get it to work.
i tried the following and it works by itself:
( SELECT product FROM Ingreds
GROUP BY product HAVING COUNT(0) > 3 ) y
ON y.product = x.product))
But when i use it in a where clause :
select product,ingredno
from Ingreds
where exists ( SELECT product FROM Ingreds
GROUP BY product HAVING COUNT(0) > 3 ) y
ON y.product = x.product))
i still get all the records, not the one with more than 3 ingredients
答案1
得分: 1
SELECT p.*
FROM product p
INNER JOIN (
(
SELECT product
FROM Ingreds
GROUP BY product
HAVING COUNT(0) >= 3
) g ON g.product = p.product
英文:
SELECT p.*
FROM product p
INNER JOIN (
(
SELECT product
FROM Ingreds
GROUP BY product
HAVING COUNT(0) >= 3
) g ON g.product = p.product
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论