SQL查询 - UNIX时间戳 - 第二个最新日期

发布于 2025-01-22 06:15:35 字数 458 浏览 1 评论 0原文

我是SQL的新手，只需要创建一个查询即可能够在我的博客上发挥功能，并且我正在对代码进行小步骤，但是我坚持回收第二个最新的Unixtime日期。

SELECT FROM_UNIXTIME(MAX(datum),'%d/%m-%Y') AS newest_postdate_ever,
(SELECT FROM_UNIXTIME(MAX(datum),'%d/%m-%Y') WHERE datum < (SELECT MAX(datum) FROM table-content)) AS second_newest_postdate_ever


[newest_postdate_ever] => 16/04-2022
[second_newest_postdate_ever] =>

但它应该说[second_newest_postdate_ever] =＆gt; 15/04-2022

原文

I'm completely new to SQL and just need to create one query to be able to make a function on my blog and I'm taking small steps in the code, but I'm stuck on reciving the second newest unixtime date.

SELECT FROM_UNIXTIME(MAX(datum),'%d/%m-%Y') AS newest_postdate_ever,
(SELECT FROM_UNIXTIME(MAX(datum),'%d/%m-%Y') WHERE datum < (SELECT MAX(datum) FROM table-content)) AS second_newest_postdate_ever


[newest_postdate_ever] => 16/04-2022
[second_newest_postdate_ever] =>

But it supposed to say [second_newest_postdate_ever] => 15/04-2022

分享到QQ

分享到微博

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

发布评论

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

情何以堪。 2025-01-29 06:15:35

如果您使用的是MySQL版本8，我们可以使用Row_number。如果是较早的版本，我们可以使用变量来执行相同的操作。我给你两个查询。
一旦我们分配了row_number desc，最近的日期就会具有rn = 1，并在使用之前获取一个日期，

CREATE TABLE table_content (id int not null auto_increment primary key,datum date not null);
INSERT INTO table_content (datum)VALUES ('2022-01-01'),('2022-02-01'),('2022-03-01')

其中mysql 8

WITH CTE AS
(SELECT
  id,
  datum,
  ROW_NUMBER() OVER 
    (ORDER BY datum DESC) AS rn
 FROM table_content
)
SELECT
  id,
  datum,
  rn
FROM CTE;

的rn = 2 对于任何

SET @rn = 1;
SELECT
  id,
  datum,
  rn
FROM
(SELECT
  id,
  datum,
  @rn AS rn,
  @rn := @rn + 1
 FROM table_content
 ORDER BY datum DESC
) tc;

版本结果。 RN = 2是我们想要的。

 id |基准| RN
 - ：| ：---------- | ：：
 3 | 2022-03-01 | 1
 2 | 2022-02-01 | 2
 1 | 2022-01-01 | 3

这给出了您想要的格式。

SET @rn = 1;
SELECT
  CASE 
    WHEN rn = 1 
      THEN "newest_postdate_ever"
    WHEN rn = 2 
      THEN "second_newest_postdate_ever"
    END AS "description",
  datum AS "date"
FROM
(SELECT
  id,
  datum,
  @rn AS rn,
  @rn := @rn + 1
 FROM table_content
 ORDER BY datum DESC
) tc
WHERE rn < 3;

描述|日期      
：----------------------------------- | ：--------------------
newest_postdate_ever | 2022-03-01
second_newest_postdate_ever | 2022-02-01

db＆lt;＆gt;＆gt;

If you are using MySQL version 8 we can use ROW_NUMBER. If it is an earlier version we can do the same thing using a variable. I give you both queries.
Once we have assigned ROW_NUMBER DESC the most recent date has rn=1 and to get the one before we use WHERE rn = 2

CREATE TABLE table_content (id int not null auto_increment primary key,datum date not null);
INSERT INTO table_content (datum)VALUES ('2022-01-01'),('2022-02-01'),('2022-03-01')

For mySQL 8

WITH CTE AS
(SELECT
  id,
  datum,
  ROW_NUMBER() OVER 
    (ORDER BY datum DESC) AS rn
 FROM table_content
)
SELECT
  id,
  datum,
  rn
FROM CTE;

For any version of mySQL

SET @rn = 1;
SELECT
  id,
  datum,
  rn
FROM
(SELECT
  id,
  datum,
  @rn AS rn,
  @rn := @rn + 1
 FROM table_content
 ORDER BY datum DESC
) tc;

The same results. rn = 2 is the one we want.

id | datum      | rn
-: | :--------- | -:
 3 | 2022-03-01 |  1
 2 | 2022-02-01 |  2
 1 | 2022-01-01 |  3

This gives the format you want.

SET @rn = 1;
SELECT
  CASE 
    WHEN rn = 1 
      THEN "newest_postdate_ever"
    WHEN rn = 2 
      THEN "second_newest_postdate_ever"
    END AS "description",
  datum AS "date"
FROM
(SELECT
  id,
  datum,
  @rn AS rn,
  @rn := @rn + 1
 FROM table_content
 ORDER BY datum DESC
) tc
WHERE rn < 3;

description                 | date      
:-------------------------- | :---------
newest_postdate_ever        | 2022-03-01
second_newest_postdate_ever | 2022-02-01

db<>fiddle here

回复收藏 0 原文

~没有更多了~