我有一個看起來像這樣的表：
| id | email | name |
| -----| -------------- |------|
| 1 | a@example.com | joe |
| 2 | b@example.com | john |
| 3 | c@example.com | joe |
| 4 | d@example.com | joe |
| 5 | d@example.com | dee |
| 6 | d@example.com | bri |
我需要在表中查詢串列“[email protected]”、“[email protected]”中具有相同名稱的電子郵件的所有行。此預期結果將回傳第 1 行和第 4 行。
uj5u.com熱心網友回復：
您可以使用子查詢或使用 INTERSECT 的 CTE 來獲取名稱，然后加入名稱以回傳所有列。
with cte1 as (
select name from my_data where email = '[email protected]'
intersect
select name from my_data where email = '[email protected]'
)
select d.*
from my_data d
join cte1 c
on d.name = c.name
where email in('[email protected]', '[email protected]');
| ID | 電子郵件 | 姓名 |
|---|---|---|
| 1 | [email protected] | 喬 |
| 4 | [email protected] | 喬 |
另一個具有相同結果的查詢：
with cte1 as (
select name, count(*) as email_count
from my_data
where email in ('[email protected]', '[email protected]')
group by name
)
select d.*
from my_data d
join cte1 c
on d.name = c.name
where email in('[email protected]', '[email protected]')
and c.email_count > 1;
uj5u.com熱心網友回復：
select id
,name
,email
from
(
select *
,case when email = '[email protected]' and count(case when email = '[email protected]' then 1 end) over(partition by name) != 0 then 1 end as f
,case when email = '[email protected]' and count(case when email = '[email protected]' then 1 end) over(partition by name) != 0 then 1 end as f2
from t
) t
where f > 0
or f2 > 0
| ID | 姓名 | 電子郵件 |
|---|---|---|
| 1 | 喬 | [email protected] |
| 4 | 喬 | [email protected] |
小提琴
uj5u.com熱心網友回復：
使用一個CTE回傳您想要的電子郵件，另一個使用聚合并回傳串列中所有電子郵件的所有名稱。
最后，使用 s 回傳的電子郵件和姓名過濾表CTE：
WITH
cte(email) AS (VALUES ('[email protected]'), ('[email protected]')),
names AS (
SELECT name
FROM tablename
WHERE email IN cte
GROUP BY name
HAVING COUNT(DISTINCT email) = (SELECT COUNT(*) FROM cte)
)
SELECT *
FROM tablename
WHERE email IN cte AND name IN names;
請注意，此查詢可針對您想要的任意數量的電子郵件進行擴展。
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/519834.html
標籤：sql数据库sqlite