gpt4 book ai didi

mysql - 从 MySQL 获取更新和更旧的帖子,但至少有一定数量

转载 作者:行者123 更新时间:2023-11-29 00:30:16 26 4
gpt4 key购买 nike

我有这样一个数据库:

+------------+-------------+
| listed | data |
+------------+-------------+
| 2013-01-01 | random text |
| 2013-01-02 | random text |
| 2013-01-03 | random text |
| 2013-01-05 | random text |
| 2013-01-06 | random text |
| 2013-01-07 | random text |
+------------+-------------+

在这种情况下,“数据”是笑话的标题。我想在当前笑话旁边列出旧笑话和新笑话。不是每天都有笑话。如果没有新的,我只想要旧的,反之亦然......

所以:

   for 2013-01-02 I want 2013-01-01 and 2013-01-03
for 2013-01-03 I want 2013-01-02 and 2013-01-05
for 2013-01-07 I want 2013-01-05 and 2013-01-06
for 2013-01-01 I want 2013-01-02 and 2013-01-03

我可以在两个查询中执行此操作,在另一个查询不返回任何内容的情况下至少获得 2 个笑话:

SELECT * FROM jokes WHERE listed>'$date' ORDER BY listed ASC limit 2
SELECT * FROM jokes WHERE listed<'$date' ORDER BY listed DESC limit 2

然后对数组的长度进行数学运算,但我想知道在单个查询中是否有正确的方法来执行此操作?

最佳答案

检查 SQL Fiddle here.I have posted solution that very large query.But I think someone can decrease its size.

SELECT * FROM (SELECT 
listed,DATA,@r2 := @r2 + 1 AS num
FROM
jokes,
(SELECT @r2:=0) AS e) t WHERE FIND_IN_SET(num,(SELECT FOUND FROM (SELECT
listed,`data`,@rn := @rn + 1 AS number,
IF(listed = '2013-01-07',#pass your date here
IF(@rn = 1,CONCAT(2,',',3),
IF(@rn = (SELECT COUNT(*) FROM jokes),CONCAT(@rn-1,',',@rn-2),CONCAT(@rn-1,',',@rn+1))) ,-1)
AS `found`
FROM jokes,(SELECT @rn := 0 ) r

ORDER BY listed ) AS k WHERE `found` != -1))>0

关于mysql - 从 MySQL 获取更新和更旧的帖子,但至少有一定数量,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16936611/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com