oracle中有个行政区划表和用户表关联,怎么统计出各级行政中注册用户?
oracle中有个行政区划表和用户表关联,怎么统计出各级行政中注册用户?
/**
*用户表
*/
CREATE TABLE "SXCTDB"."OS_SYSTEM_USER"
( "VC_USER_NAME" NVARCHAR2(10) NOT NULL ENABLE,
"VC_LOGIN_NAME" NVARCHAR2(255),
"VC_REAL_NAME" NVARCHAR2(255),
"INT_SEX" NUMBER(*,0),
"VC_PWD" VARCHAR2(50 BYTE),
"INT_STRUTS" NUMBER(*,0),
"VC_PHONE" NVARCHAR2(15),
"BL_PHONE_VERIFY" CHAR(1 BYTE),
"DT_BIRTHDAY" DATE,
"DT_LAST_LOGIN_TIME" TIMESTAMP (6),
"VC_LAST_LOGIN_IP" NVARCHAR2(32),
"LONG_LOGIN_COUNT" LONG,
"INT_ROLE" NUMBER(*,0),
"BL_PERFECT_DATA" CHAR(1 BYTE),
"VC_EMAIL" NVARCHAR2(255),
"DT_REGEDIT_TIME" TIMESTAMP (6),
"VC_RECOMMEND_USER" NVARCHAR2(10),
"BL_PASS_VERIFY" CHAR(1 BYTE),
"VC_PROVINCE" VARCHAR2(32 BYTE),
"VC_CITY" VARCHAR2(32 BYTE),
"VC_AREA" VARCHAR2(32 BYTE),
"VC_STREET" VARCHAR2(32 BYTE),
"VC_ADDRESS_ALL" NVARCHAR2(255),
"VC_ADDRESS" NVARCHAR2(255),
"BL_UPDATE_PWD" CHAR(1 BYTE),
"VC_VILLAGE" VARCHAR2(32 BYTE),
"VC_INNER_EMPLOYEE_FLAG" CHAR(1 BYTE) DEFAULT 1,
"VC_UUID" NVARCHAR2(32) DEFAULT sys_guid(),
"VC_MACHINE_ID" VARCHAR2(255 BYTE),
"VC_REG_FROM" CHAR(1 BYTE),
"INT_YTHFW_USER" CHAR(1 BYTE),
"VC_REG_DOCTOR_ACCOUNT" NVARCHAR2(10),
CONSTRAINT "PK_OS_SYSTEM_USER" PRIMARY KEY ("VC_USER_NAME")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JISHIJIAKANG" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JISHIJIAKANG" ;
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_USER_NAME" IS '用户名';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_LOGIN_NAME" IS '用户登陆名';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_REAL_NAME" IS '用户真实姓名';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."INT_SEX" IS '性别,1男,2女';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_PWD" IS '用户密码';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."INT_STRUTS" IS '用户状态,1正常启用 2 正常停用 3 黑名单';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_PHONE" IS '手机号';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."BL_PHONE_VERIFY" IS '手机号是否验证';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."DT_BIRTHDAY" IS '出生日期';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."DT_LAST_LOGIN_TIME" IS '最后登陆时间';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_LAST_LOGIN_IP" IS '最后登陆IP';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."LONG_LOGIN_COUNT" IS '登陆次数';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."INT_ROLE" IS '用户角色,1 普通会员 2 医生';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."BL_PERFECT_DATA" IS '是否完善用户信息 1 完善 0 未完善';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_EMAIL" IS '邮箱';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."DT_REGEDIT_TIME" IS '注册时间';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_RECOMMEND_USER" IS '推荐人员';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."BL_PASS_VERIFY" IS '当前用户个人信息是否通过验证';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_PROVINCE" IS '居住地所在省';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_CITY" IS '居住地所在市、县';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_AREA" IS '居住地所在乡镇、区';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_STREET" IS '居住地所在街道';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_ADDRESS_ALL" IS '地名名称汇总';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_ADDRESS" IS '详细地址';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."BL_UPDATE_PWD" IS '用户是否更改默认密码,更改默认的手机号密码';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_VILLAGE" IS '居住地所在村';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_INNER_EMPLOYEE_FLAG" IS '是否内部员工0是,1不是';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_MACHINE_ID" IS '设备ID';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_REG_FROM" IS '注册来源0APP 1PC';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."INT_YTHFW_USER" IS '信息联通服务费(缴费:1,没缴费0)';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_USER"."VC_REG_DOCTOR_ACCOUNT" IS '注册医生账号';
COMMENT ON TABLE "JISHIJIAKANG"."OS_SYSTEM_USER" IS '用户表';
/**
*行政区划表
**/
CREATE TABLE "JISHIJIAKANG"."OS_SYSTEM_AREA"
( "VC_ID" VARCHAR2(32 BYTE) NOT NULL ENABLE,
"VC_PARENT_ID" VARCHAR2(32 BYTE),
"VC_NAME" VARCHAR2(255 BYTE),
"VC_PARENT_NAME" VARCHAR2(255 BYTE),
"VC_AREA_CODE" VARCHAR2(20 BYTE),
"VC_MODIFY_USER" VARCHAR2(20 BYTE),
"DT_MODIFY_TIME" DATE,
"VC_STATE" VARCHAR2(10 BYTE),
"VC_LEAF" CHAR(1 BYTE),
CONSTRAINT "AREA_PKID" PRIMARY KEY ("VC_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JISHIJIAKANG" ENABLE,
CONSTRAINT "AREA_PKCODE" UNIQUE ("VC_AREA_CODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JISHIJIAKANG" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "JISHIJIAKANG" ;
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_AREA"."VC_ID" IS '主键标识';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_AREA"."VC_PARENT_ID" IS '父级ID,默认为0';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_AREA"."VC_NAME" IS '区域名称';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_AREA"."VC_PARENT_NAME" IS '父级名称';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_AREA"."VC_AREA_CODE" IS '行政区域代码';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_AREA"."VC_MODIFY_USER" IS '最后一次添加或修改人员';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_AREA"."DT_MODIFY_TIME" IS '最后一次添加或修改时间';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_AREA"."VC_STATE" IS '状态';
COMMENT ON COLUMN "JISHIJIAKANG"."OS_SYSTEM_AREA"."VC_LEAF" IS '0不是根结点1是根结点';
COMMENT ON TABLE "JISHIJIAKANG"."OS_SYSTEM_AREA" IS '区域';