是否可以在 SQL 查询的“WHERE”子句中比较*元组*?
是否可以在 SQL 查询的 WHERE
子句中比较元组(谢谢,a_horse_with_no_name)?这样,我可以将其转换
/* This is actually a sub-sub-sub-query in the middle *
* of an incredibly complex stored procedure. */
SELECT ISNULL(SUM(DT.DetailField), 0)
FROM DetailTable DT
WHERE DT.ForeignKey = ...
AND EXISTS (/* I know this sub-sub-sub-sub-query *
* will return at most one row. */
SELECT 'X'
FROM HeaderTable HT
WHERE HT.HeaderKey = DT.HeaderKey
AND HT.HeaderField1 = ...
AND HT.HeaderField2 = ...)
为与此类似的内容:
SELECT ISNULL(SUM(DetailField), 0)
FROM DetailTable DT
WHERE DT.ForeignKey = ...
AND (SELECT HT.HeaderField1, HT.HeaderField2
FROM HeaderTable HT
WHERE HT.HeaderKey = DT.HeaderKey) = (..., ...)
Is it possible to compare tuples (thanks, a_horse_with_no_name) in the WHERE
clause of a SQL query? That way, I could convert this:
/* This is actually a sub-sub-sub-query in the middle *
* of an incredibly complex stored procedure. */
SELECT ISNULL(SUM(DT.DetailField), 0)
FROM DetailTable DT
WHERE DT.ForeignKey = ...
AND EXISTS (/* I know this sub-sub-sub-sub-query *
* will return at most one row. */
SELECT 'X'
FROM HeaderTable HT
WHERE HT.HeaderKey = DT.HeaderKey
AND HT.HeaderField1 = ...
AND HT.HeaderField2 = ...)
Into something similar to this:
SELECT ISNULL(SUM(DetailField), 0)
FROM DetailTable DT
WHERE DT.ForeignKey = ...
AND (SELECT HT.HeaderField1, HT.HeaderField2
FROM HeaderTable HT
WHERE HT.HeaderKey = DT.HeaderKey) = (..., ...)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
写作
当然是可能的。至少对于 Oracle 和 PostgreSQL
如果您不确定子查询是否返回多于一行,您甚至可以将
=
更改为IN
Writing
is certainly possible. At least with Oracle and PostgreSQL
If you are uncertain if the sub-select returns more than one row, you can even change the
=
toIN
您正在寻找的是内部联接:
What you are looking for is inner join:
看起来您试图在这里比较记录而不是表。事实上,您正在比较查询的结果。
对于 Oracle 和 MySQL 来说这是完全可能的。以下查询是有效的,可以完成这项工作:
它将一对一地比较字段,如果它们全部匹配,则返回 1(如果不匹配,则返回 0)。如果子查询返回多于一行,则会引发 SQL 错误。该表达式也可以在其他地方使用,例如在 WHERE 子句中。
postgreSQL 的更新
正如 @tsionyx 指出的,在 PostgreSQL 中子查询不能返回多个列。返回行值类型是可行的:
It seems like you are trying to compare records not tables here. And in fact you are comparing results of queries.
It is totally possible with Oracle and MySQL. The following query is valid and do the job:
It will compare fields one against one and return 1 if they all match (or 0 if they dont). If subqueries return more than one row, it will raise a SQL error. This expression can also be used elsewhere as in WHERE clauses.
Update for postgreSQL
As @tsionyx pointed out, in PostgreSQL a subquery cannot return multiple columns. It's feasible returning a row value type:
Goran 的答案对我来说最好,我投了赞成票。只是添加另一个选项,因为您使用的是 SQL Server,所以从子查询中获取多个列的灵活方法是
outer apply
。您可以比较两个值(元组),例如:Goran's answer looks best to me and I voted for it. Just to add another option, since you're using SQL Server, a flexible way to grab multiple columns from subqueries is
outer apply
. You can compare two values (a tuple) like: