join left on多张表_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 */;

大小: 3.4 KB

大小: 3.3 KB

大小: 3.9 KB

大小: 6.2 KB

大小: 3.8 KB

大小: 4.2 KB

大小: 3 KB

大小: 6.2 KB

分享到：

2011-03-16 17:09

浏览 36056

分类:数据库

评论