用外部查询属性限制内部查询

问题描述

我目前有一个需要修改的大型SQL查询(不是我的).我有一个交易和估值表.该交易与估值具有一对多关系.这两个表是通过外键联接的.

I currently have a large SQL query (not mine) which I need to modify. I have a transaction and valuation table. The transaction has a one-to-many relationship with valuations. The two tables are being joined via a foreign key.

如果在特定日期之后没有任何交易的估值，我被要求阻止任何交易(及其后续估值)被退回.我以为可以实现此目的的方法是使用内部查询，但是我需要使内部查询了解外部查询和事务.像这样:

I've been asked to prevent any transactions (along with their subsequent valuations) from being returned if no valuations for a transaction exist past a certain date. The way I thought I would achieve this would be to use an inner query, but I need to make the inner query aware of the outer query and the transaction. So something like:

SELECT * FROM TRANSACTION_TABLE T
INNER JOIN VALUATION_TABLE V WHERE T.VAL_FK = V.ID
WHERE (SELECT COUNT(*) FROM V WHERE V.DATE > <GIVEN DATE>) > 1

显然，以上内容无法使用，因为内部查询是独立的，而且我无法从内部引用外部查询V引用.我将如何去做，还是有一个更简单的方法?

Obviously the above wouldn't work as the inner query is separate and I can't reference the outer query V reference from the inner. How would I go about doing this, or is there a simpler way?

这就是在外部查询中设置WHERE V.DATE>的情况，因为如果其中任何一个超过指定日期，我都想防止给定交易的任何估值，而不仅仅是这样做的话.

This would just be the case of setting the WHERE V.DATE > in the outer query as I want to prevent any valuation for a given transaction if ANY of them exceed a specified date, not just the ones that do.

非常感谢您提供的任何帮助.

Many thanks for any help you can offer.

推荐答案

您可能会寻找

SELECT *
FROM TRANSACTION_TABLE T
INNER JOIN VALUATION_TABLE V1 ON T.VAL_FK = V1.ID
WHERE (SELECT COUNT(*)
       FROM VALUATION_TABLE V2
       WHERE V2.ID = V1.ID AND V2.DATE > <GIVEN DATE>) > 1

这篇关于用外部查询属性限制内部查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！