时间戳

首页 > 解决方案 > 显示没有表格的日期范围

jquery - 显示没有表格的日期范围

问题描述

是否可以在没有表的情况下在 mysql 中显示日期范围？我有这个代码，但它缺乏。

SET @startDate = '2018-10-01';
SET @endDate = '2019-02-21';

select @startDate + INTERVAL seq.seq DAY AS sequential_day
  from (
    SELECT A.N + 5*(B.N + 5*(C.N + 5*(D.N + 5*(E.N + 5*(F.N))))) AS seq
      FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS E
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS F
       ) AS seq
 where seq.seq <= @endDate

它显示从2018-10-01到2024-04-11？我的查询有问题，知道吗？我想显示从2018-10-01到2019-02-21

我在用着mariaDB

标签： jquerymysqlsqlmariadb

解决方案

您可以像这样使用SEQUENCE引擎：

select CURDATE() + INTERVAL seq day  FROM seq_1_to_31;

这是完整的示例：

SELECT '2018-10-01' + interval seq day as my_date FROM seq_0_to_9999
WHERE '2018-10-01' + interval seq day  <= '2019-02-21';

您可以在此处找到详细信息：https ://mariadb.com/kb/en/library/sequence-storage-engine/

查看安装了哪些引擎

MariaDB [test]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.006 sec)

MariaDB [test]>

样本

MariaDB [test]> select seq FROM seq_1_to_4;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
+-----+
4 rows in set (0.042 sec)

MariaDB [test]> select CURDATE() + INTERVAL seq day  FROM seq_1_to_31;
+--------------------------------+
| DATE(NOW()) + INTERVAL seq day |
+--------------------------------+
| 2019-02-23                     |
| 2019-02-24                     |
| 2019-02-25                     |
| 2019-02-26                     |
| 2019-02-27                     |
| 2019-02-28                     |
| 2019-03-01                     |
| 2019-03-02                     |
| 2019-03-03                     |
| 2019-03-04                     |
| 2019-03-05                     |
| 2019-03-06                     |
| 2019-03-07                     |
| 2019-03-08                     |
| 2019-03-09                     |
| 2019-03-10                     |
| 2019-03-11                     |
| 2019-03-12                     |
...
...
| 2019-03-22                     |
| 2019-03-23                     |
| 2019-03-24                     |
| 2019-03-25                     |
+--------------------------------+
31 rows in set (0.019 sec)

MariaDB [test]>

推荐阅读