Question

sql怎么输出1-12月数据 并输出1-12月平均值数据？

Answer 1

1.利用临时表补足数据（HANA）
select A.DATAYEAR as DATAYEAR, A.DATAMONTH as DATAMONTH,sum(DATANUM) as DATANUM from (select YEAR(GL_LZ_XKTZ_NY) as DATAYEAR,Month(GL_LZ_XKTZ_NY) as DATAMONTH,count(*) as DATANUMfrom TableNamewhere Year(GL_LZ_XKTZ_NY) >= year(ADD_YEARS(current_date,-1)) and Year(GL_LZ_XKTZ_NY) <= year(current_date)group by YEAR(GL_LZ_XKTZ_NY) , Month(GL_LZ_XKTZ_NY)
union select year(ADD_YEARS(current_date,-1)) as DATAYEAR, 1 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(ADD_YEARS(current_date,-1)) as DATAYEAR, 2 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(ADD_YEARS(current_date,-1)) as DATAYEAR, 3 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(ADD_YEARS(current_date,-1)) as DATAYEAR, 4 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(ADD_YEARS(current_date,-1)) as DATAYEAR, 5 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(ADD_YEARS(current_date,-1)) as DATAYEAR, 6 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(ADD_YEARS(current_date,-1)) as DATAYEAR, 7 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(ADD_YEARS(current_date,-1)) as DATAYEAR, 8 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(ADD_YEARS(current_date,-1)) as DATAYEAR, 9 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(ADD_YEARS(current_date,-1)) as DATAYEAR, 10 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(ADD_YEARS(current_date,-1)) as DATAYEAR, 11 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(ADD_YEARS(current_date,-1)) as DATAYEAR, 12 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(current_date) as DATAYEAR, 1 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(current_date) as DATAYEAR, 2 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(current_date) as DATAYEAR, 3 as DATAMONTH , 0 as DATANUM from DUMMYunion select year(current_date) as DATAYEAR, 4 as DATAMONTH , 0 as DATANUM from DUMMY

Answer 2

查询所有数据然后将数据求平均