• Oracle多对一(虚构表)


    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 * 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
  • 相关阅读:
    vs2010创建文件夹
    strlen源码,远没有想象中的那么简单、、、、
    排序
    字符数组,字符指针,sizeof,strlen总结
    QT中的QInputDialog的小例子
    QT实现启动画面
    QT中Dialog的使用

    QT中的文件浏览
    Python日期操作
  • 原文地址:https://www.cnblogs.com/yuchne/p/12920672.html
Copyright © 2020-2023  润新知