关于HQL查询问题！

Question

我有两个表：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 这些字段应该怎么弄呢？
希望高人能帮我解答下，十分感谢！

Answer 1

[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你就自己修改一下吧。