mysql选择没有表的日期 [英] mysql select dates without tables

问题描述

我只需要返回一个月内所有天的列表.我没有访问特定的表.因此，如果给定2月，则需要一条sql select语句，返回以下内容:

I simply need to return a list of all days within a month. I'm not accessing a specific table. So I need a sql select statement if given the month of February, return the following:

Day
----
2011-02-01
2011-02-02
2011-02-03
... etc, etc. 
2011-02-27
2011-02-28

如果我知道我需要做的sql select语句，我想这应该很简单.该月份应该是可以选择的，我将从一个网页选择框中传递该信息，让用户选择月份和年份.这将成为报告的基础.我已经有一条select语句，该语句将花费每一天的时间，并对与这些日期相关的记录进行计数，但是我需要此表作为报告的基础.

This should be fairly simple, i would think, if I knew the sql select statement I need to make. The month should be selectable, I'll pass that in from a webpage select box which lets the user select the month and year. This is going to be the basis of a report. I already have a select statement that will take each of the days and give counts on records related to those dates, but I need this table as a basis of my reports.

推荐答案

我同意这些意见，即不应在数据库中进行类似操作，但从技术上讲是可能的. 如果您指定开始日期和结束日期，则在必要时向子查询添加其他数字:

I agree with the comments, that something like this shouldn't be done in the database, but technically its possible. If you give the start and end date, adding additional numbers to the subquery if necessary:

SELECT '2011-02-01' + INTERVAL a + b DAY dte
FROM
 (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
    UNION SELECT 8 UNION SELECT 9 ) d,
 (SELECT 0 b UNION SELECT 10 UNION SELECT 20 
    UNION SELECT 30 UNION SELECT 40) m
WHERE '2011-02-01' + INTERVAL a + b DAY  <  '2011-03-01'
ORDER BY a + b

结果:

"2011-02-01"
"2011-02-02"
"2011-02-03"
....
"2011-02-28"

这篇关于mysql选择没有表的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！