数据库版本
5.1.41-community MySQL Community Server (GPL)数据库结构及测试数据如下
DROP TABLE IF EXISTS `gout`;
CREATE TABLE IF NOT EXISTS `gout` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`desc` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;DROP TABLE IF EXISTS `goods`;
CREATE TABLE IF NOT EXISTS `goods` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`out_id` int(6) NOT NULL DEFAULT '0',
`name` varchar(50) CHARACTER SET gbk NOT NULL,
`qty` int(3) NOT NULL DEFAULT '0',
`wgt` decimal(7,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (`id`),
KEY `out_id` (`out_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;ALTER TABLE `goods` ADD CONSTRAINT `goods_barcode_ibfk_8` FOREIGN KEY (`out_id`) REFERENCES `gout` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;INSERT INTO `gout` (`id`, `date`, `desc`) VALUES
(1, '2010-04-09 00:00:00', NULL),
(2, '2010-04-20 00:00:00', NULL),
(3, '2010-04-25 00:00:00', NULL);INSERT INTO `goods`(`id`,`out_id`,`name`,`qty`,`wgt`) VALUES
(1, 1, '张三', 12, 5.6785),
(2, 1, '张三', 12, 5.2350),
(3, 1, '张三', 12, 5.8245),
(4, 1, '张三', 5, 2.5489),
(5, 2, '关二', 12, 8.5486),
(6, 2, '关二', 12, 8.4578),
(7, 2, '关二', 12, 8.5984),
(8, 2, '张三', 12, 5.5425),
(9, 2, '张三', 12, 5.4785),
(10, 2, '张三', 8, 3.9785),
(11, 3, '刘大', 12, 10.5470),
(12, 3, '关二', 12, 8.9542),
(13, 3, '张三', 12, 5.5553),
(14, 3, '张三', 12, 5.2458);期望结果
+----+---------------------+---------+------+------+------------------+
| id | ________date_______ | __swgt_ | sqty | desc | _____name_______ |
+----+---------------------+---------+------+------+------------------+
| _1 | 2010-04-09 00:00:00 | 19.2869 | _41_ | NULL | 张三____________ |
| _2 | 2010-04-20 00:00:00 | 40.6043 | _68_ | NULL | 关二,张三______ |
| _3 | 2010-04-25 00:00:00 | 30.3023 | _48_ | NULL | 刘大,关二,张三 |
+----+---------------------+---------+------+------+------------------+本来是想通过类似
select gout.id,date,sum(wgt) as swgt,sum(qty) as sqty,gout.desc,name from goods,gout where out_id = gout.id group by gout.id,name;
这样的语句查询出所有名称,然后再在PHP中根据gout.id组合成二维数组
但因为还需要使用 LIMIT 进行分页,所以用上面这语句就会使分页不准确了...不知道有没有什么别的方法能得到期望结果
5.1.41-community MySQL Community Server (GPL)数据库结构及测试数据如下
DROP TABLE IF EXISTS `gout`;
CREATE TABLE IF NOT EXISTS `gout` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`desc` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;DROP TABLE IF EXISTS `goods`;
CREATE TABLE IF NOT EXISTS `goods` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`out_id` int(6) NOT NULL DEFAULT '0',
`name` varchar(50) CHARACTER SET gbk NOT NULL,
`qty` int(3) NOT NULL DEFAULT '0',
`wgt` decimal(7,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (`id`),
KEY `out_id` (`out_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;ALTER TABLE `goods` ADD CONSTRAINT `goods_barcode_ibfk_8` FOREIGN KEY (`out_id`) REFERENCES `gout` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;INSERT INTO `gout` (`id`, `date`, `desc`) VALUES
(1, '2010-04-09 00:00:00', NULL),
(2, '2010-04-20 00:00:00', NULL),
(3, '2010-04-25 00:00:00', NULL);INSERT INTO `goods`(`id`,`out_id`,`name`,`qty`,`wgt`) VALUES
(1, 1, '张三', 12, 5.6785),
(2, 1, '张三', 12, 5.2350),
(3, 1, '张三', 12, 5.8245),
(4, 1, '张三', 5, 2.5489),
(5, 2, '关二', 12, 8.5486),
(6, 2, '关二', 12, 8.4578),
(7, 2, '关二', 12, 8.5984),
(8, 2, '张三', 12, 5.5425),
(9, 2, '张三', 12, 5.4785),
(10, 2, '张三', 8, 3.9785),
(11, 3, '刘大', 12, 10.5470),
(12, 3, '关二', 12, 8.9542),
(13, 3, '张三', 12, 5.5553),
(14, 3, '张三', 12, 5.2458);期望结果
+----+---------------------+---------+------+------+------------------+
| id | ________date_______ | __swgt_ | sqty | desc | _____name_______ |
+----+---------------------+---------+------+------+------------------+
| _1 | 2010-04-09 00:00:00 | 19.2869 | _41_ | NULL | 张三____________ |
| _2 | 2010-04-20 00:00:00 | 40.6043 | _68_ | NULL | 关二,张三______ |
| _3 | 2010-04-25 00:00:00 | 30.3023 | _48_ | NULL | 刘大,关二,张三 |
+----+---------------------+---------+------+------+------------------+本来是想通过类似
select gout.id,date,sum(wgt) as swgt,sum(qty) as sqty,gout.desc,name from goods,gout where out_id = gout.id group by gout.id,name;
这样的语句查询出所有名称,然后再在PHP中根据gout.id组合成二维数组
但因为还需要使用 LIMIT 进行分页,所以用上面这语句就会使分页不准确了...不知道有没有什么别的方法能得到期望结果
解决方案 »
- MySQL性能问题,使用固定内存,占用过多CPU
- mysql的问题,请求大侠指点,万谢
- 问个以其菜鸟的问题
- 关于编码
- SQL文实现数据插入的自动发番
- 在繁体WINXP下,客户端用ODBC为何连不上MYSQL服务器啊
- Linux上的mysql4.0.18,用jdbc连接,总是报错: Unsupported character encoding 'UTF-8',怎么办啦
- 在线请教:一个mssql的问题``
- 如何在安装程序中自动安装mysql并配置呢
- mysql写入图片不成功
- mysqldump 。。dump了几天了。。 0.0~
- 咨询一下 mysql的myisam和innodb引擎如何处理多用户对某一数据的同时操作?
+----+---------------------+---------+------+------+---------------+
| id | ________date_______ | __swgt_ | sqty | desc | _____name____ |
+----+---------------------+---------+------+------+---------------+
| _1 | 2010-04-09 00:00:00 | 19.2869 | _41_ | NULL | 张三__________ |
| _2 | 2010-04-20 00:00:00 | 40.6043 | _68_ | NULL | 关二,张三_____ |
| _3 | 2010-04-25 00:00:00 | 30.3023 | _48_ | NULL | 刘大,关二,张三 |
+----+---------------------+---------+------+------+----------------+
-> from gout a inner join goods b on a.id=b.out_id
-> group by a.id,a.date,a.desc;
+----+---------------------+------------+------------+------+-------------------------------+
| id | date | sum(b.wgt) | sum(b.qty) | desc | group_concat(DISTINCT b.name) |
+----+---------------------+------------+------------+------+-------------------------------+
| 1 | 2010-04-09 00:00:00 | 19.2869 | 41 | NULL | 张三 |
| 2 | 2010-04-20 00:00:00 | 40.6043 | 68 | NULL | 关二,张三 |
| 3 | 2010-04-25 00:00:00 | 30.3023 | 48 | NULL | 刘大,关二,张三 |
+----+---------------------+------------+------------+------+-------------------------------+
3 rows in set (0.00 sec)mysql>