mysql - 查询一个报告的两个表(高级)

Question

我在使用高级 SQL 查询时遇到了一些问题，而且我已经有很长时间没有使用 SQL 数据库了。我们使用 MySQL。

背景:

我们将使用两个表:

“交易表”

表:expire_history

+---------------+-----------------------------+------+-----+-------------------+-------+    
| Field         | Type                        | Null | Key | Default           | Extra |
+---------------+-----------------------------+------+-----+-------------------+-------+
| m_id          | int(11)                     | NO   | PRI | 0                 |       | 
| m_a_ordinal   | int(11)                     | NO   | PRI | 0                 |       | 
| a_expired_date| datetime                    | NO   | PRI |                   |       | 
| a_state       | enum('EXPIRED','UNEXPIRED') | YES  |     | NULL              |       | 
| t_note        | text                        | YES  |     | NULL              |       | 
| t_updated_by  | varchar(40)                 | NO   |     |                   |       | 
| t_last_update | timestamp                   | NO   |     | CURRENT_TIMESTAMP |       | 
+---------------+-----------------------------+------+-----+-------------------+-------+

“信息表”

表:信息

+---------------------+---------------+------+-----+---------------------+-------+
| Field               | Type          | Null | Key | Default             | Extra |
+---------------------+---------------+------+-----+---------------------+-------+
| m_id                | int(11)       | NO   | PRI | 0                   |       | 
| m_a_ordinal         | int(11)       | NO   | PRI | 0                   |       | 
| a_type              | varchar(15)   | YES  | MUL | NULL                |       | 
| a_class             | varchar(15)   | YES  | MUL | NULL                |       | 
| a_state             | varchar(15)   | YES  | MUL | NULL                |       | 
| a_publish_date      | datetime      | YES  |     | NULL                |       | 
| a_expire_date       | date          | YES  |     | NULL                |       | 
| a_updated_by        | varchar(20)   | NO   |     |                     |       | 
| a_last_update       | timestamp     | NO   |     | CURRENT_TIMESTAMP   |       | 
+---------------------+---------------+------+-----+---------------------+-------+

我们在一个表中有一组描述记录的字段。每条记录都由一个 m_id(人)和一个序号(一个人可以有多个记录)组成。因此，例如，我的 m_id 可以是 1，我可以有多个序数(1、2、3、4 等)，每个序数都有自己的数据集。 m_id和m_a_ordinal组成了“information”表中的复合键，“transactions”表中的m_id、m_a_ordinal和a_expired_date字段也组成了复合键。

本质上当我们过期记录时，信息表中的a_state字段被更新为过期。同时，在交易表中创建一条记录，其中包含 m_id、m_a_ordinal 和 a_expired_date。过去我们发现人们会变得不耐烦，可以点击一个按钮两次，因此通过之前的一些帮助，我设法使用以下查询缩小了每条过期记录的最近交易范围:

SELECT e1.m_id, e1.m_a_ordinal, e1.a_expired_date, e1.t_note, e1.t_updated_by 
FROM expire_history e1 
    INNER JOIN  (SELECT m_id, m_a_ordinal, MAX(a_expired_date) AS a_expired_date 
    FROM expire_history GROUP BY m_id, m_a_ordinal) e2 
    ON (e2.m_id = e1.m_id AND e2.m_a_ordinal = e1.m_a_ordinal AND e2.a_expired_date = e1.a_expired_date) 
WHERE e2.a_expired_date > '2008-05-15 00:00:00' ORDER BY a_date_expired;

看起来很简单，对吧？

让我们增加一些复杂性。 “信息”表中的每条记录也有一个“自然失效日期”。然而，我们软件的原始开发人员并未对其进行编码，以便在达到其自然到期日期后将记录状态更改为“已过期”。它也不会在事务表过期后将事务写入事务表(我理解这是因为这只是为了保留一个人过期的记录，而不是自动记录)。此外，当记录手动过期时，原始过期日期不会更改。这就是为什么这么复杂的原因:P~~。

基本上，我需要构建一个报告来显示过期的所有方面，无论是手动过期还是自然过期。

此报告应从上面的查询中获取数据，并将其与“信息表”上的另一个查询结合起来，如果 a_expire_date <= CURDATE 显示记录，除非记录存在于(上面的查询来自 expire_history)，然后显示记录来自(查询 expire_history)。

原始逻辑的粗略结构如下:

for x in record_total
   if (m_id m_a_ordinal) exists in expire_history
      display m_id, m_a_ordinal, a_expired_date, a_state)
   else if (m_id_a_ordinal) exists in information AND a_expire_date <= CURDATE
      display (m_id, m_a_ordinal, a_expire_date, a_state)
   end if
x++

我希望这足够简洁。

感谢您提供的任何帮助!

Answer 1

SELECT i.m_id, I.m_a_ordinal, 
   coalesce(e1.a_expired_date, I.A_Expire_Date) as Expire_DT, 
   coalesce(e1.t_note,'insert related item column'),
   coalesce(e1.t_updated_by, I.A_Updated_by) as Updated_By
FROM Information I 
    LEFT JOIN expire_history e1 
      ON E1.M_ID = I.M_ID
      AND I.m_a_ordinal=e1.M_a_ordinal
    INNER JOIN  
       (SELECT m_id, m_a_ordinal, MAX(a_expired_date) AS a_expired_date 
        FROM expire_history GROUP BY m_id, m_a_ordinal) e2 
      ON (e2.m_id = e1.m_id 
      AND e2.m_a_ordinal = e1.m_a_ordinal 
      AND e2.a_expired_date = e1.a_expired_date) 
WHERE coalesce(e2.a_expired_date,i.A_Expire_Date) > '2008-05-15 00:00:00' 
ORDER BY a_date_expired;

语法可能有点不对，没时间测试；但我希望你可以从中得到它的要点:

coalesce 所做的只是简单地返回一系列值中的第一个 NON-null 值。如果您只处理两个 NULLIF 也可能有效。