如何用MySQL将A表的行传给B表?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (129)

表A(tracks):

track_name1 |   link    |   age |   artists
track_name2 |   link    |   age |   here
track_name3 |   link    |   age |   but
track_name4 |   link    |   age |   how?

表B(艺术家):

jon     |   nick    |   age
mark    |   nick    |   age
luke    |   nick    |   age
you     |   nick    |   age
提问于
用户回答回答于

需要第三张桌子,把曲目和艺术家放在一起,请参阅以下代码:

CREATE TABLE IF NOT EXISTS `tracks` (
    `id` int(11) unsigned NOT NULL,
    `name` varchar(200) NOT NULL,
    `link` varchar(200) NOT NULL,
    `age` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `tracks` (`id`, `name`, `link`, `age`) VALUES
   ('1', 'track_name1', 'link1', '25'),
   ('2', 'track_name2', 'link2', '25'),
   ('3', 'track_name3', 'link3', '35'),
   ('4', 'track_name4', 'link4', '35');

CREATE TABLE IF NOT EXISTS `artists` (
    `id` int(11) unsigned NOT NULL,
    `name` varchar(200) NOT NULL,
    `nick_name` varchar(200) NOT NULL,
    `age` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `artists` (`id`, `name`, `nick_name`, `age`) VALUES
    ('1', 'jon', 'jon', '25'),
    ('2', 'mark', 'mark', '25'),
    ('3', 'luke', 'luke', '35'),
    ('4', 'you', 'you', '35');

CREATE TABLE IF NOT EXISTS `tracks_artists` (
    `id` int(11) unsigned NOT NULL,
    `track_id` int(11) NOT NULL,
    `artist_id` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `tracks_artists` (`id`, `track_id`, `artist_id`) VALUES
    ('1', '1', '1'),
    ('2', '2', '2'),
    ('3', '3', '3'),
    ('4', '4', '4’);

然后可以运行下面提到的查询来获得上述结果:

SELECT `t2`.`name`, `t2`.`link`, `t2`.`age`, `t3`.`name`
FROM `tracks_artists` `t1` 
INNER JOIN `tracks` `t2` ON `t1`.`track_id` = `t2`.`id`
INNER JOIN `artists` `t3` ON `t1`.`artist_id` = `t3`.`id`;

扫码关注云+社区

领取腾讯云代金券