现有如下表结构及数据
F_CLUBID_CRAT F_CLUBNAME_CRAT F_AREACODE_CRAT F_COUNT_CRAT F_TIME_CRAT
1 网吧1 210200 1 20071211000000
2 网吧2 210100 1 20071211000000
1 网吧1 210200 1 20071212000000
1 网吧1 210200 1 20071213000000
1 网吧1 210200 1 20071217000000
2 网吧2 210100 3 20080101000000
通过
select substring(tc.f_time_crat,1,4) gtyear,f_areacode_crat,sum(f_count_crat) cts from t_crat_lnfcs tc
where
substring(tc.f_time_crat,1,4)>='2007' and substring(tc.f_time_crat,1,4)<='2008'
and f_areacode_crat='210200' or f_areacode_crat='210400'
group by f_areacode_crat,substring(tc.f_time_crat,1,4)查询结果如下
2007 210200 4 现在想让f_areacode_crat='210400'的数据以结果0的形式显示出来,请问SQL语句如何实现,想得到下面这样的显示结果
2007 210200 4
2008 210200 0
2007 210400 0
2008 210400 0求求帮忙
F_CLUBID_CRAT F_CLUBNAME_CRAT F_AREACODE_CRAT F_COUNT_CRAT F_TIME_CRAT
1 网吧1 210200 1 20071211000000
2 网吧2 210100 1 20071211000000
1 网吧1 210200 1 20071212000000
1 网吧1 210200 1 20071213000000
1 网吧1 210200 1 20071217000000
2 网吧2 210100 3 20080101000000
通过
select substring(tc.f_time_crat,1,4) gtyear,f_areacode_crat,sum(f_count_crat) cts from t_crat_lnfcs tc
where
substring(tc.f_time_crat,1,4)>='2007' and substring(tc.f_time_crat,1,4)<='2008'
and f_areacode_crat='210200' or f_areacode_crat='210400'
group by f_areacode_crat,substring(tc.f_time_crat,1,4)查询结果如下
2007 210200 4 现在想让f_areacode_crat='210400'的数据以结果0的形式显示出来,请问SQL语句如何实现,想得到下面这样的显示结果
2007 210200 4
2008 210200 0
2007 210400 0
2008 210400 0求求帮忙
F_CLUBID_CRAT F_CLUBNAME_CRAT F_AREACODE_CRAT F_COUNT_CRAT F_TIME_CRAT
1 网吧1 210200 1 20071211000000
2 网吧2 210100 1 20071211000000
1 网吧1 210200 1 20071212000000
1 网吧1 210200 1 20071213000000
1 网吧1 210200 1 20071217000000
2 网吧2 210100 3 20080101000000
(
select 2007 year , 210200 F_AREACODE_CRAT
union select 2008 , 210200
union select 2007 , 210400
union select 2008 , 210400
) t left join
(你写那个查询) m
on t.year = m.year and t.F_AREACODE_CRAT = m.....
2007
2008然后去left join
自己做个子表,然后左连接select t.* , isnull(cts ,0) cts from
(
select 2007 year , 210200 F_AREACODE_CRAT
union select 2008 , 210200
union select 2007 , 210400
union select 2008 , 210400
) t left join
(
select substring(tc.f_time_crat,1,4) gtyear,f_areacode_crat,sum(f_count_crat) cts from t_crat_lnfcs tc
where
substring(tc.f_time_crat,1,4)> ='2007' and substring(tc.f_time_crat,1,4) <='2008'
and f_areacode_crat='210200' or f_areacode_crat='210400'
group by f_areacode_crat,substring(tc.f_time_crat,1,4)
) m
on t.year = m.gtyear and t.F_AREACODE_CRAT = m.f_areacode_crat
建议从那个表中取f_areacode_crat列表。