建表语句CREATE TABLE `PA` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `SPEC` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`pid` int(4) NOT NULL,
`color_item_no` int(3) NOT NULL,
`color` varchar(100) CHARACTER SET gbk NOT NULL,
`size_item_no` int(3) NOT NULL,
`size` varchar(10) CHARACTER SET gbk NOT NULL,
`jar_no` varchar(75) CHARACTER SET gbk NOT NULL,
`qty` int(10) NOT NULL DEFAULT '0',
`wgt` decimal(7,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `SPEC_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `PA` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
添加数据INSERT INTO `PA`(`id`,`date`) VALUES
(1,'2010-03-27');INSERT INTO `SPEC`(`pid`,`color_item_no`,`color`,`size_item_no`,`size`,`jar_no`,`qty`,`wgt`) VALUES
(1,1,'RED',1,'S','HD780',30,3.000),
(1,1,'RED',2,'M','HD780',50,3.550),
(1,1,'RED',4,'XL','HD780',20,4.300),
(1,2,'BULE',3,'L','HD390',30,4.000),
(1,2,'BULE',4,'XL','HD390',25,4.300),
(1,3,'YELLOW',1,'S','HD347',100,3.000),
(1,4,'BLACK',2,'M','HD459',90,3.550),
(1,4,'BLACK',4,'XL','HD459',70,4.300);
期望结果TABLE1
+-----+-------+-----+---+---+---+----+
| ROW | COLOR | JAR | S | M | L | XL |
+-----+-------+-----+---+---+---+----+
| 1 | RED |HD780| 30| 50| | 20 |
+-----+-------+-----+---+---+---+----+
| 2 | BULE |HD390| | | 30| 25 |
+-----+-------+-----+---+---+---+----+
| 3 |YELLOW |HD347|100| | | |
+-----+-------+-----+---+---+---+----+
| 4 | BLACK |HD459| | 90| | 70 |
+-----+-------+-----+---+---+---+----+TABLE2
+------+-----+
| SIZE | WGT |
+------+-----+
| S |3.000|
+------+-----+
| M |3.550|
+------+-----+
| L |4.000|
+------+-----+
| XL |4.300|
+------+-----+这2个是要在HTML上显示的表
现在我是通过
SELECT * FROM SPEC,PA WHERE SPEC.pid = PA.id AND PA.id = 1
这语句查询后,然后再在后台用PHP来将结果以类似
Array{
Array [1]{
Array [0] {
[xxx] = xxx
}
Array [1] {
[xxx] = xxx
}
Array [2] {
[xxx] = xxx
}
}
Array [2] {
Array [0] {
[xxx] = xxx
}
Array [1] {
[xxx] = xxx
}
}
Array [3] {
Array [0] {
[xxx] = xxx
}
}
Array [4] {
Array [0] {
[xxx] = xxx
}
Array [1] {
[xxx] = xxx
}
}
}
这样的数组来保存,最后在HTML上显示出来
但这样查询出来的有很多重复数据(比如color_item_no为1的,color、jar_no这2个数据就出现多次了)
有没有比较简单的查询能够简化操作,达到上面所期望的2个表格呢?
`id` int(8) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `SPEC` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`pid` int(4) NOT NULL,
`color_item_no` int(3) NOT NULL,
`color` varchar(100) CHARACTER SET gbk NOT NULL,
`size_item_no` int(3) NOT NULL,
`size` varchar(10) CHARACTER SET gbk NOT NULL,
`jar_no` varchar(75) CHARACTER SET gbk NOT NULL,
`qty` int(10) NOT NULL DEFAULT '0',
`wgt` decimal(7,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `SPEC_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `PA` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
添加数据INSERT INTO `PA`(`id`,`date`) VALUES
(1,'2010-03-27');INSERT INTO `SPEC`(`pid`,`color_item_no`,`color`,`size_item_no`,`size`,`jar_no`,`qty`,`wgt`) VALUES
(1,1,'RED',1,'S','HD780',30,3.000),
(1,1,'RED',2,'M','HD780',50,3.550),
(1,1,'RED',4,'XL','HD780',20,4.300),
(1,2,'BULE',3,'L','HD390',30,4.000),
(1,2,'BULE',4,'XL','HD390',25,4.300),
(1,3,'YELLOW',1,'S','HD347',100,3.000),
(1,4,'BLACK',2,'M','HD459',90,3.550),
(1,4,'BLACK',4,'XL','HD459',70,4.300);
期望结果TABLE1
+-----+-------+-----+---+---+---+----+
| ROW | COLOR | JAR | S | M | L | XL |
+-----+-------+-----+---+---+---+----+
| 1 | RED |HD780| 30| 50| | 20 |
+-----+-------+-----+---+---+---+----+
| 2 | BULE |HD390| | | 30| 25 |
+-----+-------+-----+---+---+---+----+
| 3 |YELLOW |HD347|100| | | |
+-----+-------+-----+---+---+---+----+
| 4 | BLACK |HD459| | 90| | 70 |
+-----+-------+-----+---+---+---+----+TABLE2
+------+-----+
| SIZE | WGT |
+------+-----+
| S |3.000|
+------+-----+
| M |3.550|
+------+-----+
| L |4.000|
+------+-----+
| XL |4.300|
+------+-----+这2个是要在HTML上显示的表
现在我是通过
SELECT * FROM SPEC,PA WHERE SPEC.pid = PA.id AND PA.id = 1
这语句查询后,然后再在后台用PHP来将结果以类似
Array{
Array [1]{
Array [0] {
[xxx] = xxx
}
Array [1] {
[xxx] = xxx
}
Array [2] {
[xxx] = xxx
}
}
Array [2] {
Array [0] {
[xxx] = xxx
}
Array [1] {
[xxx] = xxx
}
}
Array [3] {
Array [0] {
[xxx] = xxx
}
}
Array [4] {
Array [0] {
[xxx] = xxx
}
Array [1] {
[xxx] = xxx
}
}
}
这样的数组来保存,最后在HTML上显示出来
但这样查询出来的有很多重复数据(比如color_item_no为1的,color、jar_no这2个数据就出现多次了)
有没有比较简单的查询能够简化操作,达到上面所期望的2个表格呢?
解决方案 »
- mysql与windows编码问题
- 一个虚拟主机带Mysql数据库,如何写个API可以实现对这个数据库的增删改查呢
- SQL语句日期问题!!!!!!!!!!
- 用Java还原数据库时操作成功,但是数据库中没有数据,求高手帮忙解决
- 如何用sql在一个表内完成按字段值重复记录拆分成多个表
- 如何随机取出10条记录,并按照id降序排列呢,在线等
- 哎,PostgreSql(windows)的数据库效率太不高了!
- 散分,升到5个三角了
- mysql服务已经开了,但是项目一直连接不到本地服务器,求助
- 有使用mysql-proxy的朋友么,使用proxy的情况下,在db端看不到最终来源,怎样解决?
- 查询所有字段,格式化个别字段
- MFC中连接mysql中的一个问题,要怎么解决呢?
MYSQL 版本:5.1.41-community MySQL Community Server (GPL)