表A(曲目):
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
我想得到轨道"1“-->艺术家”乔恩和马克“,并获得所有其他信息,他们。谢谢!
发布于 2018-06-16 03:49:21
我同意@Uueerdo的评论。您需要第三个表,它将曲目和艺术家映射在一起。请参考以下代码:
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`;
我已经把代码放在Here上了。您可以对其进行测试。
希望这能有所帮助。
https://stackoverflow.com/questions/50881551
复制相似问题