英文:
SQL query to get data based on second table's date column
问题
以下是翻译好的部分：
我有两个表格的预约和评估图表，正在寻找特定用户ID的预约。
但是这些预约应该具有以下标准：
- 如果评估图表中有任何预约的行，则应该获取其评估图表被修改或创建最新的预约。
- 如果评估图表中不存在预约记录，则应获取即将到来或今天的预约。
- 如果没有即将到来的预约，则应获取该用户过去的最新预约。
我使用以下代码，但结果不如预期：
SELECT
a.id,
a.user_id,
a.appt_date,
a.appt_time,
COALESCE(ec.updated_at, ec.created_at) AS latest_modified
FROM
appointments a
LEFT JOIN
evaluation_chart ec ON ec.appt_id = a.id
LEFT JOIN
(
SELECT
appt_id,
MAX(COALESCE(updated_at, created_at)) AS latest_modified
FROM
evaluation_chart
GROUP BY
appt_id
) AS le ON a.id = le.appt_id
WHERE
a.user_id IN (4, 7)
AND (
(ec.id IS NOT NULL AND COALESCE(ec.updated_at, ec.created_at) = le.latest_modified) -- 预约有评估图表，并且评估图表已被修改为最新
OR ec.id IS NULL -- 预约没有评估图表
OR (ec.id IS NOT NULL AND le.latest_modified IS NULL) -- 预约有评估图表，但同一用户的其他预约没有修改的评估图表
)
AND (
a.appt_date >= CURDATE() -- 即将到来或今天的预约
OR a.appt_date = (SELECT MAX(appt_date) FROM appointments WHERE user_id = a.user_id) -- 用户过去的最新预约
)
GROUP BY
a.id, a.user_id, a.appt_date, a.appt_time, latest_modified
HAVING
COALESCE(MAX(le.latest_modified), MAX(a.appt_date)) = COALESCE(latest_modified, a.appt_date)
我只想为每个用户获取预约。
英文:
I have two table appointments and evaluation_chart
and looking for the appointment for specific user_ids
but these appointment should have following criteria
- if evaluation_chart table has a row for any appointment then it should get appointment whose evaluation chart is modified or created latest
- If no record exists for an appointment in evaluation chart then it should upcoming OR today's appointment.
- If there is no upcoming appointment then it should get latest appoinment of the past for that user
I have using the following but not getting as expected
SELECT
a.id,
a.user_id,
a.appt_date,
a.appt_time,
COALESCE(ec.updated_at, ec.created_at) AS latest_modified
FROM
appointments a
LEFT JOIN
evaluation_chart ec ON ec.appt_id = a.id
LEFT JOIN
(
SELECT
appt_id,
MAX(COALESCE(updated_at, created_at)) AS latest_modified
FROM
evaluation_chart
GROUP BY
appt_id
) AS le ON a.id = le.appt_id
WHERE
a.user_id IN (4, 7)
AND (
(ec.id IS NOT NULL AND COALESCE(ec.updated_at, ec.created_at) = le.latest_modified) -- Appointment has an evaluation chart and it has the latest modified evaluation chart
OR ec.id IS NULL -- Appointment has no evaluation chart
OR (ec.id IS NOT NULL AND le.latest_modified IS NULL) -- Appointment has an evaluation chart, but no other appointment for the same user has a modified evaluation chart
)
AND (
a.appt_date >= CURDATE() -- Upcoming or today's appointment
OR a.appt_date = (SELECT MAX(appt_date) FROM appointments WHERE user_id = a.user_id) -- Latest appointment in the past for the user
)
GROUP BY
a.id, a.user_id, a.appt_date, a.appt_time, latest_modified
HAVING
COALESCE(MAX(le.latest_modified), MAX(a.appt_date)) = COALESCE(latest_modified, a.appt_date)
I want to get only appointment for each user.
答案1
得分: 1
这是一个需要特殊的order by的最佳-n-每组问题：
WITH cte AS (
SELECT
x,
y,
z,
ROW_NUMBER() OVER (
PARTITION BY a.user_id
ORDER BY
/* 1 */ COALESCE(c.updated_at, c.created_at) DESC,
/* 2 */ CASE WHEN a.appt_date >= CURRENT_DATE THEN a.appt_date ELSE '9999-12-31' END ASC,
/* 3 */ a.appt_date DESC
) AS rn
FROM appointments a
LEFT JOIN evaluation_chart c ON a.id = c.appt_id
)
SELECT *
FROM cte
WHERE rn = 1
AND user_id IN (4, 7)
行按以下方式编号：
- 图表日期降序
- 今天或未来的约会日期升序
- 约会日期降序
英文:
This is a greatest-n-per-group problem which needs a special order by:
WITH cte AS (
SELECT
x,
y,
z,
ROW_NUMBER() OVER (
PARTITION BY a.user_id
ORDER BY
/* 1 */ COALESCE(c.updated_at, c.created_at) DESC,
/* 2 */ CASE WHEN a.appt_date >= CURRENT_DATE THEN a.appt_date ELSE '9999-12-31' END ASC,
/* 3 */ a.appt_date DESC
) AS rn
FROM appointments a
LEFT JOIN evaluation_chart c ON a.id = c.appt_id
)
SELECT *
FROM cte
WHERE rn = 1
AND user_id IN (4, 7)
Rows are numbered by:
- chart date descending
- today or future appointment date ascending
- appointment date descending
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论