我有以下架構：
-------- ---------
|Name | Brand|
-------- ---------|
|John | Iphone|
|John | Samsung|
|Carl | Xiaomi|
|Natan | Iphone|
|Julie | Samsung|
-------- ---------
在我的結果中，我需要回傳這兩種情況下的名稱：
1-只有 Iphone
或
2 - 擁有Iphone 和三星的獨家組合；這樣，我需要這個輸出：
--------
|Name |
--------
|John |
|Natan |
--------
這是我嘗試過的，但沒有成功：
select name
from schema
where brand = 'Iphone' or
brand in
(select brand
from schema
where brand = 'Iphone' and brand = 'Samsung')
uj5u.com熱心網友回復：
您可以嘗試在 iphone olny 和 iphone-samsung 之間使用 union
select name
from schema
where brand = 'Iphone'
union
select name
from schema
where brand in ('Iphone' , 'Samsung')
group by name
having count(distinct brand) = 2
uj5u.com熱心網友回復：
編輯：如果您想要名稱only have Iphone或組合combination of Iphone and Samsung：
嘗試：
SELECT distinct name
FROM my_table
WHERE Brand IN (
SELECT Brand
FROM my_table
WHERE brand='Iphone'
GROUP BY Brand
HAVING COUNT(*) = 1
)
OR name in ( SELECT name
FROM my_table
WHERE brand in ('Iphone','Samsung')
GROUP BY name
HAVING COUNT(brand)=2
)
;
演示：https : //www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/150
如果您需要至少具有Iphone或組合Samsung and Iphone 使用的名稱：
select distinct name
from my_table
where brand = 'Iphone' or name in ( SELECT name
FROM my_table
WHERE brand in ('Iphone','Samsung')
GROUP BY name
HAVING COUNT(brand)=2 ) ;
演示：https : //www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/148
uj5u.com熱心網友回復：
這是另一個解決方案：
SELECT name, GROUP_CONCAT(DISTINCT brand ORDER BY brand) AS brands
FROM mytable
GROUP BY name
HAVING brands IN ('Iphone', 'Iphone,Samsung');
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/358089.html
上一篇：獲取最高值ID
下一篇：計算兩個時間戳之間的值差異