主页 / dba / 问题 / 71721
Accepted
Toskan
Asked: 2014-07-17 18:50:04 +0800 CST

仅获取连接中最旧的条目

  • 772

所以我有一张桌子bugs

|bugid| 标题|

该表缺少日期列。但是在创建一个新条目时，很可能总是会创建一个新条目，bugnotes并在日期中显示“已创建错误”。

bugnotes：

| 错误注释 | 臭虫 | 日期时间 | 正文 |

我想列出所有错误，bugnotes如果可用，显示最旧条目的日期

mysql join

2 个回答

  1. 我认为你想要这样的东西（见下表定义）。我的 SQLFiddle 演示似乎发生了一些事情。

    SELECT bs.bugid, bs.bugtitle, bn.bugnotesid, bn.notetime, bn.notetext 
FROM bugs bs, bugnotes bn
JOIN  
(
  SELECT bugnotesid, MIN(notetime) 
  FROM bugnotes 
  GROUP By (bugid)
) mytab
ON bn.bugnotesid = mytab.bugnotesid 
WHERE bs.bugid = bn.bugid;

CREATE TABLE bugs values
(
  bugid int,
  bugtitle varchar(34)
);

INSERT INTO bugs values(1, 'the first bug');
INSERT INTO bugs values(2, 'the second bug');
INSERT INTO bugs values(3, 'the third bug');

CREATE TABLE bugnotes
(
  bugnotesid int,
  bugid int,
  notetime datetime,
  notetext varchar(400)
);

INSERT INTO bugnotes values(1, 1, '2014-03-14', 'afasdfasdf');
INSERT INTO bugnotes values(2, 1, '2014-03-15', 'afasdfasdf');
INSERT INTO bugnotes values(3, 1, '2014-03-16', 'afasdfasdf');
INSERT INTO bugnotes values(4, 1, '2014-03-17', 'afasdfasdf');
INSERT INTO bugnotes values(5, 2, '2014-02-14', 'afasdfasdf');
INSERT INTO bugnotes values(6, 2, '2014-02-20', 'afasdfasdf');
INSERT INTO bugnotes values(7, 2, '2014-02-22', 'afasdfasdf');
INSERT INTO bugnotes values(8, 2, '2014-02-25', 'afasdfasdf');
INSERT INTO bugnotes values(9, 2, '2014-02-28', 'afasdfasdf');
INSERT INTO bugnotes values(10, 3, '2013-03-14', 'afasdfasdf');
INSERT INTO bugnotes values(11, 3, '2013-03-16', 'afasdfasdf');
INSERT INTO bugnotes values(12, 3, '2013-03-19', 'afasdfasdf');
INSERT INTO bugnotes values(13, 3, '2013-03-20', 'afasdfasdf');
INSERT INTO bugnotes values(14, 3, '2013-03-27', 'afasdfasdf');

    [编辑 - 在分析了作为解决方案提供的两个查询之后]

    这一点对 Joshua 来说确实是一个问题——不确定我是应该把它变成一个新帖子还是继续在这里——如果有要求，我总是可以移动它。

    嗨约书亚，

    我是从 MySQL 开始的，所以我利用我们的交流来分析我们对 OP 的两个响应。我对我的发现有点困惑。我的需要大约 200 我们，而你的似乎需要两倍以上 - 而且你的行数要多得多（更多信息？）。我很好奇为什么？你知道发生了什么事吗？

    我赶紧补充说，在运行第二个（我的）查询之前，我做了一个 RESET QUERY CACHE。我也以相反的顺序运行它们并获得了相同的结果。

    我很困惑！

    约书亚的询问。

    mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000186 |
| checking permissions | 0.000011 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000043 |
| init                 | 0.000069 |
| System lock          | 0.000019 |
| optimizing           | 0.000012 |
| statistics           | 0.000038 |
| preparing            | 0.000057 |
| Creating tmp table   | 0.000056 |
| Sorting result       | 0.000008 |
| executing            | 0.000006 |
| Sending data         | 0.000218 |
| Creating sort index  | 0.000060 |
| end                  | 0.000011 |
| removing tmp table   | 0.000029 |
| end                  | 0.000009 |
| query end            | 0.000013 |
| closing tables       | 0.000018 |
| freeing items        | 0.000027 |
| cleaning up          | 0.000024 |
+----------------------+----------+
21 rows in set, 1 warning (0.00 sec)

mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)

    我的查询。

    mysql> show profile for query 2;
+----------------+----------+
| Status         | Duration |
+----------------+----------+
| starting       | 0.000131 |
| query end      | 0.000010 |
| closing tables | 0.000007 |
| freeing items  | 0.000020 |
| cleaning up    | 0.000031 |
+----------------+----------+
5 rows in set, 1 warning (0.00 sec)

mysql>
    • 2
  2. Best Answer

    试试下面的这个查询。请注意，它仅列出了错误及其派生的创建日期（即：不包括错误注释等）。连接OUTER优先返回所有错误，无论它们是否有任何相关的错误注释。

    SELECT bugid
     , bugtitle
     , MIN(notetime) created
  FROM bugs b
  LEFT OUTER JOIN bugnotes USING (bugid)
 GROUP BY bugid, bugtitle;

    但更可靠的解决方案是在表中添加一列bugs以自动捕获创建时间。注意：您必须使用此列的值回填任何现有的错误记录——您可以从SELECT上面的查询中获得该值。

    ALTER TABLE bugs ADD created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;

    编辑：这是“回填”UPDATE语句，假设您已经添加了该created列：

    UPDATE bugs b
SET created = (SELECT MIN(notetime)
               FROM bugnotes
               WHERE bugid=b.bugid);

    EDIT2：我刚刚在 MySQL 5.6.13 Community 上分析了两个查询。这看起来更理智一些，因为这两个查询在他们的个人资料中都有类似的东西checking permissions；任何 SELECT 查询都不应该总是检查权限（可能在运行skip-grant-tables模式下除外）？我很困惑为什么之前的分析没有在一个简短的配置文件上显示权限检查。

    无论如何，我认为持续时间并不重要。starting我首先运行了 Verace 的查询，并且在和中花费了很多时间opening tables。我第二次运行自己的查询，这些相同的项目花费的时间更少。如果我只是选择不同的顺序，我有 99.9% 的把握会看到相反的结果。因此，我将持续时间视为垃圾。

    第一个查询运行通常会承担加载缓存的工作。单独刷新 MySQL 的缓存是不够的。您的操作系统可能正在缓存磁盘块。即使您尝试刷新操作系统缓存，您的硬盘本身也可能至少有 8MB 缓存。我想如果您想用两个查询来平衡该字段，您可以交替运行它们 5 次以稳定软件和硬件缓存的任何影响。

    或者另一个计划（这可能是矫枉过正）：重新启动服务器，运行查询 A 并查看其配置文件。再次重新启动并运行 & profile only 查询 B。比较两个配置文件。

    我的结论是在小表（例如 10 行）上进行 MySQL 分析是徒劳的。旁注，SHOW PROFILE在 MySQL 5.6.7+ 中已弃用，而是使用PERFORMANCE_SCHEMA对象。

    mysql> show profile for query 1; -- Verace
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.055357 |
| checking permissions | 0.000019 |
| checking permissions | 0.000004 |
| checking permissions | 0.000013 |
| Opening tables       | 0.017346 |
| init                 | 0.000045 |
| System lock          | 0.000015 |
| optimizing           | 0.000005 |
| optimizing           | 0.000008 |
| statistics           | 0.000037 |
| preparing            | 0.000020 |
| Creating tmp table   | 0.000047 |
| Sorting result       | 0.000038 |
| statistics           | 0.000089 |
| preparing            | 0.000039 |
| executing            | 0.016013 |
| Sending data         | 0.000179 |
| executing            | 0.000007 |
| Sending data         | 0.004832 |
| Creating sort index  | 0.000062 |
| removing tmp table   | 0.000017 |
| Creating sort index  | 0.000062 |
| end                  | 0.000007 |
| query end            | 0.000023 |
| closing tables       | 0.000005 |
| removing tmp table   | 0.000016 |
| closing tables       | 0.000019 |
| freeing items        | 0.000041 |
| cleaning up          | 0.000028 |
+----------------------+----------+
29 rows in set, 1 warning (0.00 sec)

    .

    mysql> show profile for query 2; -- Josh
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000116 |
| checking permissions | 0.000008 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000045 |
| init                 | 0.000060 |
| System lock          | 0.000021 |
| optimizing           | 0.000010 |
| statistics           | 0.000034 |
| preparing            | 0.000033 |
| Creating tmp table   | 0.000060 |
| Sorting result       | 0.000006 |
| executing            | 0.000011 |
| Sending data         | 0.000168 |
| Creating sort index  | 0.000046 |
| end                  | 0.000006 |
| removing tmp table   | 0.000016 |
| end                  | 0.000007 |
| query end            | 0.000010 |
| closing tables       | 0.000017 |
| freeing items        | 0.000046 |
| cleaning up          | 0.000041 |
+----------------------+----------+
21 rows in set, 1 warning (0.00 sec)

    EDIT3：示例输出。我正确地得到了每个 bugid 的一行。这是使用 Verace 制作的样本数据。如果查询对您不起作用，我怀疑我们有不同的源数据。您可以通过发布带有少量代表性数据的 SQL Fiddle 来解决这个问题，或者只是编辑您的问题并向其中添加一些示例数据。

    mysql> SELECT bugid
    ->      , bugtitle
    ->      , MIN(notetime) created
    ->   FROM bugs b
    ->   LEFT OUTER JOIN bugnotes USING (bugid)
    ->  GROUP BY bugid, bugtitle;
+-------+----------------+---------------------+
| bugid | bugtitle       | created             |
+-------+----------------+---------------------+
|     1 | the first bug  | 2014-03-14 00:00:00 |
|     2 | the second bug | 2014-02-14 00:00:00 |
|     3 | the third bug  | 2013-03-14 00:00:00 |
+-------+----------------+---------------------+
3 rows in set (0.00 sec)
    • 2

相关问题