java - 相交在HQL中不起作用

所有我正在使用Hibernate，java和Sql Server创建应用程序。在执行此操作时，我遇到了一个问题，即HQL不支持Intersect和Union。对我来说，以下代码在本机SQL中有效，但在处理HQL时不起作用。

SQL

SELECT *
FROM LAB_TEST_SERVICES lts
WHERE lts.inttestid IN
(SELECT lsm.inttestid
    FROM LAB_SPECIMEN_MAPPING lsm
    WHERE lsm.status = 1
intersect
SELECT ltl.inttestid
    FROM LAB_TEST_LOCATION ltl
    WHERE ltl.status = 1)

HQL

String hql="FROM TEST_SERVICES_POJO lts WHERE lts.inttestid IN (SELECT lsm.inttestid FROM SPECIMEN_MAPPING_POJO lsm WHERE lsm.status = 1 intersect SELECT ltl.inttestid FROM TEST_LOCATION_POJO ltl WHERE ltl.status =1)";

谁能帮助我将SQL更改为HQL。提前致谢。

最佳答案

您必须用INTERSECT关系中的两个EXISTS子句替换AND指令。

试试这个:

SELECT *
FROM LAB_TEST_SERVICES_POJO lts
WHERE EXISTS
(SELECT lsm.inttestid
    FROM LAB_SPECIMEN_MAPPING lsm
    WHERE lsm.status = 1
    AND lts.inttestid = lsm.inttestid)
AND EXISTS
(SELECT ltl.inttestid
    FROM LAB_TEST_LOCATION ltl
    WHERE ltl.status = 1
    AND lts.inttestid = ltl.inttestid)