union all和order by共用技巧

比如有这么个需求:

select F_command from bfb_db_finance . t_task where F_command like '%5025:%' and F_enabled = 1 limit 1 union all select F_command from bfb_db_finance . t_task where F_command like '%5026:%' and F_enabled = 1 order by F_task_id desc  limit 1 ;

然后报错了:

ERROR 1054 (42S22): Unknown column 'F_task_id' in 'order clause'

解决方案:

select * from (select F_command from bfb_db_finance . t_task where F_command like '%5025:%' and F_command like '% 20161215 %' and F_enabled = 1 order by F_task_id desc limit 1 ) t5025 union all select * from (select F_command from bfb_db_finance . t_task where F_command like '%5026:%' and F_command like '% 20161215 %' and F_enabled = 1 order by F_task_id desc  limit 1 ) t5026 union all select * from (select F_command from bfb_db_finance . t_task where F_command like '%5028:%' and F_command like '% 20161215 %' and F_enabled = 1 order by F_task_id desc  limit 1 ) t5028 union all select * from (select F_command from bfb_db_finance . t_task where F_command like '%5027:%' and F_command like '% 20161215 %' and F_enabled = 1 order by F_task_id desc  limit 1 ) t5027 union all select * from (select F_command from bfb_db_finance . t_task where F_command like '%5036:%' and F_command like '% 20161215 %' and F_enabled = 1 order by F_task_id desc  limit 1 ) t5036;
此条目发表在 mysql 分类目录。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*


*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>