【未解决】mysql查询时不能使用别名查询,该怎么解决

2012-04-14

【未解决】mysql查询时不能使用别名查询SQL codemysql selectkeyword0_.id as col_0_0_,keyword0_.content

【未解决】mysql查询时不能使用别名查询

SQL code

mysql> select        keyword0_.id as col_0_0_,        keyword0_.content as col_1_0_,        keyword0_.type as col_2_0_,        keyword0_.alarm_level as col_3_0_,        keyword0_.state as col_4_0_,        (select            user1_.name         from            tbl_user user1_         where            keyword0_.create_user_id=user1_.user_id) as col_5_0_,        keyword0_.create_time as col_6_0_,        keyword0_.home_url as col_7_0_,        (select            keyword2_.content        from            tbl_keyword keyword2_         where            keyword0_.alarm_level=keyword2_.id             and keyword0_.state=keyword2_.state             ) as col_8_0_     from        tbl_keyword keyword0_     where        keyword0_.state='normal'         and keyword0_.type='问题分类子类' and keyword0_.content is not null    order by        keyword0_.id desc limit 20;+----------+--------------+--------------+----------+----------+----------+---------------------+----------+----------+| col_0_0_ | col_1_0_     | col_2_0_     | col_3_0_ | col_4_0_ | col_5_0_ | col_6_0_            | col_7_0_ | col_8_0_ |+----------+--------------+--------------+----------+----------+----------+---------------------+----------+----------+|      283 | dddd         | 问题分类子类 | 116      | normal   | apr-o    | 2012-02-15 09:44:10 |          | 光网升速 ||      282 | 7.7          | 问题分类子类 | 280      | normal   | apr-o    | 2012-02-14 17:07:44 |          | NULL     ||      281 | 2.14         | 问题分类子类 | 280      | normal   | apr-o    | 2012-02-14 17:07:36 |          | NULL     ||      277 | 其他         | 问题分类子类 | 119      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 3G质量   ||      276 | 无线上网问题 | 问题分类子类 | 119      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 3G质量   ||      275 | 信号质量     | 问题分类子类 | 119      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 3G质量   ||      274 | IPTV机顶盒   | 问题分类子类 | 118      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 电信设备 ||      273 | 猫           | 问题分类子类 | 118      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 电信设备 ||      272 | 上网卡       | 问题分类子类 | 112      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 电信套餐 ||      271 | 手机         | 问题分类子类 | 118      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 电信设备 ||      270 | 装维人员服务 | 问题分类子类 | 117      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 服务质量 ||      269 | 营业人员服务 | 问题分类子类 | 117      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 服务质量 ||      268 | 其他         | 问题分类子类 | 116      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 光网升速 ||      267 | 升速异议     | 问题分类子类 | 116      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 光网升速 ||      266 | 光纤到户异议 | 问题分类子类 | 116      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 光网升速 ||      265 | 其他         | 问题分类子类 | 115      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 宽带质量 ||      264 | IPTV业务     | 问题分类子类 | 115      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 宽带质量 ||      263 | 断网         | 问题分类子类 | 115      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 宽带质量 ||      262 | 网速不达标   | 问题分类子类 | 115      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 宽带质量 ||      261 | 签约形式异议 | 问题分类子类 | 114      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 业务办理 |+----------+--------------+--------------+----------+----------+----------+---------------------+----------+----------+

我的查询是想利用别名进行查询，然后去除最后一项为null的数据，求解！

SQL code

mysql> select keyword0_.id as col_0_0_, keyword0_.content as col_1_0_, keyword0_.type as col_2_0_, keyword0_.alarm_level as col_3_0_, keyword0_.state as col_4_0_, (select user1_.name from tbl_user user1_ where keyword0_.create_user_id=user1_.user_id) as col_5_0_, keyword0_.create_time as col_6_0_, keyword0_.home_url as col_7_0_, (select keyword2_.content content_ from tbl_keyword keyword2_ where keyword0_.alarm_level=keyword2_.id and keyword0_.state=keyword2_.state ) as col_8_0_ from tbl_keyword keyword0_ where keyword0_.state='normal' and keyword0_.type='问题分类子类' and keyword0_.col_8_0_.content_ is not null order by keyword0_.id desc limit 20;1054 - Unknown column 'keyword0_.col_8_0_.content_' in 'where clause'

[解决办法]

探讨

引用:

SQL执行顺序是FROM->WHERE
from
tbl_keyword keyword0_ ,
(select
user1_.name
from
tbl_user user1_
where
keyword0_.create_user_id=user1_.u……
那我在不建立视图的情况下，并且也不使用我上面实现的方法下，怎么可以使用别名……

[解决办法]
最简单的方法就是加1个SELECT * FROM ()
2、将(select
user1_.name
from
tbl_user user1_
where
keyword0_.create_user_id=user1_.user_id) as col_5_0_,
keyword0_.create_time as col_6_0_,
keyword0_.home_url as col_7_0_,
(select
keyword2_.content content_
from
tbl_keyword keyword2_
where
keyword0_.alarm_level=keyword2_.id
and keyword0_.state=keyword2_.state
) as col_8_0_
回到 FROM 后面
3、VIEW
[解决办法]
select
keyword0_.id as col_0_0_,
keyword0_.content as col_1_0_,
keyword0_.type as col_2_0_,
keyword0_.alarm_level as col_3_0_,
keyword0_.state as col_4_0_,
(select
user1_.name
from
tbl_user user1_
where
keyword0_.create_user_id=user1_.user_id) as col_5_0_,
keyword0_.create_time as col_6_0_,
keyword0_.home_url as col_7_0_,
(select
keyword2_.content
from
tbl_keyword keyword2_
where
keyword0_.alarm_level=keyword2_.id
and keyword0_.state=keyword2_.state
) as col_8_0_
from
tbl_keyword keyword0_
where
keyword0_.state='normal'
and keyword0_.type='问题分类子类'
and exists (select 1 from tbl_keyword where keyword0_.alarm_level=id and keyword0_.state=state )
order by
keyword0_.id desc limit 20;

热点排行

Mysql

【未解决】mysql查询时不能使用别名查询,该怎么解决