英文:
Select part from grouped data where all the records in grouped data satisfy any incoming criteria
问题
部分分组表：
| 部件 | 属性 | 代码 | 组 |
|---|---|---|---|
| PN1 | A | 1 | 1 |
| PN1 | B | 2 | 1 |
| PN1 | C | 3 | 1 |
| PN2 | A | 1 | 2 |
| PN2 | B | 2 | 2 |
传入标准：
| 属性 | 代码 |
|---|---|
| A | 1 |
| B | 2 |
根据结果，PN2 应该被选择，因为组 2 中的所有记录都满足传入的标准。
如果传入的标准是 (A,1)，(B,2)，(C,3)，那么两个部件都应该被选择。
英文:
part grouping table:
| part | attribute | code | Group |
|---|---|---|---|
| PN1 | A | 1 | 1 |
| PN1 | B | 2 | 1 |
| PN1 | C | 3 | 1 |
| PN2 | A | 1 | 2 |
| PN2 | B | 2 | 2 |
incoming criteria:
| attribute | Code |
|---|---|
| A | 1 |
| B | 2 |
in the result, PN2 should be selected as all the records in group 2 satisfies the incoming criteria.
have the incoming criteria been (A,1),(B,2), (C,3) both the parts should be selected.
答案1
得分: 0
以下是已翻译的内容：
你想要那些在分组表中不存在匹配项的部分。
一种方法是对条件表进行外连接，然后按部分进行聚合，看看是否有与分组表中的行数相同数量的匹配项。
select pa.part
from part_attributes pa
left join criteria c on c.attribute = pa.attribute and c.code = pa.code
group by pa.part
having count(*) = count(c.code)
order by pa.part;
英文:
You want all those parts for which does not exist a row in the grouping table that has no match in the criteria table.
One way is to outer join the criteria and then aggregate per part and see if you get as many matches as there are rows in the grouping table.
select pa.part
from part_attributes pa
left join criteria c on c.attribute = pa.attribute and c.code = pa.code
group by pa.part
having count(*) = count(c.code)
order by pa.part;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论