根据到期时间获取行

http://sqlfiddle.com/#!9/406cb/2 http://sqlfiddle.com/#!9/406cb/2

我有一本分类为 1、2、3、4 的表格书。

• 第 1 类 - 将停留 1 小时
• 第 2 类 - 将停留 2 小时
• 第 3 类 - 将停留 4 小时
• 第 4 类 - 将停留 6 小时

如何编写查询以获得所需的结果？

E.g.:

1. 如果当前时间是 1454411248 那么它应该显示所有书籍
2. 如果当前时间是 1454414847 那么它不应该显示 unixtime=1454411248 的类别 1 书籍（一小时后过期）
3. 如果当前时间是 1454418448，则到期时间 1、2、4、6 小时以下的类别 - 1、2、3、4 不应显示（unix_time将是动态的。例如，我使用了静态值）。

so on..

Table:

CREATE TABLE `books` (
    `id` int(255) NOT NULL AUTO_INCREMENT,
    `name` longtext NOT NULL,
    `category` varchar(255) NOT NULL,
    `unix_time` bigint(20) NOT NULL,
    `time_data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;


INSERT INTO `books` (`id`, `name`, `category`, `unix_time`, `time_data`) VALUES
    (1, 'book1', '1', 1454411248, '2016-02-02 05:37:28'),
    (2, 'book2', '2', 1454411248, '2016-02-02 05:37:28'),
    (3, 'book3', '3', 1454411248, '2016-02-02 05:37:28'),
    (4, 'book4', '4', 1454411248, '2016-02-02 05:37:28'),
    (5, 'book5', '1', 1454411248, '2016-02-02 05:37:28'),
    (6, 'book6', '2', 1454411248, '2016-02-02 05:37:28'),
    (7, 'book7', '3', 1454411248, '2016-02-02 05:37:28'),
    (8, 'book8', '4', 1454411248, '2016-02-02 05:37:28'),
    (9, 'book9', '1', 1454497648, '2016-02-03 05:37:28'),
    (10, 'book10', '2', 1454497648, '2016-02-03 05:37:28'),
    (11, 'book11', '1', 1454497648, '2016-02-03 05:37:28'),
    (12, 'book12', '2', 1454497648, '2016-02-03 05:37:28'),
    (13, 'book13', '3', 1454497648, '2016-02-03 05:37:28'),
    (14, 'book14', '4', 1454497648, '2016-02-03 05:37:28'),
    (15, 'book15', '1', 1454497648, '2016-02-03 05:37:28'),
    (16, 'book16', '2', 1454497648, '2016-02-03 05:37:28'),
    (17, 'book17', '3', 1454497648, '2016-02-03 05:37:28'),
    (18, 'book18', '4', 1454497648, '2016-02-03 05:37:28');

Query:

SELECT *,
    CASE category
        WHEN '1' THEN '1454407648'
        WHEN '2' THEN '1454404048'
        WHEN '3' THEN '1454396848'
        WHEN '4' THEN '1454389648'
    END as category
from books
where unix_time >1454411248

首先，您需要一个表格来关联您的category对你的价值观duration价值观。您可以创建一个物理表，也可以使用这样的子查询来生成虚拟表。

   select 1 as category, 1 as duration UNION ALL
   select 2,2 UNION ALL select 3,4 UNION ALL select 4,6

这会产生以下小查找表。

|| *category* || *duration* ||
||          1 ||          1 ||
||          2 ||          2 ||
||          3 ||          4 ||
||          4 ||          6 ||

接下来，您需要将该查找表加入到您的books表，就像这样。该查询还可以包括expiration列，如图所示。

select b.*,
       d.duration,
       FROM_UNIXTIME(unix_time) + INTERVAL d.duration HOUR expiration
  from books b
  join (select 1 as category, 1 as duration UNION ALL
        select 2,2 UNION ALL select 3,4 UNION ALL select 4,6
         ) d ON b.category = d.category

最后，您附加一个WHERE子句过滤掉未过期的行。

select b.*,
          d.duration,
         FROM_UNIXTIME(unix_time) + INTERVAL d.duration HOUR expiration
  from books b
  join (select 1 as category, 1 as duration UNION ALL
        select 2,2 UNION ALL select 3,4 UNION ALL select 4,6
         ) d ON b.category = d.category
  where FROM_UNIXTIME(unix_time) + INTERVAL d.duration HOUR >= '2016-02-03 08:00:00'

在这个例子中我使用了2016-02-03 08:00:00作为当前时间。您可以在生产系统中使用NOW().

你也可以聪明地使用sargable https://en.wikipedia.org/wiki/Sargable的版本where条款。

   where unix_time >= UNIX_TIMESTAMP('2016-02-03 08:00:00' - INTERVAL d.duration HOUR)

最后，这是一个设计偏好，但混合DATETIME和 unix 时间戳列有点奇怪。