mysql查询问题 Ecshop商城 批量查询ecshop 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 应该是可以的,group by分组应该可以 建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。 参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样,(并给以简单的算法描述) 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。 sorry~ 服务器: localhost via TCP/IP软件: MySQL软件版本: 5.5.38 - MySQL Community Server (GPL)协议版本: 10用户: root@localhost服务器字符集: UTF-8 Unicode (utf8) 结构有点大~ 现在简化了下 结构如下现在就是要取出 巴旦木、杏仁果、肉松饼....(下面就只是列了这三种,有N种商品) 在某个时间内的销售量取所有的我就知道用用count~ , 要分别列出各商品的就不知道怎么弄了 ---- 表的结构 `ec_goods`--CREATE TABLE IF NOT EXISTS `ec_goods` ( `goods_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `cat_id` smallint(5) unsigned NOT NULL DEFAULT '0', `goods_sn` varchar(60) NOT NULL DEFAULT '', `goods_name` varchar(120) NOT NULL DEFAULT '', `shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00', `goods_brief` varchar(255) NOT NULL DEFAULT '', `goods_desc` text NOT NULL, `add_time` int(10) unsigned NOT NULL DEFAULT '0', `sort_order` smallint(4) unsigned NOT NULL DEFAULT '100', `is_delete` tinyint(1) unsigned NOT NULL DEFAULT '0', `is_best` tinyint(1) unsigned NOT NULL DEFAULT '0', `is_new` tinyint(1) unsigned NOT NULL DEFAULT '0', `is_hot` tinyint(1) unsigned NOT NULL DEFAULT '0', `last_update` int(10) unsigned NOT NULL DEFAULT '0', `goods_type` smallint(5) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`goods_id`), KEY `goods_sn` (`goods_sn`), KEY `cat_id` (`cat_id`), KEY `last_update` (`last_update`),) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=73 ;---- 转存表中的数据 `ec_goods`--INSERT INTO `ec_goods` (`goods_id`, `goods_sn`, `goods_name`, `shop_price`, `goods_brief`, `goods_desc`, `add_time`, `is_delete`, `is_best`, `is_new`, `is_hot`, `last_update`) VALUES(1, 'ECS000001', '巴旦木', '19.80', '这里是brief', '这里是desc', 1405626416, 0, 0, 0, 0, 1410462880),(2, 'ECS000002', '杏仁果', '19.80', '这里是brief', '这里是desc', 1405626441, 0, 0, 0, 0, 1410313489);(3, 'ECS000003', '肉松饼', '10.10', '这里是brief', '这里是desc', 1405626446, 0, 0, 0, 0, 1410473689);CREATE TABLE IF NOT EXISTS `ec_order_goods` ( `rec_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `order_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `goods_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `goods_name` varchar(120) NOT NULL DEFAULT '', `goods_sn` varchar(60) NOT NULL DEFAULT '', `goods_number` smallint(5) unsigned NOT NULL DEFAULT '1', `goods_price` decimal(10,2) NOT NULL DEFAULT '0.00', PRIMARY KEY (`rec_id`), KEY `order_id` (`order_id`), KEY `goods_id` (`goods_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;---- 转存表中的数据 `ec_order_goods`--INSERT INTO `ec_order_goods` (`rec_id`, `order_id`, `goods_id`, `goods_name`, `goods_sn`, `goods_number`, `goods_price`,) VALUES(1, 1, 1, '巴旦木', 'ECS000001', 1, '19.80'),(2, 1, 2, '杏仁果', 'ECS000002', 2, '19.80');(3, 2, 1, '巴旦木', 'ECS000001', 4, '19.80'),(4, 2, 2, '杏仁果', 'ECS000002', 2, '19.80');(5, 3, 1, '巴旦木', 'ECS000001', 2, '19.80'),(6, 3, 2, '杏仁果', 'ECS000002', 5, '19.80');(7, 4, 1, '巴旦木', 'ECS000001', 2, '19.80'),(8, 4, 2, '杏仁果', 'ECS000002', 6, '19.80');(9, 4, 3, '肉松饼', 'ECS000003', 3, '10.80'),(10, 5, 3, '肉松饼', 'ECS000003', 2, '10.80');---- 结构 `ec_order_info`--CREATE TABLE IF NOT EXISTS `ec_order_info` ( `order_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `order_sn` varchar(20) NOT NULL DEFAULT '', `user_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `add_time` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`order_id`), UNIQUE KEY `order_sn` (`order_sn`), KEY `user_id` (`user_id`),) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1493 ;---- 数据 `ec_order_info`--INSERT INTO `ec_order_info` (`order_id`, `order_sn`, `user_id`, `add_time`) VALUES(1, '2014-05-10-008', 1137, '2014-05-10 15:00'),(2, '2014-05-10-009', 1157, '2014-05-11 12:00'),(3, '2014-05-10-010', 1158, '2014-05-11 16:00');(4, '2014-05-10-010', 1158, '2014-05-12 11:00');(5, '2014-05-10-010', 1158, '2014-05-12 13:00'); KO , 谢谢诸位select g.goods_name,count(1) from ec_order_info as o,ec_order_goods as g where g.order_id = o.order_id and best_time like '2014-09-15%' group by g.goods_id 请问个数据查询的问题 请问如何批量递增式查询,请高手赐教? 不显示删除回复显示所有回复显示星级回复显示得分回复 ==============如何写这样的Sql语句=================== mysqldump备份和还原数据库的一个疑问 避免重复插入 关于MYSQL的数据导入问题 怎么将SQL数据库备份文件*.dmp导入到MySql中啊! MySql能象access那样不需要启动服务,单个数据库直接做为程序后台数据库吗? mysql的遍历次数问题 请问在98下如何用Mysql提供的头文件和静态库编写的客户端程序? mysql-proxy读写分离失败 datalist动态行转列绑定
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
sorry~
服务器: localhost via TCP/IP
软件: MySQL
软件版本: 5.5.38 - MySQL Community Server (GPL)
协议版本: 10
用户: root@localhost
服务器字符集: UTF-8 Unicode (utf8) 结构有点大~ 现在简化了下 结构如下现在就是要取出 巴旦木、杏仁果、肉松饼....(下面就只是列了这三种,有N种商品) 在某个时间内的销售量
取所有的我就知道用用count~ , 要分别列出各商品的就不知道怎么弄了
--
-- 表的结构 `ec_goods`
--CREATE TABLE IF NOT EXISTS `ec_goods` (
`goods_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`cat_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`goods_sn` varchar(60) NOT NULL DEFAULT '',
`goods_name` varchar(120) NOT NULL DEFAULT '',
`shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`goods_brief` varchar(255) NOT NULL DEFAULT '',
`goods_desc` text NOT NULL,
`add_time` int(10) unsigned NOT NULL DEFAULT '0',
`sort_order` smallint(4) unsigned NOT NULL DEFAULT '100',
`is_delete` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_best` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_new` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_hot` tinyint(1) unsigned NOT NULL DEFAULT '0',
`last_update` int(10) unsigned NOT NULL DEFAULT '0',
`goods_type` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`goods_id`),
KEY `goods_sn` (`goods_sn`),
KEY `cat_id` (`cat_id`),
KEY `last_update` (`last_update`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=73 ;--
-- 转存表中的数据 `ec_goods`
--INSERT INTO `ec_goods` (`goods_id`, `goods_sn`, `goods_name`, `shop_price`, `goods_brief`, `goods_desc`, `add_time`, `is_delete`, `is_best`, `is_new`, `is_hot`, `last_update`) VALUES
(1, 'ECS000001', '巴旦木', '19.80', '这里是brief', '这里是desc', 1405626416, 0, 0, 0, 0, 1410462880),
(2, 'ECS000002', '杏仁果', '19.80', '这里是brief', '这里是desc', 1405626441, 0, 0, 0, 0, 1410313489);
(3, 'ECS000003', '肉松饼', '10.10', '这里是brief', '这里是desc', 1405626446, 0, 0, 0, 0, 1410473689);CREATE TABLE IF NOT EXISTS `ec_order_goods` (
`rec_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`order_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`goods_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`goods_name` varchar(120) NOT NULL DEFAULT '',
`goods_sn` varchar(60) NOT NULL DEFAULT '',
`goods_number` smallint(5) unsigned NOT NULL DEFAULT '1',
`goods_price` decimal(10,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`rec_id`),
KEY `order_id` (`order_id`),
KEY `goods_id` (`goods_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;--
-- 转存表中的数据 `ec_order_goods`
--INSERT INTO `ec_order_goods` (`rec_id`, `order_id`, `goods_id`, `goods_name`, `goods_sn`, `goods_number`, `goods_price`,) VALUES
(1, 1, 1, '巴旦木', 'ECS000001', 1, '19.80'),
(2, 1, 2, '杏仁果', 'ECS000002', 2, '19.80');
(3, 2, 1, '巴旦木', 'ECS000001', 4, '19.80'),
(4, 2, 2, '杏仁果', 'ECS000002', 2, '19.80');
(5, 3, 1, '巴旦木', 'ECS000001', 2, '19.80'),
(6, 3, 2, '杏仁果', 'ECS000002', 5, '19.80');
(7, 4, 1, '巴旦木', 'ECS000001', 2, '19.80'),
(8, 4, 2, '杏仁果', 'ECS000002', 6, '19.80');
(9, 4, 3, '肉松饼', 'ECS000003', 3, '10.80'),
(10, 5, 3, '肉松饼', 'ECS000003', 2, '10.80');
--
-- 结构 `ec_order_info`
--CREATE TABLE IF NOT EXISTS `ec_order_info` (
`order_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`order_sn` varchar(20) NOT NULL DEFAULT '',
`user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`add_time` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`order_id`),
UNIQUE KEY `order_sn` (`order_sn`),
KEY `user_id` (`user_id`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1493 ;--
-- 数据 `ec_order_info`
--INSERT INTO `ec_order_info` (`order_id`, `order_sn`, `user_id`, `add_time`) VALUES
(1, '2014-05-10-008', 1137, '2014-05-10 15:00'),
(2, '2014-05-10-009', 1157, '2014-05-11 12:00'),
(3, '2014-05-10-010', 1158, '2014-05-11 16:00');
(4, '2014-05-10-010', 1158, '2014-05-12 11:00');
(5, '2014-05-10-010', 1158, '2014-05-12 13:00');