MySQL : How to know the value of a field at date (audit table)

Question

我有一个包含字段 opt_out 的 CONTACT 表。

字段 opt_out 可能有值“Y”、“N”和 NULL。

我有一个包含字段的表 CONTACT_AUDIT

date
contact_id
field_name
value_before
value_after

当我添加新联系人时，会在 CONTACT 表中添加新行，CONTACT_AUDIT 表中没有任何内容。

当我编辑联系人时，例如，如果我将 opt_out 字段值从 NULL 更改为“Y”，则 CONTACT 表中的 opt_out 字段值会更改，并且新的一行会添加到 CONTACT_AUDIT 表中

date=NOW()
contact_id=<my contact's id>
field_name='opt_out'
value_before=NULL
value_after='Y'

我需要知道在给定日期有 opt_out='Y' 的联系人。

我试过这个:

SELECT count(*) AS nb
FROM contacts c
WHERE
( -- contact is optout now and has never been modified before
    c.optout = 'Y'
    AND c.id NOT IN (SELECT DISTINCT contact_id FROM contacts_audit WHERE field_name =     'optout')
)
OR ( -- we consider contacts where the last row before date in contacts_audit is     optout = 'Y'
    c.id IN (
      SELECT ca.contact_id
      FROM contacts_audit ca
      WHERE date_created BETWEEN '2014-07-24' AND DATE_ADD( '2014-07-24', INTERVAL 1 DAY )
      AND field_name = 'optout'
      ORDER BY date_created
      LIMIT 1
    )
)

但是mysql在子查询中不支持LIMIT

所以我尝试了 HAVING :

SELECT count(*) AS nb
FROM contacts c
WHERE
( -- contact is optout now and has never been modified before
    c.optout = 'Y'
    AND c.id NOT IN (SELECT DISTINCT contact_id FROM contacts_audit WHERE field_name =     'optout')
)
OR ( -- we consider contacts where the last row before date in contacts_audit is     optout = 'Y'
    c.id IN (
      SELECT ca.contact_id
      FROM contacts_audit ca
      WHERE date_created BETWEEN '2014-07-24' AND DATE_ADD( '2014-07-24', INTERVAL 1 DAY )
      AND field_name = 'optout'
      HAVING MAX(date_created)
    )
)

查询运行，但是现在，我不知道如何知道子查询值对应的值是'Y'还是'N'。如果我添加 WHERE 子句以仅检查“Y”值，“N”值将被过滤，我将无法知道日期的最后一个值是“Y”还是“N”...

谢谢你的帮助

Answer 1

如果我正确理解您的问题，您可能需要使用联合。我现在没有 mysql 来测试它，但代码可能是这样的。告诉我这是否有帮助

select c.id, c.optout 
where c.optout = 'Y'
AND c.id NOT IN (SELECT DISTINCT contact_id FROM contacts_audit WHERE field_name =          'optout')
UNION
select c.id, c.optout where c.id IN (
  SELECT ca.contact_id
  FROM contacts_audit ca
  WHERE date_created BETWEEN '2014-07-24' AND DATE_ADD( '2014-07-24', INTERVAL 1 DAY )
  AND field_name = 'optout'
  HAVING MAX(date_created)
)