使用外部查询atttribute限制内部查询

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.

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

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

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?

显然上面的内容是不可行的，因为内部查询是分开的，我不能从内部引用外部查询V引用。我该怎么做呢，还是有更简单的方法？

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.

这只是在外部查询中设置WHERE V.DATE>的情况，因为我想要阻止给定事务的任何评估，如果它们中的任何一个超过指定日期，而不仅仅是那些。

Many thanks for any help you can offer.

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

2 个解决方案

#1

You may looking for this

你可能正在寻找这个

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

#2

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

If execution time is important, you may want to test the various solutions on your actual data and see which works best in your situation.

如果执行时间很重要，您可能需要测试实际数据的各种解决方案，并查看哪种解决方案最适合您的情况。

#1