连接ON条件存储为字符串的两个表的TSQL方法

Question

我基本上是在尝试连接两个表，其中连接条件存储为字符串。

• 我在表A中存储了数据，其中包括字段TREATY_NO和CO_CODE。

• 我还有下面的表B。我试图返回LookupCode，其中IncomingData字段为true。

+---------------------------------------------------------------------------------------+------------------------------------------+
|                                     IncomingData                                      |                  LookupCode              |
+---------------------------------------------------------------------------------------+------------------------------------------+
| CHARINDEX('99FW',TREATY_NO,3)>0 AND (CO_CODE='PAZ' OR CO_CODE='PNJ' OR CO_CODE='PRU') | '5-0089-2013-0500-01-A-2013-Y-USD-AL-01' |
| CHARINDEX('99FV',TREATY_NO,3)>0 AND (CO_CODE='PAZ' OR CO_CODE='PNJ' OR CO_CODE='PRU') | '5-0089-2013-0500-01-A-2013-Y-USD-AL-01' |
| CHARINDEX('99FK',TREATY_NO,3)>0 AND (CO_CODE='PAZ' OR CO_CODE='PNJ' OR CO_CODE='PRU') | '5-0089-2013-0500-01-A-2013-Y-USD-AL-01' |
| CHARINDEX('99FL',TREATY_NO,3)>0 AND (CO_CODE='PAZ' OR CO_CODE='PNJ' OR CO_CODE='PRU') | '5-0089-2013-0500-01-A-2013-Y-USD-AL-01' |
+---------------------------------------------------------------------------------------+------------------------------------------+

例如，如果表a中的一行具有TREATY_NO = 'ABC99FWTZ'和CO_CODE = 'PAZ'，则LookupCode应='5-0089-2013-0500-01-A-2013-Y-USD-AL-01'

+-----------+---------+----------------------------------------+
| TREATY_NO | CO_CODE |               LookupCode               |
+-----------+---------+----------------------------------------+
| ABC99FWTZ | PAZ     | 5-0089-2013-0500-01-A-2013-Y-USD-AL-01 |
+-----------+---------+----------------------------------------+

我确信一定有一种方法可以做到这一点，但我不知道该搜索什么。我已经研究过一行一行地使用游标，但一组结果似乎是更好的解决方案。

我也一直在努力遵循这个问题的第一个答案。但问题已经完全不同了。SQL Server查询条件作为存储在表列中的文本

即使是我试图解决的问题的关键词也会很有帮助。

谢谢！

Answer 1

我建议您更改问题的设计。我想不出任何有效的方法来做这件事。但是，如果这是一个一次性任务，你可以将光标与动态Sql结合起来使其工作。例如，

--we declare the necessary variables that we will use
DECLARE @filterText as VARCHAR(512)
DECLARE @sqlText as VARCHAR(1024)
DECLARE @TREATY_NO as VARCHAR(24)
SET @TREATY_NO = 'ABC99FWTZ'
DECLARE @CO_CODE as VARCHAR(24)
SET @CO_CODE = 'PAZ'
DECLARE @tempStr AS VARCHAR(512)
--we init a cursor that will get the filter from the tableB which then we create a dynamic SQL
DECLARE db_cursor CURSOR FOR 
SELECT IncomingData 
FROM TableB
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @filterText  
WHILE @@FETCH_STATUS = 0  
BEGIN  
--we replace the variables from the text as parameters
@tempStr = REPLACE(@filterText, 'TREATY_NO', '@TREATY_NO')
@tempStr = REPLACE(@filterText, 'CO_CODE', '@CO_CODE')

--we create the SQL query as a string where the WHERE check first the row to be the one from the cursor and then the actual filter if it is true or not
@sqlText = 'SELECT @TREATY_NO, @CO_CODE, LookupCode FROM TableB WHERE @filterText = IncomeData AND ' + @tempStr
--we execute the select
EXEC (@sqlText)
FETCH NEXT FROM db_cursor INTO @filterText 
END 
CLOSE db_cursor  
DEALLOCATE db_cursor