mysql - 按日期加入查询

Question

请有人帮助我。

我需要加入这两个查询并按小时分组。查询结果可以有很多天。

结果:Query1

SELECT DATE_FORMAT( Venta.fechaImpresion,  "%h" ) AS fechaImpresion2, COUNT( * ) AS total
FROM  `Venta` 
WHERE Venta.fechaImpresion LIKE  "2016-05-02 %"
GROUP BY HOUR( Venta.fechaImpresion ) , DAY( Venta.fechaImpresion ) 
LIMIT 0 , 30

结果:Query2

SELECT DATE_FORMAT(Venta.fechaImpresion,"%h") as fechaImpresion FROM `Venta`
where Venta.fechaImpresion >= "2016-05-02" and Venta.fechaImpresion <= "2016-05-09"  
 GROUP BY hour( Venta.fechaImpresion) 

如果我尝试加入一个查询，结果为零。

SELECT DATE_FORMAT( Venta.fechaImpresion,  "%h" ) AS fecha, dia1.total AS total1
FROM  `Venta` 
LEFT JOIN (

SELECT DATE_FORMAT( Venta.fechaImpresion,  "%h" ) AS fechaImpresion
FROM  `Venta` 
WHERE Venta.fechaImpresion >=  "2016-05-02"
AND Venta.fechaImpresion <=  "2016-05-09"
GROUP BY HOUR( Venta.fechaImpresion )
)fecha ON fecha.fechaImpresion = Venta.fechaImpresion
LEFT JOIN (

SELECT DATE_FORMAT( Venta.fechaImpresion,  "%h" ) AS fechaImpresion2, COUNT( * ) AS total
FROM  `Venta` 
WHERE Venta.fechaImpresion LIKE  "2016-05-02 %"
GROUP BY HOUR( Venta.fechaImpresion ) , DAY( Venta.fechaImpresion )
)dia1 ON ( dia1.fechaImpresion2 = fecha.fechaImpresion ) 
WHERE Venta.fechaImpresion >=  "2016-05-02"
AND Venta.fechaImpresion <=  "2016-05-09"
GROUP BY HOUR( Venta.fechaImpresion ) 

Answer 1

你可以这样试试

从第一个表中选择b.second表列，a.*，b第二个表中a.date列=b.date列