mysql - 如何构造mySQL查询以查找按特定顺序排序的相关信息

Question

我的数据库中有许多表，在构造快速高效的查询时需要帮助。到目前为止，由于我编写了不同的查询，结果要么不一致要么很大（返回的信息超出了我的需要，因此，以后必须使用PHP约束结果）。这是背景。

我们的数据库处理高级住房行业的潜在客户。每个销售线索都有许多注释（销售历史记录），这些注释不仅提供销售历史记录，而且还通知用户下一个跟进日期（actionDate）。每条线索可以分配给许多不同的状态（即，活跃，前10名，移入等）（尽管不是同时）。销售线索的状态是销售过程中销售线索进展的历史。我们可以看到线索的状态和时间。

在基本“线索”表中，每个线索都有一个称为“ inquiryID”的主键，该主键会自动递增。大多数其他表中都引用了此键，以将它们与“ lead”表相关联。这是“ lead”表的结构。

TABLE: lead (~500 rows)
+-------------------+------------+-------+--------+
|  Field            |  Type      |  Key  | Extra  |
+-------------------+------------+-------+--------+
|  inquiryID        |  int(11)   |  PK   |  AI    |
|  communityID      |  int(3)    |       |        |
|  initialDate      |  date      |       |        |  
|  inquirySource    |  tinytext  |       |        |
|  inquiryType      |  tinytext  |       |        |
+-------------------+------------+-------+--------+ 

另一个表名为“ leadNote”。该表处理每个销售线索的销售日记帐。基本上，销售人员会输入书写笔记的日期（日期），书写笔记的日期（salesCounselor），笔记本身（笔记），要跟进线索的人（actionCounselor）以及他们将跟进的日期（actionDate）。

TABLE: leadNote (~15000 rows)
+-------------------+------------+-------+--------+
|  Field            |  Type      |  Key  | Extra  |
+-------------------+------------+-------+--------+
|  inquiryNoteID    |  int(11)   |  PK   |  AI    |
|  inquiryID        |  int(11)   |  FK   |        |
|  date             |  date      |       |        |
|  salesCounselor   |  tinytext  |       |        |
|  note             |  text      |       |        |
|  actionCounselor  |  int(5)    |       |        |
+-------------------+------------+-------+--------+

我将参考的最终表名为“ leadStatusHistory”。该表处理此销售线索状态的历史记录。线索可以具有许多不同的状态，但不能同时存在。我们希望能够跟踪潜在客户的状态以及时间。潜在客户将具有状态（leadStatus），将状态分配给他们的日期（statusDate），以及在其他收集的数据中谁将状态分配给他们（作者）。

TABLE: leadStatusHistory (~1200 rows)
+-------------------+-------------+-------+--------+
|  Field            |  Type       |  Key  | Extra  |
+-------------------+-------------+-------+--------+
|  historyID        |  int(11)    |  PK   |  AI    |
|  inquiryID        |  int(11)    |  FK   |        |
|  leadStatus       |  tintytext  |       |        |
|  date             |  datetime   |       |        |
|  communityID      |  int(3)     |       |        |
|  timestamp        |  timestamp  |       |        |
+-------------------+-------------+-------+--------+

我的目标是能够运行返回查询ID，actionCounselor，actionDate和当前leadStatus的查询。如前所述，我尝试过的许多不同查询带来了不同的结果。我要收集此列表的方式有两种。 1）查找下一个联系日期小于或等于今天的所有潜在客户（这是计划在今天进行跟进的潜在客户列表）。 2）查找当前与某个LeadStatus匹配的所有潜在客户（即，查找当前状态为“移入”的所有潜在客户）。

这就是我如何对表进行排序以获取所需的信息。
1）查找quickingID，actionCounselor（在最近创建的“ leadNote”行或“日期”上最大的actionCounselor列中的值），actionDate（在最近创建的“ leadNote”行或“ date”上的actionDate列中的值） ”和“ leadStatus”（在最近创建的“ leadStatusHistory”行或最新的“ timestamp”上的leadStatus列中的值）。actionDate（在最近创建的“ leadNote”上的actionDate列中的值）最大的行或“日期”）小于或等于今天。

2）查找queryID，actionCounselor（在最近创建的“ leadNote”行或“日期”中最大的actionCounselor列中的值），actionDate（在最近创建的“ leadNote”行或“ date”中的actionDate列中的值） “是最大的”，而leadStatus（最近创建的“ leadStatusHistory”行的leadStatus列中的值或最大的“时间戳记”）WHERE leadStatus（最近创建的“ leadStatusHistory”行的leadStatus列中的值或“时间戳”最大）等于“移入”。

以下是一些当前和过去查询的示例，其中包含我对它们有何问题的评论。

查询＃1：

SELECT 
    tt.inquiryID,
    tt.actionDate,
    tt.date,
    tt.actionCounselor,
  (SELECT 
            leadstatushistory.leadstatus
        FROM
            leadstatushistory
        WHERE
            leadstatushistory.inquiryID = tt.inquiryID AND leadstatushistory.historyID = (SELECT 
                    MAX(leadstatushistory.historyID) as historyID
                FROM
                    leadstatushistory
                WHERE
                    inquiryID = tt.inquiryID)) AS leadStatus
FROM
    leadnote tt
        INNER JOIN
    (SELECT 
        inquiryID,
            MAX(inquiryNoteID) as inquiryNoteID,
            MAX(leadnote.actionDate) AS actionDate
    FROM
        leadnote
    GROUP BY inquiryID) groupedtt ON tt.inquiryID = groupedtt.inquiryID AND tt.inquiryNoteID = groupedtt.inquiryNoteID
WHERE
    tt.actionDate <= '2012-08-27' AND tt.actionDate != '0000-00-00' AND (SELECT 
            leadstatushistory.leadstatus
        FROM
            leadstatushistory
        WHERE
            leadstatushistory.inquiryID = tt.inquiryID AND leadstatushistory.historyID = 
               (SELECT 
                    MAX(leadstatushistory.historyID) as historyID
                FROM
                    leadstatushistory
                WHERE
                    inquiryID = tt.inquiryID)) != 'Resident' AND tt.communityID = 4
GROUP BY tt.inquiryID

注释：给了我我需要的列，但是抱怨说，“ actionDate”列不时会反映最近创建的leadNote行的日期，有时leadStatus是错误的。例如，leadStatusHistory表的max（historyID）不一定是我们要查找的最新状态。有时，我们的员工会回去为过去的潜在客户填写丢失的leadStatus。这将创建一个新的leadStatusHistory行，其中包含新的自动递增的historyID。在这种情况下，最新的（或最大的historyID）没有最大的“ leadStatusHistory.date”，因为用户输入的日期是过去的日期（填写过去的信息，因此我们的历史记录是准确的）。我们将有关注释输入到LeadNote表中以获取过去的注释，这完全相同。新的自动增量queryNoteID不一定与“ tt.date”最大的行匹配。

查询2：

SELECT 
    maxDate.inquiryID, maxDate.date, maxDate.actionDate, maxDate.actionCounselor
FROM
    (SELECT 
        *
    FROM
        leadnote
    ORDER BY date DESC , type ASC, inquiryNoteID DESC) as maxDate
        LEFT JOIN
    staff ON maxDate.actionCounselor = staff.staffID
WHERE
    maxDate.communityID = 4
GROUP BY inquiryID

评论：给了我我需要的专栏，但它也找到了所有潜在客户的信息。这浪费了宝贵的时间，并使响应速度变慢。然后，我必须使用PHP约束数据以仅显示那些具有actionDate为<=今天且日期不是“ 0000-00-00”的线索，或者我将数据约束为仅显示那些带有leadStatus的线索“移入”。同样，这确实给了我想要的结果，但是速度很慢。另外，如果我在子查询中添加到查询“ WHERE date <= [today] AND date！='0000-00-00'”，它会更改结果，因此它们不准确，然后我仍然必须使用PHP进行约束结果仅显示那些我正在寻找的状态。

通过查看以上信息，任何人都不会对如何更好地构建查询结构有任何想法，这样我便可以快速找到所需的确切信息。还是有一种方法可以更改表的结构或关系以获得我想要的结果。请任何帮助表示赞赏。

Answer 1

我的目标是能够运行返回查询ID，actionCounselor，actionDate和当前leadStatus的查询。


您正在尝试从leadNote和leadStatusHistory表中找到groupwise maxima：即在每个inquiryID组中具有最大日期的记录。

您可以通过以下几行查询来实现此目的：

SELECT inquiryID, actionCounselor, actionDate, leadStatus
FROM   (
           leadNote NATURAL JOIN (
             SELECT   inquiryID, MAX(actionDate) AS actionDate
             FROM     leadNote
             GROUP BY inquiryID
           ) AS t
        ) JOIN (
           leadStatusHistory NATURAL JOIN (
             SELECT   inquiryID, MAX(statusDate) AS statusDate
             FROM     leadStatusHistory
             GROUP BY inquiryID
           ) AS t
        ) USING (inquiryID)

为了获得最佳性能，应确保 leadNote在 (inquiryID, actionDate)上具有复合索引，并且 leadStatusHistory在 (inquiryID, statusDate, leadStatus)上具有复合索引：

 ALTER TABLE leadNote ADD INDEX (inquiryID, actionDate);
 ALTER TABLE leadStatusHistory ADD INDEX (inquiryID, statusDate, leadStatus);

  我要收集此列表的方式有两种。 1）查找下一个联系日期小于或等于今天的所有潜在客户（这是计划在今天进行跟进的潜在客户列表）。 2）查找当前与某个LeadStatus匹配的所有潜在客户（即，查找当前状态为“移入”的所有潜在客户）。



添加 WHERE actionDate <= CURRENT_DATE
添加 WHERE leadStatus = 'move in'