如何按月并补充没数据的月显示内容,谢谢!!!
表a
id
value
month
表a数据
id value month
1 2 1
2 2 2
3 3 3
希望得到的结果
id value month
1 2 1
2 2 2
3 3 3
null null 4
null null 5
null null 6
null null 7
null null 8
null null 9
null null 10
null null 11
null null 12
------解决方案--------------------select id,value,month from test
union all select null,null,4
union all select null,null,5
union all select null,null,6
union all select null,null,7
union all select null,null,8
union all select null,null,9
union all select null,null,10
union all select null,null,11
union all select null,null,12
------解决方案--------------------select A.id,A.value,A.month
from test A right join
(select 1 as 月份
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
) B on B.月份=A.A.month
------解决方案--------------------建个临时表,一个字段,内容为12个月,用这个表跟表a left join 条件是=a.month