oracle数据库分组查询group by

1、普通分组group by查询

SELECT

        "CITY_GDDWBM" AS "citygddwbm",

        "AREA_GDDWBM" AS "areagddwbm",

        "GDJMC" AS "gdjmc"

FROM

        SMBH_IGS_YYTXX

WHERE

        "CITY_GDDWBM"='0501'

GROUP BY

        "AREA_GDDWBM","CITY_GDDWBM","GDJMC"

ORDER BY

        "AREA_GDDWBM" ASC

这种group by分组查询只能让3个字段去重，多了字段的话去重无效果

2、特殊group by分组查询

select

        "YYTTYBM" AS "yyttybm",

        "CITY_GDDWBM" AS "citygddwbm",

        "AREA_GDDWBM" AS "areagddwbm",

        "GDJMC" AS "gdjmc"

from SMBH_IGS_YYTXX where ROWID IN (

        SELECT MIN(ROWID) FROM SMBH_IGS_YYTXX t GROUP BY t.AREA_GDDWBM

) AND "CITY_GDDWBM"='0501'

这种group by分组查询能让多个字段去重

3、window的oracle数据库添加用户并授予权限

(1)使用sqlplus进图oracle数据库

sqlplus / as sysdba

(2)创建新用户

create user yyzj identified by "123456";

(3)授予权限

grant create session to yyzj;

grant create table to yyzj;

grant create tablespace to yyzj;

grant create view to yyzj;

grant dba to yyzj;

4、Oracle数据库查看和添加索引

查看索引：select index_name from all_indexes where table_name = 'STRUCTURED_ADDRESS';

添加索引：CREATE INDEX IDX_ADDRESS_CODE ON STRUCTURED_ADDRESS(ADDRESS_CODE);