MYSQL 帮助 - 操作顺序和子查询

Question

我正在使用两个表(ft_form_1 和 ft_field_options)。

ft_form_1

    submission_id  facility_id  admits  reporting_date    timestamp
    1              111A         1       2017-03-31 00:00  2017-03-31 17:53:17
    2              222B         3       2017-03-31 00:00  2017-03-31 18:42:20
    3              333C         6       2017-03-31 00:00  2017-03-31 19:27:47
    4              222B         0       2017-04-01 00:00  2017-04-01 18:12:12
    5              333C         4       2017-03-31 00:00  2017-04-01 19:38:25
    6              333C         5       2017-04-01 00:00  2017-04-01 20:31:16

ft_field_options

    list_id option_order    option_value    option_name
    1       4               111A            New York
    1       2               222B            Chicago
    1       1               333C            Boston
    1       3               444D            Miami

我目前得到的:

    facility_id option_name option_order    admits  reporting_date      timestamp
    111A        New York    3               1       2017-03-31 00:00    2017-03-31 17:53:17

我想要得到的:

    facility_id option_name option_order    admits  reporting_date      timestamp
    111A        New York    3               1       2017-03-31 00:00    2017-03-31 17:53:17
    222B        Chicago     2               3       2017-03-31 00:00    2017-03-31 18:42:20
    333C        Boston      1               4       2017-03-31 00:00    2017-04-01 19:38:25

通过以下查询，我试图从每个“facility_id”的指定“reporting_date”获取所有提交的列表，其中“list_id”等于 1。如果在同一个“reporting-date”发送多个提交'，只会显示具有最新“时间戳”的提交。

问题:我认为此查询未按我希望的顺序运行。查询似乎正在为表中的每个“facility_id”查找最大“时间戳”，然后过滤以仅显示具有指定“reporting_date”的提交。我希望它以相反的顺序发生——查询过滤器只显示具有指定“reporting_date”的提交，然后将列表缩小到只显示每个“facility_id”的最大“timestamp”。正如您从我上面的示例中看到的那样，芝加哥和波士顿被排除在列表之外，因为它们在第二天有一个更新的“时间戳”。

我是一个MYSQL新手，非常感谢任何帮助!我最初从这个链接得到 MAX 子查询的想法:http://www.w3resource.com/sql/aggregate-functions/max-date.php

SELECT t1.facility_id, t1.admits, t1.reporting_date, t1.timestamp, 
t2.option_name, t2.option_order
FROM ft_form_1 t1
LEFT JOIN ft_field_options t2
ON (t1.facility_id = t2.option_value AND t2.list_id = 1)
WHERE (DATE_FORMAT(t1.reporting_date,'%m/%d/%Y') = 
DATE_FORMAT(NOW() - INTERVAL 1 DAY,'%m/%d/%Y')) AND (timestamp=(
SELECT MAX(timestamp)
FROM ft_form_1
WHERE facility_id = t1.facility_id))
ORDER BY option_order ASC

*假设今天是 2017 年 4 月 1 日，上述查询将起作用。

Answer 1

这里的答案是使用结构化查询语言的结构化部分。SQL 是一种声明性而非过程性语言，因此考虑它做事的顺序。

首先，您要查找 list_id 为 1 的行。让我们执行一个查询来获取它。请注意使用 JOIN 而不是 LEFT JOIN:我们不需要不匹配的元素。 ( http://sqlfiddle.com/#!9/8bdc3c/1/0 )

                  SELECT t1.* 
                    FROM ft_form_1 t1
                    JOIN ft_field_options t2 
                            ON t1.facility_id = t2.option_value 
                           AND t2.list_id = 1

接下来，我们需要找到每个设施的每个报告日期最后一次提交的时间戳。 ( http://sqlfiddle.com/#!9/8bdc3c/3/0 )

                  SELECT MAX(timestamp) timestamp,
                         reporting_date reporting_date,
                         facility_id 
                    FROM ft_form_1
                   GROUP BY reporting_date, facility_id

这些类型的子查询是一种很好的方式，因为它们很容易测试。

最后，我们将这两个子查询连接在一起以获得我们想要的结果。 ( http://sqlfiddle.com/#!9/8bdc3c/6/0 )

SELECT a.*
  FROM (
                  SELECT t1.* 
                    FROM ft_form_1 t1
                    JOIN ft_field_options t2 
                            ON t1.facility_id = t2.option_value 
                           AND t2.list_id = 1
       ) a
  JOIN (
                  SELECT MAX(timestamp) timestamp,
                         reporting_date reporting_date,
                         facility_id 
                    FROM ft_form_1
                   GROUP BY reporting_date, facility_id
       ) b ON a.facility_id = b.facility_id
          AND a.timestamp = b.timestamp
          AND a.reporting_date = b.reporting_date

诀窍在于 GROUP BY 子查询，它找到每个报告日期的最后一个时间戳。

现在，您的表中有一个 submission_id。如果该 submission_id 是自动递增的，那么很可能每个设施中用于报告数据的最大 submission_id 值就是您想要的值。 如果这是真的，您可以大大简化查询。

这将为您提供对应于您想要的提交的 submission_id 值

                SELECT MAX(submission_id)
                  FROM ft_form_1
                 GROUP BY reporting_date, facility_id

您可以像这样将其连接到您的主表，以获得您想要的结果。 ( http://sqlfiddle.com/#!9/8bdc3c/10/0 )

 SELECT t1.* 
   FROM ft_form_1 t1
   JOIN ft_field_options t2 ON t1.facility_id = t2.option_value 
                           AND t2.list_id = 1
   JOIN (
                SELECT MAX(submission_id) submission_id
                  FROM ft_form_1
                 GROUP BY reporting_date, facility_id
        ) q ON t1.submission_id = q.submission_id