mysql order by和union的联合使用
时间：2007-12-25 来源：boean
今天在做一个统计报表的时候用到了order by desc排序，结果发现它不能和 union一起使用
Error Code : 1054
Unknown column 't2.count_vod' in 'order clause' 原句： select 'a week upload',count(*) from update_table where date_format(time,'%Y%m%d') = date_format(DATE_ADD(now(),INTERVAL -7 DAY),'%Y%m%d')
union select 'a week upload title',t1.title,t2.user_count from update_table as t1 ,update_user as t2 where where date_format(time,'%Y%m%d') = date_format(DATE_ADD(now(),INTERVAL -7 DAY),'%Y%m%d') and t1.user_count=t2.user_count order by t1.user_count desc; 利用临时表来解决这个问题： select 'a week upload',count(*) from update_table where date_format(time,'%Y%m%d') = date_format(DATE_ADD(now(),INTERVAL -7 DAY),'%Y%m%d')
union select * from (select 'a week upload title',t1.title,t2.user_count from update_table as t1 ,update_user as t2 where where date_format(time,'%Y%m%d') = date_format(DATE_ADD(now(),INTERVAL -7 DAY),'%Y%m%d') and t1.user_count=t2.user_count order by t1.user_count desc) as temp; 如果有多个查询结果需要放在一起可以用类似的方式解决
Unknown column 't2.count_vod' in 'order clause' 原句： select 'a week upload',count(*) from update_table where date_format(time,'%Y%m%d') = date_format(DATE_ADD(now(),INTERVAL -7 DAY),'%Y%m%d')
union select 'a week upload title',t1.title,t2.user_count from update_table as t1 ,update_user as t2 where where date_format(time,'%Y%m%d') = date_format(DATE_ADD(now(),INTERVAL -7 DAY),'%Y%m%d') and t1.user_count=t2.user_count order by t1.user_count desc; 利用临时表来解决这个问题： select 'a week upload',count(*) from update_table where date_format(time,'%Y%m%d') = date_format(DATE_ADD(now(),INTERVAL -7 DAY),'%Y%m%d')
union select * from (select 'a week upload title',t1.title,t2.user_count from update_table as t1 ,update_user as t2 where where date_format(time,'%Y%m%d') = date_format(DATE_ADD(now(),INTERVAL -7 DAY),'%Y%m%d') and t1.user_count=t2.user_count order by t1.user_count desc) as temp; 如果有多个查询结果需要放在一起可以用类似的方式解决
相关阅读 更多 +
