ORACLE1.11

select * from user1

select * from dicts

-- 多对一（字典类应用）

drop table user1;

create table user1(

    id varchar2(10),

    username varchar2(20),

    sex varchar2(10),

    minzu varchar2(10)

);

insert into user1 values(1,'小明','男','汉族');

insert into user1 values(1,'小芳','女','傣族');

commit;

select * from user1

 

<option value="0">男</option>

<option value="1">女</option>

-----------------------------

drop table user1;

create table user1(

    id varchar2(10) primary key,

    user_name varchar2(20),

    sex number(1),

    minzu number(2)

);

insert into user1 values(1,'小明',1,1);

insert into user1 values(2,'小芳',0,2);

commit

select * from user1

drop table dicts

create table dicts(

   id number primary key,

   display varchar2(100), -- 显示内容

   key_value number,      -- 真实的值

   key_type varchar2(10),  -- 类别

   lang varchar2(10)      -- 语言

);

 

insert into dicts values(1,'男',1,'sex','cn');

insert into dicts values(2,'女',0,'sex','cn');

insert into dicts values(3,'boy',1,'sex','en');

insert into dicts values(4,'girl',0,'sex','en');

insert into dicts values(5,'汉族',1,'minzu','cn');

insert into dicts values(6,'傣族',2,'minzu','cn');

insert into dicts values(7,'hanzu',1,'minzu','en');

insert into dicts values(8,'daizu',2,'minzu','en');

select * from dicts

 

-- 传统的关联方式

select

user1.id,

user_name,

dict_sex.display sex,--sex,

dict_minzu.display minzu--minzu

from user1,dicts dict_sex,dicts dict_minzu

where

user1.sex=dict_sex.key_value and dict_sex.key_type='sex' and dict_sex.lang='en'

and

user1.minzu=dict_minzu.key_value and dict_minzu.key_type='minzu' and dict_minzu.lang='en'

 

-- 传统的关联方式2

select * from user1

select * from dicts where lang='cn' and key_type='sex'

select * from dicts where lang='cn' and key_type='minzu'

 

select user1.id,user_name,dict1.display sex,dict2.display minzu from

user1,

(select * from dicts where lang='cn' and key_type='sex') dict1,

(select * from dicts where lang='cn' and key_type='minzu') dict2

where

user1.sex=dict1.key_value

and

user1.minzu=dict2.key_value

 

-- 优化

select
user1.id,
user_name,
dict1.display sex,
dict2.display minzu
from user1
left join (select * from dicts where lang='cn' and key_type='sex') dict1
on user1.sex = dict1.key_value
left join (select * from dicts where lang='cn' and key_type='minzu') dict2
on user1.minzu = dict2.key_value

 

select
user1.id,
user_name,
nvl(dict1.display,'未知') sex,
nvl(dict2.display,'未知') minzu
from user1
left join (select * from dicts where lang='cn' and key_type='sex') dict1
on user1.sex = dict1.key_value
left join (select * from dicts where lang='cn' and key_type='minzu') dict2
on user1.minzu = dict2.key_value

 

-- 实验

select * from user1

insert into user1 values(4,'小红',2,1);

insert into user1 values(5,'小林',1,3);

commit

 

-- 观察内关联的结果

（小红和小林都丢了）

-- 观察左关联的结果

 

 

-- 继续对右表操作

select * from dicts

insert into dicts values(9,'外星族',99,'minzu','cn');

commit

 

select '' from dual
select null from dual
select nvl('123','未知') from dual
select nvl(null,'未知') from dual

select * from dicts
insert into dicts values(10,'维吾尔族',3,'minzu','cn')
commit