当前位置： 文江博客 话题详情

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

发布于 2024-09-26 14:54:00 字数 3149 浏览 8 评论 0 原文

我想获取给定 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'

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

更新以澄清更复杂的要求

原文

I want to get the latest MainNumber, Serial, BarType and Notes for a given MainNumber, if they exist. Note that BarType is stored in a lookup table and referenced with BarID.

Unreason came up with this:

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

This is fine whether Notes is NULL or not, but now I need the Serial and the BarType. A MainNumber may have been assigned to multiple Serials during its lifetime, but I only want the latest Serial. (I'll need to do this with about 15 other fields in other tables, so a performant answer would be appreciated where possible)

Tables

Numbers Table:

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 table:

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 table:

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 table:

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

Sample Data

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'

Note: two rows for MainNumber = 1, but no row for a MainNumber of 2. The IDs are just technical and are never used.

SELECT * FROM numbers:

'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'

SELECT * FROM bars:

'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'

SELECT * FROM ref_bars:

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

Expected Output

MainNumber = 1

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

MainNumber = 2

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

Edit: Fixed it and tested it, whilst making things clearer (hopefully). I was rushed off to do other things earlier today, sorry for wasting people's time with a badly-written, incomplete question.

Updated to clarify the more complex requirements

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（5）

苍风燃霜 2024-10-03 14:54:00

您可以按照 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，请务必小心。

You can do it with a JOIN, as suggested by Unreason.

Another way would be with a subquery:

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=?

Note that the solution using JOIN may or may not perform better, you'll have to try it.

Also note that "LIMIT" is MySQL-specific (not ANSI SQL), so take care if you intend to migrate to another DBMS.

回复 原文
苄①跕圉湢 2024-10-03 14:54:00

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

给定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)

Choosing to ignore your initial queries since they do not reproduce your problem I am looking at you expected output I assume that you are trying to achieve the following:

Given MainNumber look for the record
in tabel notes and return the row
with max date, if there are no records
in table notes for a given
MainNumber then return constant
'None'

(this might not be what is requested, so please correct the expected output if it is not)

This can be easily achieved with, for example

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 latest row from notes.
Now from the application side if it does not return any rows just print @MainNumber, 'None' and that is it...

If you look for pure SQL (and assuming that you do need some other columns from the numbers table) then you can do:

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

EDIT:
The first query is tested

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)

The second query initially returned multiple rows in case of multiple entries in the numbers table, DISTINCT fixes that

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)
回复 原文
随心而道 2024-10-03 14:54:00

也许是这样的？

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 行以及预期输出，如果您可以简化示例，那就更好了。希望有帮助。

Maybe something like this?

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

I removed the where-clause since you don't describe why you have it. If you need it you can just insert it again.

EDIT: The query is updated, but it is still hard to understand exatcly what you want. When I wrote example data I meant 2-3 rows for each table together with expected output, and if you could simplify the example that would be even better. Hope it helps.

回复 原文
涫野音 2024-10-03 14:54:00

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

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

I finally worked it out. It's actually a lot more simple than I thought. It's closer to the original query I was writing, too.

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
回复 原文
最丧也最甜 2024-10-03 14:54:00 
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)

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)
回复 原文
~没有更多了~

关于作者

陈独秀

暂无简介

0 文章
0 评论
25 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

隔纱相望

文章 0 评论 0

昵称有卵用

文章 0 评论 0

梨涡

文章 0 评论 0

蓝咒

文章 0 评论 0

白芷

文章 0 评论 0

樱娆

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文