select
date_format(qar.create_date, '%y-%m-%d') as qarcreatedate,
(case when qar.create_date > 0 then qar.id
when qar.create_date = "null" then 0 end
) as qarid
from
xx_qrcode q
left join xx_qrcode_access_record qar on qar.qrcode_id = q.id
right join xx_product p on q.product_id = p.id
where
p.stores is null
and qar.create_date between '2015-05-29' and '2015-06-05'
group by qar.create_date结果:
qarCreateDate qarid
2015-05-29 0
2015-05-30 0
2015-05-31 0
2015-06-01 02015-06-02 267
2015-06-03 268
2015-06-03 269
2015-06-04 270
2015-06-04 271,05-29—06-01是没查出来
请问怎么将2015-05-29,2015-05-30,2015-05-31和2015-06-01 这些不存在的也统计出来。非常感谢各位的指点!
date_format(qar.create_date, '%y-%m-%d') as qarcreatedate,
(case when qar.create_date > 0 then qar.id
when qar.create_date = "null" then 0 end
) as qarid
from
xx_qrcode q
left join xx_qrcode_access_record qar on qar.qrcode_id = q.id
right join xx_product p on q.product_id = p.id
where
p.stores is null
and qar.create_date between '2015-05-29' and '2015-06-05'
group by qar.create_date结果:
qarCreateDate qarid
2015-05-29 0
2015-05-30 0
2015-05-31 0
2015-06-01 02015-06-02 267
2015-06-03 268
2015-06-03 269
2015-06-04 270
2015-06-04 271,05-29—06-01是没查出来
请问怎么将2015-05-29,2015-05-30,2015-05-31和2015-06-01 这些不存在的也统计出来。非常感谢各位的指点!
LEFT JOIN `secumaster` b ON a.trday=b.`tradingday` -- 只有一天的数据这张表
WHERE trday BETWEEN 20170401 AND 20170430
GROUP BY trday
LEFT JOIN `secumaster` b ON a.trday=b.`tradingday` -- 只有一天的数据这张表
WHERE trday BETWEEN 20170401 AND 20170430
GROUP BY trday
(
@begin_date datetime,
@end_date datetime
)
returns @t table(date datetime)
as
begin
with maco as
(
select @begin_date AS date
union all
select date+1 from maco
where date+1 <=@end_date
)
insert into @t
select * from maco option(maxrecursion 0);
return
end
go这是函数
select * from dbo.generateTime('2009-01-01','2009-01-10')调用方法
2009-01-01 00:00:00.000
2009-01-02 00:00:00.000
2009-01-03 00:00:00.000
2009-01-04 00:00:00.000
2009-01-05 00:00:00.000
2009-01-06 00:00:00.000
2009-01-07 00:00:00.000
2009-01-08 00:00:00.000
2009-01-09 00:00:00.000
2009-01-10 00:00:00.000结果,你可以left join
Note: MySQL Installer is 32 bit, but will install both 32 bit and 64 bit binaries.