就这视图,用视图名称查结果为空,用下面的语句查结果正常。将下面的查询语句起一新视图名创建,又能查出结果。
create view v_jh_ringing_ycIndex
as
select '下载同歌三次以上' as 'ycIndexName',left(lie6*100,INSTR(lie6*100,'.')+2) as 'ycValue' from jh_ringing where city = convert('全省' using gbk)
union
select '下载无流量' as 'ycIndexName',left(lie10*100,INSTR(lie10*100,'.')+2) as 'ycValue' from jh_ringing where city = convert('全省' using gbk)
union
select '无效用户' as 'ycIndexName',left(lie14*100,INSTR(lie14*100,'.')+2) as 'ycValue' from jh_ringing where city = convert('全省' using gbk)
union
select '下载同CP歌曲+振铃15次以上' as 'ycIndexName',left(lie18*100,INSTR(lie18*100,'.')+2) as 'ycValue' from jh_ringing where city = convert('全省' using gbk);
这个视图使用上面的视图
................
UNION
select
'振铃' as 'busName',
lie22 as 'bili',
(select CONCAT(ycindexName,concat('--',ycValue,'%')) from v_jh_ringing_ycIndex order by ycValue desc limit 1) as 'ycIndex',
(select GROUP_CONCAT(city) as 'city' from v_jh_ringing_city where qujian = 1 group by qujian) as 'Exceed10',
(select GROUP_CONCAT(city) as 'city' from v_jh_ringing_city where qujian = 2 group by qujian) as 'Between10and8',
(select GROUP_CONCAT(city) as 'city' from v_jh_ringing_city where qujian = 3 group by qujian) as 'NotExcessive'
from
jh_ringing
where
city=convert('全省' using gbk);