数据库版本
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 进行分页,所以用上面这语句就会使分页不准确了...不知道有没有什么别的方法能得到期望结果
解决方案 »
- 初学postgres:(c/s结构)关于多个客户端对数据库进行操作的疑问,以及定时问题?
- com.mysql.jdbc.CommunicationsException
- mysql5.0.22不支持存储过程调用存储过程吗?
- 在mysql存储过程中如何创建Tempoary表
- 在linux下把mysql的MyISAM的表(frm,MYD,MYI)拷过去后出现read only,请教
- My SQL和SQL SERVER2000,哪一个功能更强大呢?
- 请教各位关于POSTGRESQL的性能问题!!
- 菜鸟问题:怎样关闭mysql服务器
- mysql,select for update解锁后怎么再延时2,3秒左右?
- mysql 1064.
- 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>