我想以這樣的方式過濾表格，如果城市共享相同的代碼不應該被選中請告知如何實作這一點。
| 代碼 | 城市 |
|---|---|
| 3 | MYS |
| 3 | 紐約市 |
| 3 | STA |
| 4 | MYS |
| 4 | MYS |
| 5 | MYS |
期待結果
| 代碼 | 城市 |
|---|---|
| 4 | MYS |
| 5 | MYS |
uj5u.com熱心網友回復：
在 SQL Server 中，使用 anapply operator允許一種有效形式的相關子查詢，也可以在 where 子句中參考。在這里，我們可以計算每個代碼的不同城市值的數量，如果這是 1 則僅輸出這些行并使用select distinct洗掉任何重復的結果行：
with a as (
select 3 as code, 'MYS' as City
union all
select 3, 'NYR'
union all
select 3, 'STA'
union all
select 4, 'MYS'
union all
select 4, 'MYS'
union all
select 5, 'MYS'
)
select distinct
a.code
, a.city
from a
cross apply (
select count(distinct c.city)
from a as c
where a.code = c.code
) as ia (c_count)
where ia.c_count = 1
結果
------ ------
| code | city |
------ ------
| 4 | MYS |
| 5 | MYS |
------ ------
db<>在這里擺弄
uj5u.com熱心網友回復：
如果這是你想要的，你能試試嗎
with a as (
select 3 as code, 'MYS' as City
union all
select 3, 'NYR'
union all
select 3, 'STA'
union all
select 4, 'MYS'
union all
select 4, 'MYS'
union all
select 5, 'MYS'
)
select *
from (
select distinct code, city
from a
) b
group by code
having count(*) = 1;
這是針對 Mariadb 的，但邏輯是distinct,count(*)和group by. 這是一種方法
對于 SQL Server 試試這個
Select distinct * from a where code in (select code
from (
select distinct code, city
from a
) b
group by code
having count(*) = 1);
小提琴
uj5u.com熱心網友回復：
你可以簡單地group by Code和count(distinct City) = 1. City您可以使用min或max顯示的結果中只有一個唯一的
select Code, min(City) as City
from yourtable
group by Code
having count(distinct City) = 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/492338.html