mysql LEFT JOIN 不像左连接

Question

这是我的问题。我必须在同一查询中获得问题列表及其答案。如果答案早于 30 天，我想得到一个空回复，但我仍然想要问题:

我正在尝试进行左连接，但如果没有匹配项，问题仍然没有出现。您可以在这个 sqlfiddle 中看到模式和我的选择，也可以在这里看到:

http://sqlfiddle.com/#!9/a88184/1

SELECT p.*
     , r.nota
     , r.replied 
  FROM preguntas p 
  LEFT 
  JOIN respuestas r 
    ON p.id = r.id_pregunta 
   AND r.uuid_user ="f6912e4bb23130b9" 
 WHERE r.replied > DATE_SUB(NOW(),INTERVAL 30 DAY) 
   AND p.id_formulario = "1" 
   AND activo ="1" 
 ORDER 
    BY orden ASC

架构

CREATE TABLE `preguntas` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`id_formulario` INT(11) NOT NULL,
`pregunta` TEXT NULL COLLATE 'utf8_spanish_ci',
`opcional` TINYINT(1) NOT NULL DEFAULT '0',
`activo` TINYINT(1) NOT NULL DEFAULT '0',
`orden` INT(11) NOT NULL,
PRIMARY KEY (`id`))

COLLATE='utf8_spanish_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=302
;

CREATE TABLE `respuestas` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`intento` INT(11) NOT NULL DEFAULT '1',
`id_formulario` INT(11) NOT NULL,
`id_pregunta` INT(11) NULL DEFAULT NULL,
`uuid_user` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_spanish_ci',
`nota` INT(11) NULL DEFAULT NULL,
`replied` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`))

COLLATE='utf8_spanish_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=1259;

INSERT INTO `preguntas` (`id`, `id_formulario`, `pregunta`, `opcional`, `activo`, `orden`) VALUES (126, 1, 'INICIATIVA PERSONAL', 0, 1, 1);
INSERT INTO `preguntas` (`id`, `id_formulario`, `pregunta`, `opcional`, `activo`, `orden`) VALUES (139, 1, 'TENER RAPIDEZ Y AGILIDAD', 0, 1, 5);

INSERT INTO `respuestas` (`id`, `intento`, `id_formulario`, `id_pregunta`, `uuid_user`, `nota`, `replied`) VALUES (174, 1, 1, 126, 'f6912e4bb23130b9', 4, '2019-05-23 18:08:15');
INSERT INTO `respuestas` (`id`, `intento`, `id_formulario`, `id_pregunta`, `uuid_user`, `nota`, `replied`) VALUES (175, 1, 1, 139, 'f6912e4bb23130b9', 4, '2019-04-03 18:08:15');

当前结果:

id  id_formulario   pregunta    opcional    activo  orden   nota    replied
126 1   INICIATIVA PERSONAL false   true    1   4   2019-05-23T18:08:15Z

预期结果:

id  id_formulario   pregunta    opcional    activo  orden   nota    replied
126 1   INICIATIVA PERSONAL false   true    1   4   2019-05-23T18:08:15Z
139 1   TENER RAPIDEZ Y AGILIDAD    false   true    5   (empty) (empty)

Answer 1

将左表的列放在 where 子句中有效地将 left join 转换为 inner join。

为防止这种情况，将条件移动到加入:

SELECT p.*, r.nota, r.replied FROM preguntas p 
LEFT JOIN respuestas r ON p.id = r.id_pregunta 
    AND r.uuid_user ="f6912e4bb23130b9" and r.replied > DATE_SUB(NOW(),INTERVAL 30 DAY) 
where p.id_formulario = "1" AND activo ="1" ORDER BY orden ASC

sqlfiddle