时间戳

首页 > 解决方案 > SQL获取共享外键的所有行

sql - SQL获取共享外键的所有行

问题描述

我希望找到所有至少有一个带有一些（非特定）外键的兄弟的行。

mytable
id |fk_id    
---|--------
1  |100 
2  |200
3  |200
4  |300 
5  |300
6  |300

我的查询应该返回第 2 到第 6 行，但不是第 1 行，因为它是单独的。

我想出了一个可行的解决方案，它使用了 2 个子查询，这似乎太多了。（在 20k+ 行上运行几秒钟，这意味着至少 O(n^2)

SELECT * from mytable 
WHERE fk_id IN 
  (SELECT fk_id FROM
    (SELECT fk_id, SUM(fk_id) as mycnt from mytable GROUP BY fk_id)
  WHERE mycnt >= 2)

什么是更快的解决方案？

常规编程非 SQL 解决方案是仅按 fk_id 排序，然后摆脱单项，这将是 O(nlogn) 用于通用排序加上 O(n) 只是迭代一次，所以 O(nlogn)

使用 SQLite，但其他 SQL 方言也可以。

标签： sqlsqlitegroup-by

解决方案

存在：

SELECT m.* FROM mytable m
WHERE EXISTS (
  SELECT 1 FROM mytable
  WHERE id <> m.id AND fk_id = m.fk_id
)

请参阅演示。
或者使用 COUNT() 窗口函数：

SELECT m.id, m.fk_id 
FROM (
  SELECT *, COUNT(id) OVER (PARTITION BY fk_id) counter  
  FROM mytable
) m
WHERE m.counter > 1

请参阅演示。
结果：

| id  | fk_id |
| --- | ----- |
| 2   | 200   |
| 3   | 200   |
| 4   | 300   |
| 5   | 300   |
| 6   | 300   |

推荐阅读