有3个表脚本如下:
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');SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb_3`
-- ----------------------------
DROP TABLE IF EXISTS `tb_3`;
CREATE TABLE `tb_3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`zp_no` varchar(10) DEFAULT NULL,
`sale_shop` varchar(20) DEFAULT NULL,
`addr` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb_3
-- ----------------------------
INSERT INTO `tb_3` VALUES ('1', '1001', '国美', '广州');
INSERT INTO `tb_3` VALUES ('2', '1002', '苏宁', '佛山');
INSERT INTO `tb_3` VALUES ('3', '1002', '沃尔玛', '珠海');
mysql 语句如何实现将3个表 查询结果如下:id zp_no pro_name ppp xxx
1 1001 灶具 (1)配件A,300 (2)配件B,100 (1)国美,广州
2 1002 消毒柜 (1)配件C,350 (1)苏宁,佛山 (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');SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tb_3`
-- ----------------------------
DROP TABLE IF EXISTS `tb_3`;
CREATE TABLE `tb_3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`zp_no` varchar(10) DEFAULT NULL,
`sale_shop` varchar(20) DEFAULT NULL,
`addr` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tb_3
-- ----------------------------
INSERT INTO `tb_3` VALUES ('1', '1001', '国美', '广州');
INSERT INTO `tb_3` VALUES ('2', '1002', '苏宁', '佛山');
INSERT INTO `tb_3` VALUES ('3', '1002', '沃尔玛', '珠海');
mysql 语句如何实现将3个表 查询结果如下:id zp_no pro_name ppp xxx
1 1001 灶具 (1)配件A,300 (2)配件B,100 (1)国美,广州
2 1002 消毒柜 (1)配件C,350 (1)苏宁,佛山 (2)沃尔玛,珠海
MySQL中的ROWNUM的实现
-> from (
-> select tb_1.id,zp_no,pro_name,group_concat(concat('()',name,',',price)) as ppp
-> from tb_1 inner join tb_2 using(zp_no)
-> group by tb_1.id,zp_no,pro_name
-> ) a inner join tb_3 using(zp_no)
-> group by a.id;
+----+-------+----------+-------------------------+---------------------------+
| id | zp_no | pro_name | ppp | xxx |
+----+-------+----------+-------------------------+---------------------------+
| 1 | 1001 | 灶具 | ()配件A,300,()配件B,100 | ()国美,广州 |
| 2 | 1002 | 消毒柜 | ()配件C,350 | ()苏宁,佛山,()沃尔玛,珠海 |
+----+-------+----------+-------------------------+---------------------------+
2 rows in set (0.01 sec)mysql>