當我下指令
select years, casecnt
from tablename
where CompanyName = 'A公司'
and Years between 2015 and 2019
結果是
years | casecnt
2018 | 3
2016 | 4
2015 | 7
但我希望能顯示結果如下
years | casecnt
2019 | 0
2018 | 3
2017 | 0
2016 | 4
2015 | 7
解決方法
1.新增一個Years的table,
2.
select YearsTable.Years, IsNull(tableA.CaseCnt,0) as CaseCnt
from YearsTable
left join tableA
on tableA.Years = YearsTable.Years
and tableA.CompanyName = 'A公司'
where (YearsTable.years between 2015 and 2019)
order by YearsTable.Years
