這條sql怎么寫？

考察左連接、右連接和聯(lián)合

表結構,都沒有添加時間和更新時間 演示用:

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;

準備數(shù)據(jù):

INSERT INTO `target` (`id`, `username`, `category`, `price`, `num`)

VALUES

(1, 'A', '電', 10, 2),

(2, 'A', '洗', 20, 3),

(3, 'A', '廚', 30, 4),

(4, 'A', '衛(wèi)', 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', '衛(wèi)', 70, 9),

(5, 'A', '廚', 80, 10),

(6, 'A', '手提', 90, 11);

執(zhí)行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)

結果: