有2个表:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb_1`
-- ----------------------------
DROP TABLE IF EXISTS `tb_1`;
CREATE TABLE `tb_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`zp_no` varchar(10) DEFAULT NULL,
`pro_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb_1
-- ----------------------------
INSERT INTO `tb_1` VALUES ('1', '1001', '灶具');
INSERT INTO `tb_1` VALUES ('2', '1002', '消毒柜');SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb_2`
-- ----------------------------
DROP TABLE IF EXISTS `tb_2`;
CREATE TABLE `tb_2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`zp_no` varchar(10) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`price` varchar(20) DEFAULT NULL,
`date1` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb_2
-- ----------------------------
INSERT INTO `tb_2` VALUES ('1', '1001', '配件A', '300', '2010-08-20');
INSERT INTO `tb_2` VALUES ('2', '1001', '配件B', '100', '2010-08-19');
INSERT INTO `tb_2` VALUES ('3', '1002', '配件C', '350', '2010-08-19');
mysql语句如何实现结果如下:
| id | zp_no | pro_name | name1 | price1 | name2 | price2
+----+-------+----------+-------------------------+---------------------------+
| 1 | 1001 | 灶具 | 配件A | 300 | 配件B | 100
| 2 | 1002 | 消毒柜 | 配件C | 350 | null | null
+----+-------+----------+-------------------------+---------------------------+
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb_1`
-- ----------------------------
DROP TABLE IF EXISTS `tb_1`;
CREATE TABLE `tb_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`zp_no` varchar(10) DEFAULT NULL,
`pro_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb_1
-- ----------------------------
INSERT INTO `tb_1` VALUES ('1', '1001', '灶具');
INSERT INTO `tb_1` VALUES ('2', '1002', '消毒柜');SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb_2`
-- ----------------------------
DROP TABLE IF EXISTS `tb_2`;
CREATE TABLE `tb_2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`zp_no` varchar(10) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`price` varchar(20) DEFAULT NULL,
`date1` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb_2
-- ----------------------------
INSERT INTO `tb_2` VALUES ('1', '1001', '配件A', '300', '2010-08-20');
INSERT INTO `tb_2` VALUES ('2', '1001', '配件B', '100', '2010-08-19');
INSERT INTO `tb_2` VALUES ('3', '1002', '配件C', '350', '2010-08-19');
mysql语句如何实现结果如下:
| id | zp_no | pro_name | name1 | price1 | name2 | price2
+----+-------+----------+-------------------------+---------------------------+
| 1 | 1001 | 灶具 | 配件A | 300 | 配件B | 100
| 2 | 1002 | 消毒柜 | 配件C | 350 | null | null
+----+-------+----------+-------------------------+---------------------------+
http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html