一个以记录集为条件查询并进行分组排序筛选的SQL请教 MYSQL复杂查询排序 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 基于楼主提供的测试数据,正确的结果是什么样? 建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。 参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样,(并给以简单的算法描述) 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。 /*SQLyog 企业版 - MySQL GUIMySQL - 5.6.16 **********************************************************************//*!40101 SET NAMES utf8 */;create table `store_info` ( `bianhao` varchar (30), `shuliang` double ); insert into `store_info` (`bianhao`, `shuliang`) values('2014038','7');insert into `store_info` (`bianhao`, `shuliang`) values('2014037','3');insert into `store_info` (`bianhao`, `shuliang`) values('2014036','1');insert into `store_info` (`bianhao`, `shuliang`) values('2014035','9');insert into `store_info` (`bianhao`, `shuliang`) values('2014034','3');insert into `store_info` (`bianhao`, `shuliang`) values('2014033','1');insert into `store_info` (`bianhao`, `shuliang`) values('2014032','2');insert into `store_info` (`bianhao`, `shuliang`) values('2014031','6');insert into `store_info` (`bianhao`, `shuliang`) values('2014030','3');insert into `store_info` (`bianhao`, `shuliang`) values('2014029','3');insert into `store_info` (`bianhao`, `shuliang`) values('2014028','3');insert into `store_info` (`bianhao`, `shuliang`) values('2014027','9');insert into `store_info` (`bianhao`, `shuliang`) values('2014026','5');insert into `store_info` (`bianhao`, `shuliang`) values('2014025','19');insert into `store_info` (`bianhao`, `shuliang`) values('2014024','7');insert into `store_info` (`bianhao`, `shuliang`) values('2014023','4');insert into `store_info` (`bianhao`, `shuliang`) values('2014022','3');insert into `store_info` (`bianhao`, `shuliang`) values('2014021','6');insert into `store_info` (`bianhao`, `shuliang`) values('2014020','1');insert into `store_info` (`bianhao`, `shuliang`) values('2014019','7');insert into `store_info` (`bianhao`, `shuliang`) values('2014018','8');insert into `store_info` (`bianhao`, `shuliang`) values('2014017','5');insert into `store_info` (`bianhao`, `shuliang`) values('2014016','4');insert into `store_info` (`bianhao`, `shuliang`) values('2014015','4');insert into `store_info` (`bianhao`, `shuliang`) values('2014014','1');insert into `store_info` (`bianhao`, `shuliang`) values('2014013','10');insert into `store_info` (`bianhao`, `shuliang`) values('2014012','9');insert into `store_info` (`bianhao`, `shuliang`) values('2014011','4');insert into `store_info` (`bianhao`, `shuliang`) values('2014010','5');insert into `store_info` (`bianhao`, `shuliang`) values('2014009','10');insert into `store_info` (`bianhao`, `shuliang`) values('2014008','11');insert into `store_info` (`bianhao`, `shuliang`) values('2014007','8');insert into `store_info` (`bianhao`, `shuliang`) values('2014006','1');insert into `store_info` (`bianhao`, `shuliang`) values('2014005','15');insert into `store_info` (`bianhao`, `shuliang`) values('2014004','7');insert into `store_info` (`bianhao`, `shuliang`) values('2014003','7');insert into `store_info` (`bianhao`, `shuliang`) values('2014002','1');insert into `store_info` (`bianhao`, `shuliang`) values('2014001','5');insert into `store_info` (`bianhao`, `shuliang`) values('2013153','4');insert into `store_info` (`bianhao`, `shuliang`) values('2013152','9');insert into `store_info` (`bianhao`, `shuliang`) values('2013151','2');insert into `store_info` (`bianhao`, `shuliang`) values('2013150','6');insert into `store_info` (`bianhao`, `shuliang`) values('2013149','6');insert into `store_info` (`bianhao`, `shuliang`) values('2013148','2');insert into `store_info` (`bianhao`, `shuliang`) values('2013147','3');insert into `store_info` (`bianhao`, `shuliang`) values('2013146','6');insert into `store_info` (`bianhao`, `shuliang`) values('2013145','2');insert into `store_info` (`bianhao`, `shuliang`) values('2013144','3');insert into `store_info` (`bianhao`, `shuliang`) values('2013143','3');insert into `store_info` (`bianhao`, `shuliang`) values('2013142','6');insert into `store_info` (`bianhao`, `shuliang`) values('2013141','5');insert into `store_info` (`bianhao`, `shuliang`) values('2013140','1');insert into `store_info` (`bianhao`, `shuliang`) values('2013139','5');insert into `store_info` (`bianhao`, `shuliang`) values('2013138','3');insert into `store_info` (`bianhao`, `shuliang`) values('2013137','4');insert into `store_info` (`bianhao`, `shuliang`) values('2013136','5');insert into `store_info` (`bianhao`, `shuliang`) values('2013135','4');insert into `store_info` (`bianhao`, `shuliang`) values('2013134','2');insert into `store_info` (`bianhao`, `shuliang`) values('2013133','2');insert into `store_info` (`bianhao`, `shuliang`) values('2013132','23');insert into `store_info` (`bianhao`, `shuliang`) values('2013131','12');insert into `store_info` (`bianhao`, `shuliang`) values('2013130','8');insert into `store_info` (`bianhao`, `shuliang`) values('2013129','11');insert into `store_info` (`bianhao`, `shuliang`) values('2013128','11');insert into `store_info` (`bianhao`, `shuliang`) values('2013127','3');insert into `store_info` (`bianhao`, `shuliang`) values('2013126','9');insert into `store_info` (`bianhao`, `shuliang`) values('2013125','4');insert into `store_info` (`bianhao`, `shuliang`) values('2013124','5');insert into `store_info` (`bianhao`, `shuliang`) values('2013123','2');insert into `store_info` (`bianhao`, `shuliang`) values('2013122','15');insert into `store_info` (`bianhao`, `shuliang`) values('2013121','3');insert into `store_info` (`bianhao`, `shuliang`) values('2013120','6');insert into `store_info` (`bianhao`, `shuliang`) values('2013119','1');insert into `store_info` (`bianhao`, `shuliang`) values('2013118','4');insert into `store_info` (`bianhao`, `shuliang`) values('2013117','5');建表脚本思路,我先条件查询当天shuliang=3的数据,并把bianhao+1可以得到隔天的数据,然后对查询到的记录集进行group by和order by,但是当再增加一个 where条件 count(*)>3时就会报错,因为涉及到条件是记录集的问题。这里我就不知道怎么做了。select shuliang,count(*) from store_info where bianhao in (select bianhao+1 from lottery_dlt_info where shuliang = 3) group by shuliang order by count(*) desc 用having count(*)>3 order by 2 如何判断数据中是否存在空值? 这条mysql语句explain看起来已经很优化的了,但是执行起来要十几秒 mysql触发器的插入操作,如何进行判断 请人优化sql数据库 请教一个SQL查询语句写法。。 请高手指点!! 在mysql 中 创建表时 表字段加上注释 vc+mysql, 如何插入100万条记录?求实例代码 MySQL4.1.12a难道不支持InnoDB表类型吗? 请教,关于mySQL网络连接的问题! mysql 日期格式运算 Order by 以字段序号来排序,单不知道字段名 mysql 调用存储过程报1365错误
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
/*
SQLyog 企业版 - MySQL GUI
MySQL - 5.6.16
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;create table `store_info` (
`bianhao` varchar (30),
`shuliang` double
);
insert into `store_info` (`bianhao`, `shuliang`) values('2014038','7');
insert into `store_info` (`bianhao`, `shuliang`) values('2014037','3');
insert into `store_info` (`bianhao`, `shuliang`) values('2014036','1');
insert into `store_info` (`bianhao`, `shuliang`) values('2014035','9');
insert into `store_info` (`bianhao`, `shuliang`) values('2014034','3');
insert into `store_info` (`bianhao`, `shuliang`) values('2014033','1');
insert into `store_info` (`bianhao`, `shuliang`) values('2014032','2');
insert into `store_info` (`bianhao`, `shuliang`) values('2014031','6');
insert into `store_info` (`bianhao`, `shuliang`) values('2014030','3');
insert into `store_info` (`bianhao`, `shuliang`) values('2014029','3');
insert into `store_info` (`bianhao`, `shuliang`) values('2014028','3');
insert into `store_info` (`bianhao`, `shuliang`) values('2014027','9');
insert into `store_info` (`bianhao`, `shuliang`) values('2014026','5');
insert into `store_info` (`bianhao`, `shuliang`) values('2014025','19');
insert into `store_info` (`bianhao`, `shuliang`) values('2014024','7');
insert into `store_info` (`bianhao`, `shuliang`) values('2014023','4');
insert into `store_info` (`bianhao`, `shuliang`) values('2014022','3');
insert into `store_info` (`bianhao`, `shuliang`) values('2014021','6');
insert into `store_info` (`bianhao`, `shuliang`) values('2014020','1');
insert into `store_info` (`bianhao`, `shuliang`) values('2014019','7');
insert into `store_info` (`bianhao`, `shuliang`) values('2014018','8');
insert into `store_info` (`bianhao`, `shuliang`) values('2014017','5');
insert into `store_info` (`bianhao`, `shuliang`) values('2014016','4');
insert into `store_info` (`bianhao`, `shuliang`) values('2014015','4');
insert into `store_info` (`bianhao`, `shuliang`) values('2014014','1');
insert into `store_info` (`bianhao`, `shuliang`) values('2014013','10');
insert into `store_info` (`bianhao`, `shuliang`) values('2014012','9');
insert into `store_info` (`bianhao`, `shuliang`) values('2014011','4');
insert into `store_info` (`bianhao`, `shuliang`) values('2014010','5');
insert into `store_info` (`bianhao`, `shuliang`) values('2014009','10');
insert into `store_info` (`bianhao`, `shuliang`) values('2014008','11');
insert into `store_info` (`bianhao`, `shuliang`) values('2014007','8');
insert into `store_info` (`bianhao`, `shuliang`) values('2014006','1');
insert into `store_info` (`bianhao`, `shuliang`) values('2014005','15');
insert into `store_info` (`bianhao`, `shuliang`) values('2014004','7');
insert into `store_info` (`bianhao`, `shuliang`) values('2014003','7');
insert into `store_info` (`bianhao`, `shuliang`) values('2014002','1');
insert into `store_info` (`bianhao`, `shuliang`) values('2014001','5');
insert into `store_info` (`bianhao`, `shuliang`) values('2013153','4');
insert into `store_info` (`bianhao`, `shuliang`) values('2013152','9');
insert into `store_info` (`bianhao`, `shuliang`) values('2013151','2');
insert into `store_info` (`bianhao`, `shuliang`) values('2013150','6');
insert into `store_info` (`bianhao`, `shuliang`) values('2013149','6');
insert into `store_info` (`bianhao`, `shuliang`) values('2013148','2');
insert into `store_info` (`bianhao`, `shuliang`) values('2013147','3');
insert into `store_info` (`bianhao`, `shuliang`) values('2013146','6');
insert into `store_info` (`bianhao`, `shuliang`) values('2013145','2');
insert into `store_info` (`bianhao`, `shuliang`) values('2013144','3');
insert into `store_info` (`bianhao`, `shuliang`) values('2013143','3');
insert into `store_info` (`bianhao`, `shuliang`) values('2013142','6');
insert into `store_info` (`bianhao`, `shuliang`) values('2013141','5');
insert into `store_info` (`bianhao`, `shuliang`) values('2013140','1');
insert into `store_info` (`bianhao`, `shuliang`) values('2013139','5');
insert into `store_info` (`bianhao`, `shuliang`) values('2013138','3');
insert into `store_info` (`bianhao`, `shuliang`) values('2013137','4');
insert into `store_info` (`bianhao`, `shuliang`) values('2013136','5');
insert into `store_info` (`bianhao`, `shuliang`) values('2013135','4');
insert into `store_info` (`bianhao`, `shuliang`) values('2013134','2');
insert into `store_info` (`bianhao`, `shuliang`) values('2013133','2');
insert into `store_info` (`bianhao`, `shuliang`) values('2013132','23');
insert into `store_info` (`bianhao`, `shuliang`) values('2013131','12');
insert into `store_info` (`bianhao`, `shuliang`) values('2013130','8');
insert into `store_info` (`bianhao`, `shuliang`) values('2013129','11');
insert into `store_info` (`bianhao`, `shuliang`) values('2013128','11');
insert into `store_info` (`bianhao`, `shuliang`) values('2013127','3');
insert into `store_info` (`bianhao`, `shuliang`) values('2013126','9');
insert into `store_info` (`bianhao`, `shuliang`) values('2013125','4');
insert into `store_info` (`bianhao`, `shuliang`) values('2013124','5');
insert into `store_info` (`bianhao`, `shuliang`) values('2013123','2');
insert into `store_info` (`bianhao`, `shuliang`) values('2013122','15');
insert into `store_info` (`bianhao`, `shuliang`) values('2013121','3');
insert into `store_info` (`bianhao`, `shuliang`) values('2013120','6');
insert into `store_info` (`bianhao`, `shuliang`) values('2013119','1');
insert into `store_info` (`bianhao`, `shuliang`) values('2013118','4');
insert into `store_info` (`bianhao`, `shuliang`) values('2013117','5');建表脚本思路,我先条件查询当天shuliang=3的数据,并把bianhao+1可以得到隔天的数据,然后对查询到的记录集进行group by和order by,但是当再增加一个 where条件 count(*)>3时就会报错,因为涉及到条件是记录集的问题。这里我就不知道怎么做了。select shuliang,count(*) from store_info where bianhao in (
select bianhao+1 from lottery_dlt_info where shuliang = 3
) group by shuliang order by count(*) desc
order by 2