huiwenshu 2009-07-14 02:30
浏览 242
已采纳

关于HQL查询问题!

我有两个表:citizen_base_info,data_dictionary

desc citizen_base_info;
Name Type Nullable Default Comments


EHRID VARCHAR2(23)

NAME VARCHAR2(20)

BIRTHDAY DATE Y

[color=red]SEX_CODE [/color] VARCHAR2(1) Y

[color=red]MARRIAGE_CODE [/color] VARCHAR2(2) Y

NATION_CODE VARCHAR2(2) Y

NATIONALITY_CODE VARCHAR2(3) Y

CREATION_DATETIME DATE

desc data_dictionary;
Name Type Nullable Default Comments


VERSION VARCHAR2(50) Y

NAME VARCHAR2(20) Y

[color=red]MEANING[/color] VARCHAR2(500) Y

[color=red]------------------------------------ [/color]

现在我的citizen_base_info.sex_code 是代号,通过data_dictionary (name,version)来取得值
比如:sex_code 为1,对应data_dictionary(1,gb_2302)的含义为meaning “男”,

citizen_base_info.MARRIAGE_CODE为1,对应data_dictionary(1,mar_2201)的含义为meaning “未结婚”。

现在我想要查询如下的两表连接查询,用HQL来实现。来查询有含义的字段。如
NAME BIRTHDAY SEX_ MEANING MARRIAGE_MEANING 这些字段应该怎么弄呢?
希望高人能帮我解答下,十分感谢!

  • 写回答

2条回答 默认 最新

  • numenZQ 2009-07-14 09:35
    关注

    [code="sql"]
    select i.name as name,
    i.birthday as birthday,
    sd.meaning as sex_meaning,
    md.meaning as marriage_meaning
    from citizen_base_info i, data_dictionary sd, data_dictionary md
    where i.sex_code = sd.name
    and sd.version = ?
    and i.marriage_code = md.name
    and md.version = ?;

    -- or
    select i.name as name,
    i.birthday as birthday,
    (select sd.meaning
    from data_dictionary sd
    where sd.name = i.sex_code
    and sd.version = ?) as sex_meaning,
    (select md.meaning
    from data_dictionary md
    where md.name = i.marriage_code
    and md.version = ?) as marriage_meaning
    from citizen_base_info i;

    [/code]

    HQL或criteria你就自己修改一下吧。

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?