DB列提取和拆分以查找其他表结果 [英] DB column extract and split to find other table result

问题描述

我有 table1 其中 Temp_ID 列是 ID 列值的组合 table2 和 table3.

I have table1 where Temp_ID column is combination of ID column values form table2 and table3.

示例:table1_id:table2_id {values like (1:1,2:2)}

Example: table1_id:table2_id {values like (1:1,2:2)}

我在我的Java代码中做了什么我需要使用Temp_ID并根据COLON(:)进行拆分，然后我们可以使用得到表2和表3的数据从 split 中获得的 ID 值.

What I do in my Java code I need to use Temp_ID and split according to COLON(:), and then we can get table 2 and table 3 data using ID values obtained from split.

但是 table1 可以有大量数据，所以如果我在 Java 中这样做，我需要一次又一次地运行 2 个查询.

But table1 can have huge data, so if i do this in java I need to run 2 queries again and again.

是否可以编写查询，因此我可以在查询中对 Temp_ID 进行拆分，并使用它们来执行 JOIN 并在 MS SQL 本身中获取数据.

Is it possible to write query, So i can do this split of Temp_ID in query and use those to do JOIN and get data in MS SQL itself.

推荐答案

我想说的很简单.这是执行类似操作的查询

Neat thinking I'd say. Here's a query for doing something like that

SELECT 
t1.temp_id
   --  ... put your column list here from t2 and t3 here like t2.name
FROM table1 t1 
  LEFT JOIN table2 t2 
     ON CAST(LEFT(t1.temp_id,CHARINDEX(':',t1.temp_id)-1) AS INT)=t2.ID
  LEFT JOIN table3 t3 
     ON CAST(RIGHT(t1.temp_id,CHARINDEX(':',REVERSE(t1.temp_id))-1) AS INT)=t3.ID

这里还有一个小脚本，用于测试这个

Here's a small script for testing this out as well

create table table1 (temp_id varchar(10))
insert into table1 values('1:1'),('21:2'),('1:22'),('1:'),(':2')

create table table2 (id int, value varchar(2))
insert into table2 values (1,'1'),(21,'21'),(1,'1')

create table table3 (id int, value varchar(2))
insert into table3 values (1,'1'),(2,'2'),(22,'22')

输出PS:注意输出中的 null 处理

Output PS: Note the null handling in the output

这篇关于DB列提取和拆分以查找其他表结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！