mysql - SQL : Query Show date range with if

Question

我在 MySQL 中遇到语法问题...让我向您展示

预期结果

|身份证 |日期            |状态 |
|-----|----------------|----------|
| 2 | | 2014-06-10 | H         |
| 2 | | 2014-06-11 |哦         |
| 2 | | 2014-06-12 | H         |
<强>| 2 | | 2014-06-13 | -          |
| 2 | | 2014-06-14 | H         |

问题
在我的数据库中未找到 Date = 2014-06-13，但我仍希望显示状态为 ' - ' 。

查询已尝试

SELECT id,date,status 
FROM v_absensi 
WHERE site_absensi='IDJK068PBP' 
  AND tanggal != 'Belum Ada' 
  AND DATE_FORMAT( STR_TO_DATE( tanggal ,'%d/%m/%Y' ) ,'%Y/%m/%d' )
      BETWEEN DATE_FORMAT( STR_TO_DATE( '18/06/2014' ,'%d/%m/%Y' ) ,'%Y/%m/%d' )
      AND DATE_FORMAT( STR_TO_DATE( '23/06/2014' ,'%d/%m/%Y' ) ,'%Y/%m/%d' ) 
  AND id='2'

需要你们的建议
感谢您的建议!!!

Answer 1

创建一个像这样的虚拟表。

CREATE TABLE aDummyTable ( id int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (id) );

然后插入一些值:

INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);
INSERT INTO aDummyTable (id) VALUES (NULL);

将此视为您的表格:

CREATE TABLE  urTable (
  id int(10) unsigned NOT NULL auto_increment,
  dateVal date,
  val varchar(10),
   PRIMARY KEY  (id)
);

插入一些值，例如:

INSERT INTO urTable  (id,dateVal,val)VALUES   (1,'2014-06-10','H');
INSERT INTO urTable  (id,dateVal,val)VALUES   (2,'2014-06-11','O');
INSERT INTO urTable  (id,dateVal,val)VALUES   (3,'2014-06-12','H');
INSERT INTO urTable  (id,dateVal,val)VALUES   (4,'2014-06-14','H');

这是您的结果。

SELECT x.ts AS time,
         COALESCE(y.val, '-') AS urVal
     FROM (SELECT DATE_FORMAT(DATE_ADD('2014-06-10', INTERVAL n.id - 1 DAY), '%Y-%m-%d') AS ts
             FROM aDummyTable n
            WHERE DATE_ADD('2014-06-10', INTERVAL n.id - 1 DAY) <= '2014-06-14') x
LEFT JOIN urTable y  ON  DATE_FORMAT(dateVal, '%Y-%m-%d') = x.ts;

Fiddle