SQL Multiple where子句 [英] SQL multiple where clause

问题描述

我找不到使用这样的多个where子句是否有效(我使用JPA，MySQL)，我需要多个where子句，其中一个在这里是"not"，还是我错过了什么?

I couldn't find if using multiple where clauses like this is valid or not(I use JPA, MySQL) I need multiple where clauses one of them will be a "not" here, or am I missing something?

select d from T_DEBIT d where d.status=PENDING and 
where not exists (
select r
from T_REQUEST r
where 
r.debit.id = d.id and
r.status = SUCCESSFUL
)

请询问您是否需要更多信息，

Please do ask if you need further information,

推荐答案

JPA提供了对子查询的支持. 请参阅规范

JPA provides support for subqueries. See the specification

子查询可以在WHERE或HAVING子句中使用.的语法 子查询如下:

Subqueries may be used in the WHERE or HAVING clause. The syntax for subqueries is as follows:

subquery ::= simple_select_clause subquery_from_clause [where_clause
[groupby_clause] [having_clause] 

子查询仅限于此中的WHERE和HAVING子句 释放.将考虑对FROM子句中的子查询的支持 在规范的更高版本中.

Subqueries are restricted to the WHERE and HAVING clauses in this release. Support for subqueries in the FROM clause will be considered in a later release of the specification.

您的查询似乎是用SQL编写的，将其转换为JPQL需要做一些事情:

Your query appears to be written in SQL, converting it to JPQL will require a few things:

1. 使用实体名称代替表名称.
2. 如果status是String类型的字段，请确保将诸如PENDING之类的状态用单引号引起来.
3. 遵循存在表达式的语法

1. Use the entity names instead of tables names.
2. If status is a field of type String be sure to enclose the statuses such as PENDING with single quotes.
3. Follow syntax for the exists expression

我相信您也可以将查询写为联接(伪代码):

I believe you could also write your query as a join (PSEUDO CODE):

select d 
from T_DEBIT d 
left join T_REQUEST tr
on d.id = tr.debit_id
where d.status = 'PENDING'
and tr.status = 'SUCCESSFUL`
and tr.debit_id is null

这篇关于SQL Multiple where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！