如何使用 Nhibernate 查询包
我需要获取国家列表中不包含国家/地区的出版物(对 IsoCode2 的研究)
sql 查询是:
select * from pub_head ph
where not exists
(select 1 from pub_head_forbidden_country phfc , country c
where phfc.pub_head_id = ph.pub_head_id
and phfc.country_id = c.country_id
and c.iso_code2 = 'CA');
模型:
<class name="Publication" table="PUB_HEAD">
<id name="Id" column="PUB_HEAD_ID">
<generator class="native">
<param name="sequence">SEQ_PUB_HEAD</param>
</generator>
</id>
<idbag name="Countries" table="PUB_HEAD_COUNTRY" lazy="true">
<collection-id column="PUB_HEAD_COUNTRY_ID">
<generator class="native">
<param name="sequence">SEQ_PUB_HEAD_COUNTRY</param>
</generator>
</collection-id>
<key column ="PUB_HEAD_ID" />
<many-to-many class="Model.Referential.Country, Model" column="COUNTRY_ID"/>
</idbag>
</class>
<class name="Country" table="Country">
<id name="Id" column="COUNTRY_ID">
<generator class="native">
</generator>
</id>
<property name="Name">
<column name="NAME"></column>
</property>
<property name="IsoCode2">
<column name="ISO_CODE2"></column>
</property>
<property name="IsoCode3">
<column name="ISO_CODE3"></column>
</property>
</class>
我从子查询开始,但没有成功做到这一点。
谢谢
I need to get publications which the country list doesn't contain a country (research on IsoCode2)
The sql query is :
select * from pub_head ph
where not exists
(select 1 from pub_head_forbidden_country phfc , country c
where phfc.pub_head_id = ph.pub_head_id
and phfc.country_id = c.country_id
and c.iso_code2 = 'CA');
And the model :
<class name="Publication" table="PUB_HEAD">
<id name="Id" column="PUB_HEAD_ID">
<generator class="native">
<param name="sequence">SEQ_PUB_HEAD</param>
</generator>
</id>
<idbag name="Countries" table="PUB_HEAD_COUNTRY" lazy="true">
<collection-id column="PUB_HEAD_COUNTRY_ID">
<generator class="native">
<param name="sequence">SEQ_PUB_HEAD_COUNTRY</param>
</generator>
</collection-id>
<key column ="PUB_HEAD_ID" />
<many-to-many class="Model.Referential.Country, Model" column="COUNTRY_ID"/>
</idbag>
</class>
<class name="Country" table="Country">
<id name="Id" column="COUNTRY_ID">
<generator class="native">
</generator>
</id>
<property name="Name">
<column name="NAME"></column>
</property>
<property name="IsoCode2">
<column name="ISO_CODE2"></column>
</property>
<property name="IsoCode3">
<column name="ISO_CODE3"></column>
</property>
</class>
I began with SubQueries, but I didn't success to do it.
Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
Sql也可以是这样的
The Sql could be like this too
对加盟过敏?这个 SQL 很难理解。
是这个意思吗?
[作为答案发布,因为这个 SQL 在评论中会很糟糕。]
Allergic to joins? That SQL is very hard to understand.
Does it mean this?
[Posting as answer because this SQL would be awful in a comment.]