比较MySQL中的日期，其中日期在同一个表中 [英] Compare Dates in MySQL where Dates are in the Same Table

问题描述

我想比较同一个表中的两个日期。我试图比较它们，看看哪个是最近的日期。我看到了CAST（）和MAX（），但我不确定如何以最近的方式比较它们。

我们在内部系统中有一个页面显示事件。我们需要执行报告页面，但我们只想根据项目提取最新日期。如果我们有3个具有不同日期的项目的不同实例，我需要根据日期知道它的最后已知记录。第一个日期字段是客户，另一个是客户。使用MAX（）会提取一条记录，然后将最后一个已知日期粘贴到日期字段中，这是我们不想要的。将显示最后一个已知日期，但我们需要比较该特定日期的日期，然后用该日期拉出记录，而不是其他两个记录。

解决方案

< blockquote>假设您正在使用支持它的DBMS，请 ROW_NUMBER 功能 [ ^ ]正是你要找的：

  WITH  cteOrderedResults  As  
（
  SELECT  
 SomeColumns，
 ROW_NUMBER（） OVER （ PARTITION   BY 客户 ORDER   BY  TheDate  DESC ） 作为 RN 
  FROM  
 YourTable 
）
 < span class =code-keyword> SELECT  
 SomeColumns 
  FROM  
 cteOrderedResults 
  WHERE  
 RN =  1  
; 

注意：为了将来参考，请始终指定您使用的DMBS，包括版本号。此外，它有助于包括表的基本结构和一些示例数据。如果可能，请使用 SQLFiddle [ ^ ]创建问题的一小部分样本。

I am trying to compare two dates in the same table. I am trying to compare them to see which is the most recent date. I see the CAST() and MAX(), but I'm not sure as to how to compare them in the manner of which is the most recent.
We have a page in an internal system that displays events. We need to do a reporting page, but we only want to pull the most recent dates based on the item. If we have 3 different instances of that item with different dates, I would need to know its last known record based upon the date. First date field is to the customer and the other is from the customer. Using MAX() is pulls up a record and then sticks the last known date in the date field, which we do not want. The last known date will be displayed, but we are needing to compare the dates for that particular date and then pull the record with that date, not the other two records.

解决方案

Assuming you're using a DBMS that supports it, the ROW_NUMBER function[^] is what you're looking for:

WITH cteOrderedResults As
(
    SELECT
        SomeColumns,
        ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY TheDate DESC) As RN
    FROM
        YourTable
)
SELECT
    SomeColumns
FROM
    cteOrderedResults
WHERE
    RN = 1
;

NB: For future reference, always specify which DMBS you are using, including the version number. Also, it helps to include the basic structure of your table, and some example data. If possible, use SQLFiddle[^] to create a small sample of the problem.

这篇关于比较MySQL中的日期，其中日期在同一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！