mysql - 防止 SQL 查询中出现重复

Question

我想要的行为是..

1. 查看是否有匹配项(您喜欢的 user_id 也喜欢您)
2. 按创建匹配的最新时间(最接近的日期/时间)事件排序，即，如果在您喜欢我之后我又喜欢您，那么我是最新参与的人，并且应该显示我的时间戳。
3. 如果存在 matches_appointment_time.propose_time，则首先按距现在最接近的日期/时间的顺序显示这些行。最高的行也应将商定标志设置为 1

这是我的三个表的表结构:

喜欢

id(pk) | user_id | liked_user_id | timestamp

信息

id(pk) | user_id | name | age | gender | timestamp

matches_appointment_times

id(pk) | user_id_1 | user_id_2 | proposed_time | agreed | timestamp

以下是重复行行为的示例:

这是一个使用 SQL 复制我的三个表的链接

http://rextester.com/DFUI4453

这是我的查询:

SELECT
  info.user_id,
  info.name,
  info.age,
  info.gender,
  DATE_FORMAT(likes.time, '%c/%e/%y') AS matchedTime,
  t.proposed_time AS proposedTime,
  t.agreed
FROM info
INNER JOIN likes
  ON info.user_id = likes.liked_user_id
  AND likes.user_id = ?
LEFT JOIN (SELECT
  user_id_1,
  user_id_2,
    MAX(agreed) AS agreed,
  MAX(DATE_FORMAT(proposed_time, '%b %d, %Y @ %h:%i %p')) AS proposed_time
FROM matches_appointment_times
WHERE proposed_time > NOW()
GROUP BY user_id_1,
         user_id_2) t
  ON (info.user_id = t.user_id_1 OR info.user_id = t.user_id_2)
  AND (t.user_id_1 = ? OR t.user_id_2 = ?)
WHERE likes.liked_user_id IN (SELECT
  user_id
FROM likes
WHERE likes.liked_user_id = ?)
ORDER BY 
CASE
  WHEN t.proposed_time IS NOT NULL AND t.agreed = 1 THEN t.proposed_time
END DESC,
CASE
  WHEN t.proposed_time IS NOT NULL AND t.agreed = 0 THEN t.proposed_time
END DESC,
CASE
  WHEN t.proposed_time IS NULL THEN likes.time
END DESC

Answer 1

Please use Distinct in Subquery:


SELECT  
  info.user_id,
  info.name,
  info.age,
  info.gender,
  DATE_FORMAT(likes.time, '%c/%e/%y') AS matchedTime,
  t.proposed_time AS proposedTime,
  t.agreed
FROM info
INNER JOIN likes
  ON info.user_id = likes.liked_user_id
  AND likes.user_id = ?
LEFT JOIN (SELECT 
Distinct
  user_id_1,
  user_id_2,
  MAX(agreed) AS agreed,
  MAX(DATE_FORMAT(proposed_time, '%b %d, %Y @ %h:%i %p')) AS proposed_time
FROM matches_appointment_times
WHERE proposed_time > NOW()
GROUP BY user_id_1,
         user_id_2) t
  ON (info.user_id = t.user_id_1 OR info.user_id = t.user_id_2)
  AND (t.user_id_1 = ? OR t.user_id_2 = ?)
WHERE likes.liked_user_id IN (SELECT
  user_id
FROM likes
WHERE likes.liked_user_id = ?)
ORDER BY 
CASE
  WHEN t.proposed_time IS NOT NULL AND t.agreed = 1 THEN t.proposed_time
END DESC,
CASE
  WHEN t.proposed_time IS NOT NULL AND t.agreed = 0 THEN t.proposed_time
END DESC,
CASE
  WHEN t.proposed_time IS NULL THEN likes.time
END DESC