由网友(我要回幼儿园当学霸)分享简介：我生成以下SQL查询SELECTv.uuid, d.start_time, d.end_timeFROMvisits vINNER JOINvisit_dates d ON v.uuid = d.visit_uuidWHEREv.study_environment_site_uuid = (SELECTstu...

我生成以下SQL查询

SELECT
v.uuid, d.start_time, d.end_time
FROM
visits v
  INNER JOIN
  visit_dates d ON v.uuid = d.visit_uuid
WHERE
v.study_environment_site_uuid = (SELECT
  study_environment_site_uuid
  FROM
  visits
  WHERE
  uuid = 'e4663612-39f9-4c43-bd86-c4c5a9235b03')
AND v.uuid != 'e4663612-39f9-4c43-bd86-c4c5a9235b03'
AND d.start_time < (SELECT start_time FROM visit_dates WHERE visit_uuid = 'e4663612-39f9-4c43-bd86-c4c5a9235b03' ORDER BY start_time LIMIT 1)
ORDER BY d.start_time;

现在正在尝试将其反映到SLICK

(for {
  vSes <- visits.filter(_.uuid === uuid)
  vDate <- visitDates.filter(_.visitUuid === uuid).sortBy(_.startTime).take(1)
  (v, d) <- visits join visitDates on (_.uuid === _.visitUuid)
  if (v.uuid =!= uuid && v.studyEnvironmentSiteUuid === vSes.studyEnvironmentSiteUuid && d.startTime < vDate.startTime)
} yield (v.uuid)).result.map(_.headOption)

但这产生了错误的结果。我使用的是SLICK 3.2.1 生成以下SQL

SELECT 
    x2.`uuid`,
    x7.start_time
FROM
    `visits` x3,
    (SELECT 
        `visit_uuid` AS x4, `start_time` AS x5
    FROM
        `visit_dates`
    WHERE
        `visit_uuid` = ?
    ORDER BY `start_time`
    LIMIT 1) x6,
    `visits` x2,
    `visit_dates` x7
WHERE
    ((x2.`uuid` = x7.`visit_uuid`)
        AND ((NOT (x2.`uuid` = ?))
        AND (x7.`start_time` < x6.x5)))
        AND ((x3.`uuid` = ?)
        AND (x2.`study_environment_site_uuid` = x3.`study_environment_site_uuid`));

生成的查询不是联接，它返回多行，而不是手动查询生成的一行。

有什么想法/建议吗？

推荐答案

由于还没有答案，我将发布我对此问题的解决方法

for {
  (v, d) <- visits join visitDates on (_.uuid === _.visitUuid)
  vSes <- visits.filter(_.uuid === uuid)
  vDate <- visitDates.filter(_.visitUuid === uuid).sortBy(_.startTime).take(1)
  if v.uuid =!= uuid && v.studyEnvironmentSiteUuid === vSes.studyEnvironmentSiteUuid && d.startTime < vDate.startTime
} yield (v.uuid, d.startTime)).result.map {
  case results@(_ +: _) => Some(results.maxBy(_._2)._1)
  case _ => None
}