oracle常见问题

  1. oracle在做SELECT查询时,表名后面不可以用AS取别名,直接"[表名] [表别名]"就行了,字段后面可以跟AS。

    -- 表名取别名
        SELECT UUID
        FROM TABLE_NAME AS aabb;    -- error; SQL command not properly ended
        
        -- 字段取别名
        SELECT UUID AS UUID
        FROM TABLE_NAME;    -- ok
  2. oracle在用GROUP BY分组时,SELECT要查询的字段只能是要分组的字段,聚合函数除外。

    SELECT VEHICLENO, UUID
        FROM GPS20190814
        GROUP BY VEHICLENO;    -- error; not a GROUP BY expression
    
        SELECT VEHICLENO, MAX(UUID)    // MAX为取最大值
        FROM GPS20190814
        GROUP BY VEHICLENO;    -- ok
  3. oracle在用SELECT查询时,必须指定表名,不能直接在查询SELECT后的计算结果。

    SELECT 2 / 1;    -- error; FROM keyword not found where expected
        -- ROWNUM指定返回第几行,如果不指定,将会返回多条数据
        SELECT 2 / 1
        FROM TABLE_NAME
        WHERE ROWNUM = 1;    -- ok

2019-8-14