MariaDB:获取两个表中两个查询结果的交集

esyap4oy  于 2022-11-08  发布在  其他
关注(0)|答案(1)|浏览(120)

所以我有两个表,都有相同的结构:
| 表格A|表格B|
| - -|- -|
|识别码|识别码|
| 一个|一个|
| 三个|2个|
| 五个|三个|
| 10个|五个|
我需要的是比较tableA.ID和tableB.ID,找出两个表中哪些ID是空闲的,并得到这些空闲ID的间隔。
要在一个表中找到空闲ID,我使用以下代码:

SELECT
    a.ID + 1 start,
    min(b.ID) - 1 end,
    min(b.ID) - a.ID - 1 gap

FROM
   tableA a,
   tableB b

WHERE      a.ID < b.ID
GROUP BY   a.ID
HAVING     start < MIN(b.ID)

它工作正常,我得到我的差距:

表格A

| 起动机|结束|间隙|
| - -|- -|- -|
| 2个|2个|一个|
| 四个|四个|一个|
| 六个|九个|四个|

表格B

| 起动机|结束|间隙|
| - -|- -|- -|
| 四个|四个|一个|
但是,我需要比较结果,并检查 both 表中哪些ID是空闲的。
| 起动机|结束|间隙|
| - -|- -|- -|
| 四个|四个|一个|
| 六个|九个|四个|
在这里我真的迷路了,我不知道该怎么做。我试过使用join,但它对我不起作用:

WHERE a.ID < b.ID AND a.ID IN (

SELECT
    c.ID+1 startID,
    min(d.ID) - 1 endID,
    min(d.ID) - c.ID - 1 gap

from
   tableB c,
   tableB d

where      c.rowid < d.rowid
)
ni65a41a

ni65a41a1#

您可以 union 这两个表以获取两者的唯一值,然后使用LEAD函数查找空闲ID,如下所示:

WITH reserved_IDs AS
(
  SELECT ID FROM tableA
  UNION 
  SELECT ID FROM tableB
  UNION SELECT 0 /* To start from 1 in case 1 value is missing from both tables*/
)
SELECT start_id, end_id, gap
FROM
(
  SELECT ID+1 AS start_id, 
         LEAD(ID) OVER (ORDER BY ID) - 1 AS end_id,
         LEAD(ID) OVER (ORDER BY ID)-ID-1 gap 
  FROM reserved_IDs
) T
WHERE gap > 0

请参见demo

相关问题