mysql - Group BY MySQL-使用什么列？

Question

您好，我有以下结果集。我需要找到每个 id 的 min(diff)(例如 2904 min 是 36)。如果我使用 group by id，它不会显示 36 作为 min

# id, id_contact,NAME,OPTENTION_DATE,SEND_DATE, diff
2904, 28,Version 2, 2014-11-05, 2014-12-11 16:45:41, 36
2904, 28, Version 1, 2014-09-01, 2014-12-11 16:45:41, 101
2903, 178,Version 2, 2014-11-05, 2014-12-09 16:06:39, 34
2903, 178,Version 1, 2014-09-01, 2014-12-09 16:06:39, 99

我的查询是

SELECT 
  email.id,
  a_email_contact.id_contact,
  email.subject,
  x.NAME,
  x.OPTENTION_DATE,
  email.SEND_DATE,
  min(DATEDIFF(email.SEND_DATE, x.OPTENTION_DATE)) as diff
FROM
  classification_element y,
  classification_version x ,
  email,a_email_contact
where
  x.id_project=y.id_project
  and y.id_project=11
  and y.id_company=3
  and y.ID_VERSION=x.id
  and email.SEND_DATE>x.OPTENTION_DATE
  and a_email_contact.id_email=email.id
group by email.ID
order by diff asc

我应该在哪一列上使用分组依据？

该查询的结果是

# id, id_contact, subject, NAME, OPTENTION_DATE, SEND_DATE, diff
2904, 28,Version 1, 2014-09-01, 2014-12-11 16:45:41, 36
2903, 178,Version 1, 2014-09-01, 2014-12-09 16:06:39, 34
2902, 168,Version 1, 2014-09-01, 2014-10-16 10:22:42, 45

这是错误的，因为 #2904 应该有版本 2，其中 36 作为 diff，但它显示版本 1。

Answer 1

根据您正在使用的记录量，它可能不是最佳的，但它应该有效 -

SELECT
id,
min(diff)
FROM
(
SELECT 
  email.id,
  a_email_contact.id_contact,
  email.subject,
  x.NAME,
  x.OPTENTION_DATE,
  email.SEND_DATE,
  DATEDIFF(email.SEND_DATE, x.OPTENTION_DATE) as diff
FROM
  classification_element y,
  classification_version x ,
  email,a_email_contact
where
  x.id_project=y.id_project
  and y.id_project=11
  and y.id_company=3
  and y.ID_VERSION=x.id
  and email.SEND_DATE>x.OPTENTION_DATE
  and a_email_contact.id_email=email.id
order by diff asc
) tmp
group by id