本文介紹了在子集中具有特定值的父級(jí)的 SQL 查詢的處理方法，對(duì)大家解決問題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
我試圖只返回父級(jí)，其中子級(jí)集包含多個(gè)特定記錄.鑒于此表:
I am trying to return just the parent where the set of children contain multiple specific records. Given this table:
Product State
------- -----
111 AZ
111 CA
111 UT
222 AZ
222 WA
333 CA
我想找到同時(shí)具有 AZ 子記錄和 CA 子記錄的 Product 列表，即使它還有其他記錄.哪個(gè)會(huì)返回...
I want find the list of Product that have both a AZ child record and a CA child record, even if it also has other records. Which would return ...
Product
-------
111
推薦答案
select product
from table
where state in ('az','ca')
group by product
having count(distinct(state)) = 2
select distinct product
from table
where state = 'az'
INTERSECT
select distinct product
from table
where state = 'ca'
select distinct t1.product
from table t1
join table t2
on t1.product = t2.product
and t1.state = 'az'
and t2.state = 'ca'
最后一個(gè)可能是最有效的.
The last is probably going to be the most efficient.
這篇關(guān)于在子集中具有特定值的父級(jí)的 SQL 查詢的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請(qǐng)聯(lián)系我們刪除處理，感謝您的支持！