sql - 合并多个列

Question

我有如下所示的三个简单表格:

+----+-------------+  +----+-----------+  +----+---------------+
| artists          |  | albums         |  | songs              |
+----+-------------+  +----+-----------+  +----+---------------+
| id | name        |  | id | name      |  | id | name          |
+----+-------------+  +----+-----------+  +----+---------------+
| 0  | The Beatles |  | 0  | Help!     |  | 0  | Help!         |
| 1  | Metallica   |  | 1  | Metallica |  | 1  | Enter Sandman |
+----+-------------+  +----+-----------+  +----+---------------+

我希望查询结果如下:

+--------+---------------+
| type   | name          |
+--------+---------------+
| artist | The Beatles   |
| artist | Metallica     |
| album  | Help!         |
| album  | Metallica     |
| song   | Help!         |
| song   | Enter Sandman |
+--------+---------------+

我正在尝试使用 LIKE 查询来搜索艺术家、专辑和歌曲。

现在我使用下面的 SQL 从三列中获取结果，然后我使用 Ruby 将它们放在一个对象中。我认为使用纯 SQL 而不是解释型语言来返回结果会更有效。

SELECT name FROM artists WHERE name LIKE 'something%'
SELECT name FROM albums WHERE name LIKE 'something%'
SELECT name FROM songs WHERE name LIKE 'something%'

我对 SQL 不是很好，并且已经为此苦苦挣扎了几天，想知道这里是否有人可以指出我正确的方向。

Answer 1

您可以使用 UNION 将相似的结果连接在一起。试试这个:

SELECT 'artist' as Type, name FROM artists WHERE name LIKE 'something%'
UNION SELECT 'album' as Type, name FROM albums WHERE name LIKE 'something%'
UNION SELECT 'song' as Type, name FROM songs WHERE name LIKE 'something%'