时间戳

首页 > 解决方案 > 在多行满足连接条件的情况下随机连接

sql - 在多行满足连接条件的情况下随机连接

问题描述

这是我的 SQLite 数据的一个非常简化的版本：

CREATE Table Questionnaires
(
   questionnaire_id INTEGER NOT NULL,
   seconds INTEGER NOT NULL,
   measure CHAR(4) NOT NULL,
   score INTEGER NOT NULL,
   PRIMARY KEY (questionnaire_id)
)

INSERT INTO Questionnaires (seconds, measure, score) VALUES (5, 'PHQ9', 10), (5, 'GAD7', 8), (20, 'PHQ9', 5)

CREATE Table Events
(
   event_id INTEGER NOT NULL,
   seconds INTEGER NOT NULL,
   PRIMARY KEY (event_id)
)

INSERT INTO Events (seconds) VALUES (5), (5), (10), (15), (20)

我想加入这两个表seconds。我目前的尝试没有给出我想要的结果：

SELECT
    Events.event_id,
    Questionnaires.questionnaire_id,
    Questionnaires.seconds,
    Questionnaires.measure,
    Questionnaires.score
FROM Questionnaires
LEFT OUTER JOIN Events
ON Events.seconds = Questionnaires.seconds

有两个问卷seconds == 5和两个事件，seconds == 5所以LEFT OUTER JOIN给了我四个结果行，即

  1. 问卷 1 与事件 1 相结合，
  2. 问卷 1 与事件 2 相结合，
  3. 问卷 2 与事件 1 相结合，以及
  4. 问卷 2 与事件 2 相结合。

但我想要的是要么

我不介意我得到哪一个。

我可以看到如何在过程语言中做到这一点，但我看不到如何在 SQL 中以集合论的方式做到这一点。

有任何想法吗？

（注意在我的真实数据集中重复匹配事件很少见，这就是为什么我没有发现我的愚蠢错误。）

标签： sqlsqlite

解决方案

在加入之前，您可以在两个表上使用row_number()窗口函数：

select
    e.event_id,
    q.questionnaire_id,
    q.seconds,
    q.measure,
    q.score
from (
  select *, row_number() over (partition by seconds order by questionnaire_id) rn
  from Questionnaires
) q left join (
  select *, row_number() over (partition by seconds order by event_id) rn
  from Events
) e on e.seconds = q.seconds and e.rn = q.rn

请参阅演示。
或者没有窗口函数：

SELECT
    e.event_id,
    q.questionnaire_id,
    q.seconds,
    q.measure,
    q.score
FROM Questionnaires q LEFT OUTER JOIN Events e
ON e.seconds = q.seconds
AND (select count(*) from Questionnaires where seconds = q.seconds and questionnaire_id < q.questionnaire_id) =
    (select count(*) from Events where seconds = e.seconds and event_id < e.event_id);

请参阅演示。

结果：

| event_id | questionnaire_id | seconds | measure | score |
| -------- | ---------------- | ------- | ------- | ----- |
| 1        | 1                | 5       | PHQ9    | 10    |
| 2        | 2                | 5       | GAD7    | 8     |
| 5        | 3                | 20      | PHQ9    | 5     |

推荐阅读