因为mysql当前暂不支持full join的关联方式,如果只有两张表关联的情况,可以使用left join、right join、union的方式处理,但是如果存在多张表都需要full join的方式,又如何处理呢?
1、可以采用嵌套子查询的方式,将前面的查询sql语句当成当前full查询的主表,这样相对好理解,可以理解为永远只有两张表的full join。
2、(此方式为本文重点说明的方式)那就是有几个full join,就用几个union,具体如下:
一、先建五张表:
CREATE TABLE `lsg_category` (
`id` bigint(10) NOT NULL COMMENT '主键ID',
`type` int(3) DEFAULT NULL COMMENT '物品类型',
`type_name` varchar(255) NOT NULL COMMENT '物品名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='物品类目表';
INSERT INTO `lsg_category`(`id`, `type`, `type_name`) VALUES (1, 1, '水果类目');
INSERT INTO `lsg_category`(`id`, `type`, `type_name`) VALUES (2, 2, '体育类目');
INSERT INTO `lsg_category`(`id`, `type`, `type_name`) VALUES (3, 3, '学习用品');
INSERT INTO `lsg_category`(`id`, `type`, `type_name`) VALUES (4, 4, '办公用品');
INSERT INTO `lsg_category`(`id`, `type`, `type_name`) VALUES (5, 5, '游戏类目');
CREATE TABLE `lsg_fruit` (
`id` bigint(10) NOT NULL COMMENT '主键ID',
`fruit_name` varchar(255) DEFAULT NULL COMMENT '水果名称',
`category_id` bigint(10) DEFAULT NULL COMMENT '物品类目ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='水果表';
INSERT INTO `lsg_fruit`(`id`, `fruit_name`, `category_id`) VALUES (1, '苹果', 1);
INSERT INTO `lsg_fruit`(`id`, `fruit_name`, `category_id`) VALUES (2, '桃子', 1);
INSERT INTO `lsg_fruit`(`id`, `fruit_name`, `category_id`) VALUES (3, '西瓜', 1);
INSERT INTO `lsg_fruit`(`id`, `fruit_name`, `category_id`) VALUES (4, '橘子', 6);
INSERT INTO `lsg_fruit`(`id`, `fruit_name`, `category_id`) VALUES (5, '西红柿', 6);
CREATE TABLE `lsg_physical` (
`id` bigint(10) NOT NULL COMMENT '主键ID',
`physical_name` varchar(255) DEFAULT NULL COMMENT '体育物品名称',
`category_id` bigint(10) DEFAULT NULL COMMENT '物品类目ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='体育表';
INSERT INTO `lsg_physical`(`id`, `physical_name`, `category_id`) VALUES (1, '篮球', 2);
INSERT INTO `lsg_physical`(`id`, `physical_name`, `category_id`) VALUES (2, '足球', 2);
INSERT INTO `lsg_physical`(`id`, `physical_name`, `category_id`) VALUES (3, '排球', 2);
INSERT INTO `lsg_physical`(`id`, `physical_name`, `category_id`) VALUES (5, '乒乓球', 7);
INSERT INTO `lsg_physical`(`id`, `physical_name`, `category_id`) VALUES (6, '橄榄球', 8);
CREATE TABLE `lsg_game` (
`id` bigint(10) NOT NULL COMMENT '主键ID',
`game_name` varchar(255) DEFAULT NULL COMMENT '游戏名称',
`category_id` bigint(20) DEFAULT NULL COMMENT '类目ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='游戏表';
INSERT INTO `lsg_game`(`id`, `game_name`, `category_id`) VALUES (1, '跳绳', 5);
INSERT INTO `lsg_game`(`id`, `game_name`, `category_id`) VALUES (2, '踢毽子', 5);
INSERT INTO `lsg_game`(`id`, `game_name`, `category_id`) VALUES (3, '捉迷藏', 15);
INSERT INTO `lsg_game`(`id`, `game_name`, `category_id`) VALUES (4, '猜猜我是谁', 16);
INSERT INTO `lsg_game`(`id`, `game_name`, `category_id`) VALUES (5, '划拳', 17);
INSERT INTO `lsg_game`(`id`, `game_name`, `category_id`) VALUES (6, '剪刀石布', 17);
CREATE TABLE `lsg_learn` (
`id` bigint(10) NOT NULL COMMENT '主键ID',
`learn_name` varchar(255) DEFAULT NULL COMMENT '学习物品名称',
`category_id` bigint(10) DEFAULT NULL COMMENT '类目ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学习用品表';
INSERT INTO `lsg_learn`(`id`, `learn_name`, `category_id`) VALUES (1, '书桌', 3);
INSERT INTO `lsg_learn`(`id`, `learn_name`, `category_id`) VALUES (2, '凳子', 3);
INSERT INTO `lsg_learn`(`id`, `learn_name`, `category_id`) VALUES (3, '讲台', 10);
INSERT INTO `lsg_learn`(`id`, `learn_name`, `category_id`) VALUES (4, '黑板', 11);
二、两张表的full join查询
(a full join b on a.id=b.id)
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`
FROM
`lsg_category` AS `lsg_category`
LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id` UNION
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`
FROM
`lsg_category` AS `lsg_category`
RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
三、3张表的full join查询
(a full join b on a.id=b.id full join c on a.id=c.id)
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`
FROM
`lsg_category` AS `lsg_category`
LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id` UNION
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`
FROM
`lsg_category` AS `lsg_category`
RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id` UNION
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`
FROM
`lsg_category` AS `lsg_category`
LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
RIGHT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id`
(a full join b on a.id=b.id full join c on b.id=c.id)
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`
FROM
`lsg_category` AS `lsg_category`
LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id` UNION
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`
FROM
`lsg_category` AS `lsg_category`
RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id` UNION
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`
FROM
`lsg_category` AS `lsg_category`
RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
RIGHT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id`
四、4张表的full join关联
(a full join b on a.id=b.id full join c on a.id=c.id full join d on a.id=d.id)
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
`lsg_game`.`id` AS `lsg_game$id`,
`lsg_game`.`game_name` AS `lsg_game$game_name`,
`lsg_game`.`category_id` AS `lsg_game$category_id`
FROM
`lsg_category` AS `lsg_category`
LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id`
LEFT JOIN `lsg_game` AS `lsg_game` ON `lsg_category`.`id` = `lsg_game`.`category_id` UNION
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
`lsg_game`.`id` AS `lsg_game$id`,
`lsg_game`.`game_name` AS `lsg_game$game_name`,
`lsg_game`.`category_id` AS `lsg_game$category_id`
FROM
`lsg_category` AS `lsg_category`
RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id`
LEFT JOIN `lsg_game` AS `lsg_game` ON `lsg_category`.`id` = `lsg_game`.`category_id` UNION
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
`lsg_game`.`id` AS `lsg_game$id`,
`lsg_game`.`game_name` AS `lsg_game$game_name`,
`lsg_game`.`category_id` AS `lsg_game$category_id`
FROM
`lsg_category` AS `lsg_category`
LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
RIGHT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id`
LEFT JOIN `lsg_game` AS `lsg_game` ON `lsg_category`.`id` = `lsg_game`.`category_id` UNION
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
`lsg_game`.`id` AS `lsg_game$id`,
`lsg_game`.`game_name` AS `lsg_game$game_name`,
`lsg_game`.`category_id` AS `lsg_game$category_id`
FROM
`lsg_category` AS `lsg_category`
LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_category`.`id` = `lsg_physical`.`category_id`
RIGHT JOIN `lsg_game` AS `lsg_game` ON `lsg_category`.`id` = `lsg_game`.`category_id`
(a full join b on a.id=b.id full join c on b.id=c.id full join d on c.id=d.id)
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
`lsg_game`.`id` AS `lsg_game$id`,
`lsg_game`.`game_name` AS `lsg_game$game_name`,
`lsg_game`.`category_id` AS `lsg_game$category_id`
FROM
`lsg_category` AS `lsg_category`
LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id`
LEFT JOIN `lsg_game` AS `lsg_game` ON `lsg_physical`.`id` = `lsg_game`.`id` UNION
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
`lsg_game`.`id` AS `lsg_game$id`,
`lsg_game`.`game_name` AS `lsg_game$game_name`,
`lsg_game`.`category_id` AS `lsg_game$category_id`
FROM
`lsg_category` AS `lsg_category`
RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id`
LEFT JOIN `lsg_game` AS `lsg_game` ON `lsg_physical`.`id` = `lsg_game`.`id` UNION
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
`lsg_game`.`id` AS `lsg_game$id`,
`lsg_game`.`game_name` AS `lsg_game$game_name`,
`lsg_game`.`category_id` AS `lsg_game$category_id`
FROM
`lsg_category` AS `lsg_category`
RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
RIGHT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id`
LEFT JOIN `lsg_game` AS `lsg_game` ON `lsg_physical`.`id` = `lsg_game`.`id` UNION
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`,
`lsg_game`.`id` AS `lsg_game$id`,
`lsg_game`.`game_name` AS `lsg_game$game_name`,
`lsg_game`.`category_id` AS `lsg_game$category_id`
FROM
`lsg_category` AS `lsg_category`
RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
RIGHT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id`
RIGHT JOIN `lsg_game` AS `lsg_game` ON `lsg_physical`.`id` = `lsg_game`.`id`
五张表的就不列了,以此类推,说明:每次增加一个full join,则新加一个union,而union的内容为他所关联的主表对应的那个union下的内容(直接拷贝下来,然后在末尾加上right join)。
3、如果既有full join,又有left join或者right join,如何处理呢?这个方式简单,每次遇到left join或者right join,只需要往每个union的最后加上left join或者right join即可。
我举个例子:(a full join b on a.id=b.id left join c on b.id=c.id)
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`
FROM
`lsg_category` AS `lsg_category`
LEFT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id` UNION
SELECT
`lsg_category`.`id` AS `lsg_category$id`,
`lsg_category`.`type` AS `lsg_category$type`,
`lsg_category`.`type_name` AS `lsg_category$type_name`,
`lsg_fruit`.`id` AS `lsg_fruit$id`,
`lsg_fruit`.`fruit_name` AS `lsg_fruit$fruit_name`,
`lsg_fruit`.`category_id` AS `lsg_fruit$category_id`,
`lsg_physical`.`id` AS `lsg_physical$id`,
`lsg_physical`.`physical_name` AS `lsg_physical$physical_name`,
`lsg_physical`.`category_id` AS `lsg_physical$category_id`
FROM
`lsg_category` AS `lsg_category`
RIGHT JOIN `lsg_fruit` AS `lsg_fruit` ON `lsg_category`.`id` = `lsg_fruit`.`category_id`
LEFT JOIN `lsg_physical` AS `lsg_physical` ON `lsg_fruit`.`id` = `lsg_physical`.`id`
批注:以上如果有不正确的地方,望指正,不胜感激。