两个不同的表连接mysql
两个不同的表连接mysql
提问于 2014-02-22 23:34:25
我有两张桌子，其中一张：
专辑：
CREATE TABLE IF NOT EXISTS `albums` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`singer` varchar(64) NOT NULL,
`year` int(11) NOT NULL,
`releaseDate` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `categoryId` (`categoryId`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;音乐：
CREATE TABLE IF NOT EXISTS `musics` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL,
`singer` varchar(128) NOT NULL,
`genre` varchar(128) NOT NULL,
`albumId` int(11) DEFAULT NULL,
`year` int(4) NOT NULL,
`releaseDate` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `categoryId` (`categoryId`),
KEY `albumId` (`albumId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;我想加入这张桌子，由releaseDate订购。有可能吗？对不起我的英语。
结果：现在我得到了一些结果：
+-----------------------------------------------+-------------------------+-------------+
| albums_name | musics_name | releaseDate |
+-----------------------------------------------+-------------------------+-------------+
| The Artificial Theory For The Dramatic Beauty | K | NULL |
| The Artificial Theory For The Dramatic Beauty | Fiction In Hope | NULL |
| The Artificial Theory For The Dramatic Beauty | Chemicarium | NULL |
| The Artificial Theory For The Dramatic Beauty | Voice | NULL |
| The Artificial Theory For The Dramatic Beauty | Blue | NULL |
| The Artificial Theory For The Dramatic Beauty | Mirror | NULL |
| The Artificial Theory For The Dramatic Beauty | If You Want To Wake Up? | NULL |
| The Artificial Theory For The Dramatic Beauty | Interlude | NULL |
| NULL | Everything At Once | 2010-11-11 |
| NULL | Blue Freightliner | 2011-11-11 |
+-----------------------------------------------+-------------------------+-------------+我要：
+-----------------------------------------------+-------------------------+-------------+
| albums_name | musics_name | releaseDate |
+-----------------------------------------------+-------------------------+-------------+
| The Artificial Theory For The Dramatic Beauty | NULL | 2009-11-11 |
| NULL | Everything At Once | 2010-11-11 |
| NULL | Blue Freightliner | 2011-11-11 |
+-----------------------------------------------+-------------------------+-------------+回答 2
Stack Overflow用户
回答已采纳
发布于 2014-02-23 20:34:47
有两个非常不同的部分，您的输出似乎包括：
- 专辑及其发行日期；
- 专辑-无音轨和他们的发行日期。
每个部分来自不同的桌子，我觉得这是一个典型的例子，一个联合，而不是连接，两组：
SELECT
name AS albums_name,
NULL AS musics_name,
releaseDate
FROM albums
UNION ALL
SELECT
NULL AS albums_name,
name AS musics_name,
releaseDate
FROM musics
WHERE
album_id IS NULL
ORDER BY
releaseDate ASC
;Stack Overflow用户
发布于 2014-02-22 23:36:50
你应该学习/玩JOIN。有几种不同的类型(内连接、左连接)。
下面是一个让您开始的简单示例：
SELECT albums.name AS albums.name, musics.name AS musics_name, musics.releaseDate
FROM albums
LEFT JOIN musics ON albums.id = musics.albumId
ORDER BY musics.releaseDate或者，如果你只需要音乐和相配的专辑：
SELECT albums.name AS albums.name, musics.name AS musics_name, musics.releaseDate
FROM musics
LEFT JOIN albums ON musics.albumId = albums.id
ORDER BY musics.releaseDate页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/21962117
复制相关文章
相似问题
腾讯云开发者
