17,086
社区成员
发帖
与我相关
我的任务
分享
--省
create table Province
(
pid int ,
Province_name varchar2(50)
)
--市
create table City
(
cid int ,
city_name varchar2(50),
pid int --外键省id
)
--城镇
create table Town
(
tid int ,
town_name varchar2(50),
cid int --外键市id
)
--村
create table Village
(
vid int ,
village_name varchar2(50),
tid int --外键镇id
)
--用户档案表
create table Archives
(
id int ,
a_name varchar2(50),--姓名
a_sex char(1),--性别
........
vid int ---外键村id
)
CREATE OR REPLACE VIEW v_village
AS
SELECT p.ID, p.province_name, c.cid, c.city_name, t.tid, t.town_name,
v.vid, v.village_name
FROM province p, city c, town t, village v
WHERE v.tid = t.tid AND t.cid = c.cid AND c.ID = p.ID
CREATE OR REPLACE VIEW v_village
AS
SELECT p.ID, p.province_name, c.cid, c.city_name, t.tid, t.town_name,
v.vid, v.village_name
FROM province p, city c, town t, village v
WHERE v.tid = t.tid AND t.cid = c.cid AND c.ID = p.ID
select * from Archives a,v_village v where a.vid=v.vid
select
c1.name as 村名,
c2.name as 镇名,
c3.name as 市名,
c4.name as 省名,
z.* from 用户主表 z
left join 地址代码表 c1 on z.村ID = c1.id
left join 地址代码表 c2 on c1.pid = c2.id
left join 地址代码表 c3 on c2.pid = c3.id
left join 地址代码表 c4 on c3.pid = c4.id