这条sql怎么写？
考察左连接、右连接和联合
表结构,都没有添加时间和更新时间 演示用:
CREATE TABLE `target` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL DEFAULT 'A',
`category` varchar(20) NOT NULL,
`price` int(11) unsigned NOT NULL DEFAULT '0',
`num` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
CREATE TABLE `orders` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL DEFAULT 'A',
`category` varchar(20) NOT NULL,
`price` int(11) unsigned NOT NULL DEFAULT '0',
`num` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
准备数据:
INSERT INTO `target` (`id`, `username`, `category`, `price`, `num`)
VALUES
(1, 'A', '电', 10, 2),
(2, 'A', '洗', 20, 3),
(3, 'A', '厨', 30, 4),
(4, 'A', '卫', 40, 5),
(5, 'A', '脚', 50, 8);
INSERT INTO `orders` (`id`, `username`, `category`, `price`, `num`)
VALUES
(1, 'A', '保暖瓶', 20, 6),
(2, 'A', '电', 30, 7),
(3, 'A', '洗', 40, 8),
(4, 'A', '卫', 70, 9),
(5, 'A', '厨', 80, 10),
(6, 'A', '手提', 90, 11);
执行sql:
select t.username,t.category,t.price,t.num,o.price,o.num from target as t left join orders as o using(category)
union
select o.username,o.category,t.price,t.num,o.price,o.num from target as t right join orders as o using(category)
结果:
