我有一個Visited包含 2 列的表：
ID | City
ID是整數，City是字串。
請注意，沒有任何列本身是鍵 - 我們可以使用相同的 ID 訪問多個城市，以及在同一個城市中使用多個不同的 ID。
給定一個特定的 ID，我想回傳表中至少訪問了輸入 ID 所到之處的一半的所有 ID（不包括他們自己）
編輯：我們只計算相同的地方。所以如果 ID 1 訪問了城市 a,b,c。ID 2 訪問了 b、c、d。ID 3 訪問了 c,d,e。
那么對于 ID=1 我們只回傳 [2]，因為在 ID1 訪問的三個城市中，ID3 只訪問了一個
uj5u.com熱心網友回復：
將visited表與特定id訪問過的城市串列進行行內，然后選擇id分組時行數至少為行數一半的id。
with u as
(select city as visitedBySpecificId from visited where id = *specificId*),
v as
(select * from visited inner join u on city = visitedBySpecificId where id <> *specificId*)
(select id from v group by id having count(*) >= (select count(*) from u)/2.0)
小提琴
uj5u.com熱心網友回復：
加入他們并比較計數。
create table suspect_tracking (id int, city varchar(30)) insert into suspect_tracking values (1, 'Brussels'), (1,'London'), (1,'Paris') , (1,'New York'), (1,'Bangkok'), (1, 'Hong Kong') , (1,'Dubai'), (1,'Singapoor'), (1,'Rome') , (1,'Macau'), (1, 'Istanbul'), (1,'Kuala Lumpur') , (1,'Dehli'), (1,'Tokyo'), (1,'Moscow') , (2,'New York'), (2,'Bangkok'), (2, 'Hong Kong') , (2,'Dubai'), (2,'Singapoor'), (2,'Rome') , (2,'Macau'), (2, 'Istanbul'), (2,'Kuala Lumpur') , (3,'Macau'), (3, 'Istanbul'), (3,'Kuala Lumpur') , (3,'Dehli'), (3,'Tokyo'), (3,'Moscow')
with cte_suspects as ( select id, city from suspect_tracking group by id, city ) , cte_prime_suspect as ( select distinct id, city from suspect_tracking where id = 1 ) , cte_prime_total as ( select id, count(city) as cities from cte_prime_suspect group by id ) select sus.id from cte_prime_suspect prime join cte_prime_total primetot on primetot.id = prime.id join cte_suspects sus on sus.city = prime.city and sus.id <> prime.id group by prime.id, sus.id, primetot.cities having count(sus.city) >= primetot.cities/2
| 身份證 | | -: | | 2 |
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/385370.html
標籤：sql