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屋!