参考:mysql left join 多个表
SQL:
select * from t_a a left join t_b b on a.bid = b.bid left join t_c c on a.cid = c.cid
t_a表
t_b表
t_c表
总的结果:
==============================================================
t_a表
t_b表
t_c表
总的结果表:
第二次带数据的sql:
- # MySQL-Front 3.2 (Build 14.3)
- /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
- /*!40103 SET TIME_ZONE=’SYSTEM’ */;
- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
- /*!40101 SET SQL_MODE=’STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’ */;
- /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
- /*!40103 SET SQL_NOTES=’ON’ */;
- # Host: 127.0.0.1 Database: jointest
- # ——————————————————
- # Server version 5.0.22-community-nt
- DROP DATABASE IF EXISTS `jointest`;
- CREATE DATABASE `jointest` /*!40100 DEFAULT CHARACTER SET utf8 */;
- USE `jointest`;
- #
- # Table structure for table t_a
- #
- CREATE TABLE `t_a` (
- `aid` int(11) NOT NULL auto_increment,
- `aname` varchar(11) default NULL,
- `bid` int(11) default NULL COMMENT ‘引用自t_b的bid’,
- `cid` int(11) default NULL COMMENT ‘引用自t_c的cid’,
- PRIMARY KEY (`aid`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- #
- # Dumping data for table t_a
- #
- INSERT INTO `t_a` VALUES (1,‘a名字1’,1,1);
- INSERT INTO `t_a` VALUES (2,‘a名字2’,NULL,2);
- #
- # Table structure for table t_b
- #
- CREATE TABLE `t_b` (
- `bid` int(11) NOT NULL auto_increment,
- `bname` varchar(11) default NULL,
- PRIMARY KEY (`bid`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- #
- # Dumping data for table t_b
- #
- INSERT INTO `t_b` VALUES (1,‘b名字’);
- #
- # Table structure for table t_c
- #
- CREATE TABLE `t_c` (
- `cid` int(11) NOT NULL auto_increment,
- `cname` varchar(11) default NULL,
- PRIMARY KEY (`cid`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- #
- # Dumping data for table t_c
- #
- INSERT INTO `t_c` VALUES (1,‘c名字’);
- INSERT INTO `t_c` VALUES (2,‘c名字2’);
- /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
- /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
- /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;