如何得到两次约会之间的天数

时间：2021-02-08 21:28:48

I have a situation where I need to find the number of days that have passed between two rows with date fields. The rows that the calculation needs to be made are not sorted.

我有一种情况，我需要找到在两行与日期字段之间传递的天数。需要进行计算的行没有排序。

Here is the structure of the table

这是表格的结构

Folio   DATE    

1       6/1/2015
2       4/1/2015
1       3/1/2015
4       2/1/2015
1       1/1/2015

Basically, I would need to sort by date and keep only the last two transactions grouped by folio. so in this example, the transaction by folio 1 on 1/1/2015 would be ignored.

基本上，我需要按日期排序，只保留按folio分组的最后两个事务。在这个例子中，folio 1在2015年1月1日的事务将被忽略。

Suppose that I need to do the following: 1. Group by folio number 2. only count the days between the last two transactions by folio. For example, folio #1 would only include the transactions from 6/1/2015 and 3/1/2015.

假设我需要做以下事情:1。按页码2分类。只计算最近两次交易之间的天数。例如，folio #1只包含6/1/2015和3/1/2015的事务。

The result I'm looking for:

我想要的结果是:

Folio   FirstDATE   LastDate   #ofDays

1       3/1/2015     6/1/2015    90

Any MySQL pros out there? My skills are still in newbie territory. Thank you!

有任何MySQL专业人员吗?我的技能还停留在新手的领域。谢谢你！

UPDATE:

更新:

I've managed to come up with the following:

我想出了以下方法:

SELECT 
  SubQuery.`Folio Number`,
  SubQuery.LatestClosing,
  SubQuery.FirstClosing,
  DATEDIFF(SubQuery.LatestClosing, SubQuery.FirstClosing) AS numofdays
FROM (SELECT
    Subquery.`Folio Number`,
    SubQuery.LatestClosing,
    SubQuery.FirstClosing
  FROM (SELECT t.`Folio Number`,
      MAX(t.`Closing Date`) AS LatestClosing,
      (SELECT
          s.`Closing Date`
        FROM MLSFinalimport s
        WHERE t.`Folio Number` = s.`Folio Number`
        ORDER BY s.`Closing Date` DESC
        LIMIT 1, 1) AS FirstClosing,
    FROM MLSFinalimport t
    GROUP BY t.`Folio Number`) SubQuery) SubQuery

This is generating a result that looks like this:

结果是这样的:

LatestClosing     First Closing   numofdays
7/20/2016         5/9/2006         3725

This is what I need. However, I'm stuck trying to add the original column for each row called "Folio Number". How do I proceed?

这就是我需要的。然而，我不得不为每一行添加一个名为“Folio Number”的原始列。我怎么继续?

Thank you very much.

非常感谢。

1 个解决方案

#1

2  

Pros for MySQL at this? Probably the opposite.. MySQL doesn't support window functions so you can try using a correlated query with LIMIT/OFFSET:

MySQL的优点?可能是相反的。MySQL不支持窗口函数，所以您可以尝试使用带有限制/偏移量的相关查询:

SELECT p.folio,p.max_d,p.second_d,DATEDIFF(p.max_d,p.second_d) as NumOfDays
FROM (
    SELECT t.folio,MAX(t.date) as max_d,
           (SELECT s.date FROM YourTable s
            WHERE t.folio = s.folio
            ORDER BY s.date DESC
            LIMIT 1,1) as second_d
    FROM YourTable t
    GROUP BY t.folio) p

#1

2  

Pros for MySQL at this? Probably the opposite.. MySQL doesn't support window functions so you can try using a correlated query with LIMIT/OFFSET:

MySQL的优点?可能是相反的。MySQL不支持窗口函数，所以您可以尝试使用带有限制/偏移量的相关查询:

SELECT p.folio,p.max_d,p.second_d,DATEDIFF(p.max_d,p.second_d) as NumOfDays
FROM (
    SELECT t.folio,MAX(t.date) as max_d,
           (SELECT s.date FROM YourTable s
            WHERE t.folio = s.folio
            ORDER BY s.date DESC
            LIMIT 1,1) as second_d
    FROM YourTable t
    GROUP BY t.folio) p
标签：

相关文章