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

已找到解决方案，不过不是很好，但是很实用，求更好的解决方案！mysql> select * from(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='问题分类子类'
    order by
        keyword0_.id desc) tk where tk.col_8_0_ is not null 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 |          | 光网升速 |
|      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     | 业务办理 |
|      260 | 业务办理要求异议 | 问题分类子类 | 114      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 业务办理 |
|      259 | 维修异议         | 问题分类子类 | 113      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 安装维修 |
+----------+------------------+--------------+----------+----------+----------+---------------------+----------+----------+
20 rows in set

解决方案 »

  1.   

    SQL执行顺序是FROM->WHERE
    from
            tbl_keyword keyword0_ ,
    (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_ OR
    将from
            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 
                 存为VIEW，再与工作表连接
      

  2.   

    那我在不建立视图的情况下，并且也不使用我上面实现的方法下，怎么可以使用别名进行where条件查询呢？
      

  3.   

    2楼已经提示
    from
       tbl_keyword keyword0_ ,
    (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_ 
      

  4.   


    可否帮我利用别名查询最后一项不为null值的数据！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='问题分类子类'
        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_ |
    +----------+------------------+--------------+----------+----------+----------+---------------------+----------+----------+
    |      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     | 业务办理 |
    |      260 | 业务办理要求异议 | 问题分类子类 | 114      | normal   | Happy    | 2012-02-13 11:12:59 | NULL     | 业务办理 |
    +----------+------------------+--------------+----------+----------+----------+---------------------+----------+----------+
    20 rows in set
      

  5.   

    最简单的方法就是 加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
      

  6.   

    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;
      

  7.   


    别名在MYSQL中是后产生的，无法在WHERE中直接使用。 但其它有些数据库好像可以。
      

  8.   

    那你的那句话（标红色的）是什么意思呢，我还没有看懂，我本来是想用别名判断不为null的，但是数据库说它不认识那个字段！
      

  9.   

    select name as name1 from table1 having (name1='aaa')
    我也刚刚在查这个问题，在另一个帖子上看到的