用于选择日期的mysql查询 [英] mysql query for selecting date

问题描述

您好，



我希望从日期开始记录。如果任何特定日期，记录不可用，则日期必须带有空数据。



例如。

Hello,

i want record from table by date. if any particular date, record is not available then date must come with null data.

eg.

  date--------------id---------name
2015/09/01-----------1---------abc1
2015/09/02-----------2---------abc2
2015/09/04-----------3---------abc3
2015/09/06-----------4---------abc4

我想要这样的东西。

and i want something like this.

  date--------------id---------name
2015/09/01-----------1---------abc1
2015/09/02-----------2---------abc2
2015/09/03---------null--------null
2015/09/04-----------3---------abc3
2015/09/05---------null--------null
2015/09/06-----------4---------abc4

Thnaks In Advance ..

Thnaks In Advance..

推荐答案

不幸的是，在MySQL中生成序列并不像在MSSQL中那样容易。



但是，我通过 logan5 at SO [ ^ ]



这是我对你的表的解释：

Unfortunately generating sequences in MySQL is not quite as easy as it is in MSSQL.

However, I adapted a solution by logan5 at SO[^]

This is my interpretation of your table:

CREATE TABLE your_table
(
  datecol DATE,
  id INT,
  namecol VARCHAR(255)
);
INSERT INTO your_table (datecol, id, namecol) VALUES('2015/09/01',1,'abc1');
INSERT INTO your_table (datecol, id, namecol) VALUES('2015/09/02',2,'abc2');
INSERT INTO your_table (datecol, id, namecol) VALUES('2015/09/04',3,'abc3');
INSERT INTO your_table (datecol, id, namecol) VALUES('2015/09/06',4,'abc4');

我创建了一个工作表

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

哪个我然后在此查询中使用：

Which I then used in this query:

select adddate('2015-09-01', numlist.id) as `date` , y.id, y.namecol
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
LEFT OUTER JOIN your_table y on y.datecol = adddate('2015-09-01', numlist.id)
where adddate('2015-09-01', numlist.id) <= '2015-09-30';

它产生你想要的结果。



如果您打算经常这样做，可能值得建立一个永久的日期表

It produces the results you are after.

If you are going to do this often it is probably worth setting up a permanent table of dates

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