数据库版本
MySQL 5.1.41数据结构及测试数据
DROP TABLE IF EXISTS `t1`;
CREATE TABLE IF NOT EXISTS `t1`(
`pid` INT(1) NOT NULL,
`cid1` INT(1) NOT NULL DEFAULT '1',
PRIMARY KEY(`pid`,`cid1`)
)engine=InnoDB;DROP TABLE IF EXISTS `t2`;
CREATE TABLE IF NOT EXISTS `t2`(
`pid` INT(1) NOT NULL,
`cid2` INT(1) NOT NULL DEFAULT '1'
)engine=InnoDB;INSERT INTO `t1` VALUES
(1,2),
(1,4);INSERT INTO `t2` VALUES
(1,1),
(1,1),
(1,2),
(1,2),
(1,3),
(1,3),
(1,3),
(1,2),
(1,2);
期望结果
+------+------+
| cid2 | cid1 |
+------+------+
| 1 | NULL |
| 2 | 2 |
| 3 | NULL |
| NULL | 4 |
+------+------+我试着用
SELECT DISTINCT `cid2`,`cid1` FROM `t2` LEFT JOIN `t1` ON (`t2`.`pid`=`t1`.`pid`) WHERE `t2`.`pid`=1;
结果和想要的结果相差很多,有办法得到我期望的结果吗??
MySQL 5.1.41数据结构及测试数据
DROP TABLE IF EXISTS `t1`;
CREATE TABLE IF NOT EXISTS `t1`(
`pid` INT(1) NOT NULL,
`cid1` INT(1) NOT NULL DEFAULT '1',
PRIMARY KEY(`pid`,`cid1`)
)engine=InnoDB;DROP TABLE IF EXISTS `t2`;
CREATE TABLE IF NOT EXISTS `t2`(
`pid` INT(1) NOT NULL,
`cid2` INT(1) NOT NULL DEFAULT '1'
)engine=InnoDB;INSERT INTO `t1` VALUES
(1,2),
(1,4);INSERT INTO `t2` VALUES
(1,1),
(1,1),
(1,2),
(1,2),
(1,3),
(1,3),
(1,3),
(1,2),
(1,2);
期望结果
+------+------+
| cid2 | cid1 |
+------+------+
| 1 | NULL |
| 2 | 2 |
| 3 | NULL |
| NULL | 4 |
+------+------+我试着用
SELECT DISTINCT `cid2`,`cid1` FROM `t2` LEFT JOIN `t1` ON (`t2`.`pid`=`t1`.`pid`) WHERE `t2`.`pid`=1;
结果和想要的结果相差很多,有办法得到我期望的结果吗??
-> union
-> select cid2,cid1 from t2 right join t1 on cid2=cid1 ;
+------+------+
| cid2 | cid1 |
+------+------+
| 1 | NULL |
| 2 | 2 |
| 3 | NULL |
| NULL | 4 |
+------+------+
4 rows in set (0.06 sec)mysql>
看到UNION才想起来...这要求貌似以前学校里老师就教过了....Orz