将多列结果插入到单列表中

I have a table 'movies' with three Columns: 'id', 'master_id' and 'searchMe' (simplified). I have another Table 'temp_ids' with a single column: 'id'. It is a temporary table, but I don't think that matters.

我有一个包含三个列的表“movies”:“id”、“master_id”和“searchMe”(简化)。我有另一个表'temp_ids'，只有一个列:'id'。这是一张临时的桌子，但我认为这并不重要。

When I make a query on my table 'movies' like

当我对我的表格“电影”进行查询时

SELECT `id`, `master_id` FROM 'movies' WHERE searchMe = '1';

I get a multi column result. Now I want to insert every id and every master_id into the 'temp_ids'-Table, but one at a time. So if my result is

我得到一个多列结果。现在，我想将每个id和每个master_id插入到“temp_ids”表中，但每次只插入一个。如果我的结果是

id_1 | master_1
id_2 | master_2
id_3 | NULL

I want my temp_ids to look like

我希望我的temp_id看起来像这样

id_1
master_1
id_2
master_2
id_3

So I want to convert every single column in the result into its own row. How can I do that in an elegant way? I know I can do it in multiple queries, searching for id and master_id separatly, and I know I can solve that problem with PHP or so. But I would prefer it to solve that problem in a single mysql-query, if such a thing is possible.

我想把结果中的每一列都转换成它自己的行。我怎样才能优雅地做到这一点呢?我知道我可以在多个查询中完成，分别搜索id和master_id，我知道我可以用PHP解决这个问题。但如果可能的话，我希望它能在一个mysql查询中解决这个问题。

I made a sqlfiddle for this: http://sqlfiddle.com/#!2/b4a7f/2

我做了一个sqlfiddle: http://sqlfiddle.com/#!2/b4a7f/2。

2 个解决方案

#1

To SELECT the data you can use a UNION ALL for this:

要选择数据，您可以为此使用一个联合:

SELECT  `id`
FROM movies
WHERE searchMe = 1
union all
SELECT `master_id`
FROM movies
WHERE searchMe = 1
  and master_id is not null

see SQL Fiddle with Demo

Doing it this way, you cannot distinguish between what value comes from each column, so you can always add an indicator, this will give you two columns but then you know where the data came from:

这样做，你无法区分每一列的值，所以你总可以添加一个指示器，这会给你两列，但你知道数据来自哪里:

SELECT  `id`, 'id' type
FROM movies
WHERE searchMe = 1
union all
SELECT `master_id`, 'master'
FROM movies
WHERE searchMe = 1
  and master_id is not null

Then you would just use this query to INSERT INTO temp using this SELECT

然后您就可以使用这个查询来使用这个SELECT插入到temp中

#2

It would be like this

就像这样

INSERT INTO temp_ids(id)
SELECT id 
FROM
(
   SELECT id
   FROM FirstTable 
   UNION
   SELECT master AS id
   FROM SecondTable 
) t