我有下一個結構的表：
ID | NAME | ORGANIZATION_ID | CITY_ID | COUNTRY_ID
---------------------------------------------------
1 | JOE | 1 | null | 5
2 | JACK | null | 3 | 5
3 | TOM | 1 | null | 3
現在，如果我提供 organizationId 和 cityId 作為結果，我需要將 countryId=5 作為通用值，因為對于兩個提供的值，都有帶有 countryId 的行，而對于 countryId=3，只有 Tom 在列 organizationId 中有值，并且沒有一個來自 cityId 列的值
我試過這個查詢：
select distinct(country_id) from employee where organization_id = 1 or city_id=3;
但結果我得到了 5 和 3。
我怎樣才能做到這一點，結果只有 5？
uj5u.com熱心網友回復：
SELECT country_id
FROM employee
WHERE organization_id = 1 OR city_id = 3
GROUP BY country_id
HAVING COUNT(organization_id) > 0 AND COUNT(city_id) > 0
資料庫<>小提琴
uj5u.com熱心網友回復：
我認為您正在嘗試獲取組織 = 1 的國家/地區 ID 和城市 = 3 的國家/地區 ID 的交集。
我可以想到以下查詢。他們都只回傳 country_id = 5。
SELECT
country_id
FROM
employee
WHERE
organization_id = 1
AND country_id IN (SELECT country_id FROM employee WHERE city_id=3)
SELECT
country_id
FROM
employee
WHERE
organization_id = 1
INTERSECT
SELECT
country_id
FROM
employee
WHERE
city_id=3
SELECT
e1.country_id
FROM
employee e1
INNER JOIN
employee e2
ON
e1.organization_id = 1
AND e2.city_id = 3
AND e1.country_id = e2.country_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/331489.html