MySQL根据查找表中的值从多个表中按日期获取最新记录

Question

我想获取给定 MainNumber 的最新 MainNumber、Serial、BarType 和 Notes（如果存在）。请注意，BarType 存储在查找表中并通过 BarID 引用。

Unreason 提出了这个：

SELECT @MainNumber, COALESCE(n.Notes, 'None')
FROM numbers 
     LEFT JOIN notes n ON numbers.MainNumber = n.MainNumber
     LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date < n2.Date
WHERE n2.Date IS NULL AND numbers.MainNumber = @MainNumber

无论 Notes 是否为 NULL 都可以，但现在我需要 Serial 和 BarType。 MainNumber 在其生命周期内可能已分配给多个序列号，但我只想要最新的序列号。 （我需要对其他表中的大约 15 个其他字段执行此操作，因此如果可能，我们将不胜感激）

表

Numbers 表：

CREATE TABLE `numbers` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `MainNumber` varchar(11) NOT NULL,
  `Serial` varchar(20) NOT NULL,
  `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `Serial` (`Serial`)
) ENGINE=MyISAM AUTO_INCREMENT=460 DEFAULT CHARSET=latin1

Notes 表：

CREATE TABLE `notes` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `MainNumber` varchar(11) NOT NULL,
  `Notes` longtext NOT NULL,
  `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  KEY `MainNumber` (`MainNumber`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

ref_bars 表：

CREATE TABLE `ref_bars` (
  `BarID` varchar(6) NOT NULL,
  `BarType` varchar(30) NOT NULL,
  PRIMARY KEY  USING BTREE (`BarID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

bars 表：

CREATE TABLE `bars` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `MainNumber` varchar(11) NOT NULL,
  `BarID` varchar(6) NOT NULL,
  `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  KEY `MainNumber` (`MainNumber`)
) ENGINE=MyISAM AUTO_INCREMENT=212 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

示例数据

SELECT * FROM Notes：

'ID','MainNumber','Notes','Date'
'1','1','repaired','2009-03-23 12:00:00'
'2','1','replaced','2009-08-15 19:20:05'

注意：MainNumber = 1 时有两行，但 MainNumber 为 2 时没有行。ID 只是技术性的，从未使用过。

从数字中选择*：

'ID','MainNumber','Serial','Date'
'1','1','4642785154854','2008-08-15 12:30:00'
'2','1','4642315642316','2009-08-15 12:50:00'
'3','2','5412558456223','2010-08-15 11:30:00'

从条形中选择*：

'ID','MainNumber','BarID','Date'
'1','1',1,'2008-08-15 12:30:00'
'2','1',2,'2009-08-15 12:50:00'
'3','2',2,'2010-08-15 11:30:00'

从ref_bars中选择*：

'BarID','BarType'
'1','Banned'
'2','Payment required'

预期输出

MainNumber = 1

MainNumber,Notes,Banned,Unpaid
'1','replaced','Yes','Yes'

MainNumber = 2

MainNumber,Notes,Banned,Unpaid
'2','None','No','Yes'

编辑：修复并测试它，同时使事情变得更清晰（希望如此）。今天早些时候我被赶去做其他事情，很抱歉因为一个写得不好、不完整的问题浪费了人们的时间。

更新以澄清更复杂的要求

Answer 1

您可以按照 Unreason 的建议使用 JOIN 来完成此操作。

另一种方法是使用子查询：

select distinct s.MainNumber, 
COALESCE(
  (select n.notes from notes n where n.MainNumber=s.MainNumber order by n.Date desc limit 1), 
  'None') as LastNote
from numbers s
WHERE s.MainNumber=?

请注意，使用 JOIN 的解决方案可能会或可能不会执行得更好，您必须尝试一下。

另请注意，“LIMIT”是 MySQL 特定的（不是 ANSI SQL），因此如果您打算迁移到另一个 DBMS，请务必小心。

Answer 2

选择忽略您的初始查询，因为它们不会重现您的问题我正在查看您的预期输出我假设您正在尝试实现以下目标：

给定MainNumber查找记录
在 tabel notes 中并返回该行
最大日期，如果没有记录
在给定的表注释中
MainNumber 然后返回常量
“无”

（这可能不是所要求的，所以如果不是，请更正预期的输出）

这可以很容易地实现，例如

SELECT @MainNumber, n.Notes
FROM notes n
     LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date < n2.Date
WHERE n2.Date IS NULL AND n.MainNumber = @MainNumber

Which will return the最新行 fromnotes.
现在，从应用程序端来看，如果它不返回任何行，只需打印 @MainNumber, 'None' 就可以了...

如果您寻找纯 SQL（并假设您确实需要数字表中的其他一些列），那么您可以做：

SELECT @MainNumber, COALESCE(n.Notes, 'None')
FROM numbers 
     LEFT JOIN notes n ON numbers.MainNumber = n.MainNumber
     LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date < n2.Date
WHERE n2.Date IS NULL AND numbers.MainNumber = @MainNumber

编辑：
第一个查询经过测试

mysql> SET @MainNumber = 1;                                                     Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @MainNumber, n.Notes FROM notes n      LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date < n2.Date WHERE n2.Date IS NULL AND n.MainNumber = @MainNumber;
+-------------+----------+
| @MainNumber | Notes    |
+-------------+----------+
|           1 | replaced | 
+-------------+----------+
1 row in set (0.00 sec)

第二个查询最初在数字表中有多个条目的情况下返回多行，DISTINCT 修复了该问题

mysql> SET @MainNumber = 1;                                                     Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT @MainNumber, COALESCE(n.Notes, 'None')
    -> FROM numbers 
    ->      LEFT JOIN notes n ON numbers.MainNumber = n.MainNumber
    ->      LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date < n2.Date
    -> WHERE n2.Date IS NULL AND numbers.MainNumber = @MainNumber
    -> ;
+-------------+---------------------------+
| @MainNumber | COALESCE(n.Notes, 'None') |
+-------------+---------------------------+
|           1 | replaced                  | 
+-------------+---------------------------+
1 row in set (0.00 sec)

mysql> SET @MainNumber = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT @MainNumber, COALESCE(n.Notes, 'None')
    -> FROM numbers 
    ->      LEFT JOIN notes n ON numbers.MainNumber = n.MainNumber
    ->      LEFT JOIN notes n2 ON n.MainNumber = n2.MainNumber AND n.Date < n2.Date
    -> WHERE n2.Date IS NULL AND numbers.MainNumber = @MainNumber
    -> ;
+-------------+---------------------------+
| @MainNumber | COALESCE(n.Notes, 'None') |
+-------------+---------------------------+
|           2 | None                      | 
+-------------+---------------------------+
1 row in set (0.00 sec)

Answer 3

也许是这样的？

LEFT JOIN (select notes n
JOIN (
    SELECT
        MAX(Date) AS Date,
        MainNumber
    FROM notes
    GROUP BY MainNumber
) AS nx
    ON n.MainNumber = nx.MainNumber AND n.Date = nx.Date) n
ON notes.MainNumber = main.MainNumber

我删除了 where 子句，因为您没有描述为什么拥有它。如果您需要它，只需再次插入即可。

编辑：查询已更新，但仍然很难准确理解您想要的内容。当我编写示例数据时，我的意思是每个表包含 2-3 行以及预期输出，如果您可以简化示例，那就更好了。希望有帮助。

Answer 4

我终于解决了。实际上比我想象的要简单得多。它也更接近我编写的原始查询。

SELECT
    s.MainNumber,
    n.Notes
FROM numbers s
JOIN (
    SELECT
        MAX(Date) AS Date,
        MainNumber
    FROM numbers
    WHERE MainNumber = 2
) AS sx
    ON s.MainNumber = sx.MainNumber AND s.Date = sx.Date

LEFT JOIN notes n
    ON s.MainNumber = n.MainNumber
LEFT JOIN (
    SELECT
            MAX(Date) AS Date,
            COALESCE(MainNumber,0) AS MainNumber
    FROM notes
    WHERE MainNumber = 2
) AS nx
    ON n.MainNumber = nx.MainNumber AND n.Date = nx.Date

Answer 5

SELECT MainNumber,
       COALESCE(Notes,"None") as Notes,
       if(BarID=1,"Yes","No") as Banned,
       if(BarID=2,"Yes","No") as Unpaid,
       COALESCE(notes.Date,CURRENT_TIMESTAMP) as Date
FROM numbers LEFT JOIN notes USING (MainNumber)
     LEFT JOIN bars USING (MainNumber)
GROUP BY MainNumber,Date
HAVING Date=COALESCE(MAX(notes.Date),CURRENT_TIMESTAMP)