根据第三个表中的选择,从另一个表中插入行

时间:2021-03-22 19:45:23

I have a SELECT statement:

我有一个SELECT语句:

SELECT searchstring1, searchstring2 FROM t1 WHERE user_id = number;

Which produces multiple rows:

这会产生多行:

[result1, result2]
[result3, result4]
[result5, result6]

Now I want to use these rows for a new SELECT statement, like this:

现在我想将这些行用于新的SELECT语句,如下所示:

SELECT val1, val2, val3 FROM t2 WHERE val1 LIKE '%searchstring1%' AND val2 = 'searchstring2';

Which produces even more rows:

这会产生更多行:

[result7, result8, result9]
[result10, result11, result12]
.
.
.
[result100, result101, result102]

And then insert the resulting rows into a third table:

然后将结果行插入第三个表:

INSERT INTO t3 (val1, val2, val3) VALUES ('result7', 'result8, 'result9');
INSERT INTO t3 (val1, val2, val3) VALUES ('result10', 'result11, 'result12');
.
.
.

I know I have to do some form of join on the first two, and probably have to do the insert as a new query - but I have no idea how to do it. Can you help? And is it possible to do all this in one query?

我知道我必须在前两个上做某种形式的连接,并且可能必须将插入作为新查询 - 但我不知道如何做到这一点。你能帮我吗?是否可以在一个查询中完成所有这些操作?

1 个解决方案

#1


1  

Maybe something like this:

也许是这样的:

INSERT INTO t3 (val1, val2, val3)
SELECT
    t2.val1,
    t2.val2,
    t2.val3
FROM
    t2
WHERE EXISTS
    (
        SELECT
            NULL
        FROM
            t1
        WHERE
            t2.val1 LIKE CONCAT('%',t1.searchstring1,'%')
            AND t2.val2 LIKE CONCAT('%',t1.searchstring2,'%')
            AND t1.user_id = number
    )

#1


1  

Maybe something like this:

也许是这样的:

INSERT INTO t3 (val1, val2, val3)
SELECT
    t2.val1,
    t2.val2,
    t2.val3
FROM
    t2
WHERE EXISTS
    (
        SELECT
            NULL
        FROM
            t1
        WHERE
            t2.val1 LIKE CONCAT('%',t1.searchstring1,'%')
            AND t2.val2 LIKE CONCAT('%',t1.searchstring2,'%')
            AND t1.user_id = number
    )