#mysql中 对于查询结果只显示n条连续行的问题#

在领扣上碰到的一个题目:求满足条件的连续3行结果的显示

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people;
Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).
For example, the table stadium:
+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

For the sample data above, the output is:
+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

1.首先先进行结果集的查询

select id,date,people from stadium where people>=100;

2.给查询的结果集增加一个自增列

SELECT @newid:=@newid+1 AS newid,test.* 
FROM(SELECT @newid:=0)r, test WHERE people>100

3.自增列和id的差值 相同即连续

SELECT @newid:=@newid+1 AS newid,test.* ,@cha:=id-@newid AS cha 
FROM(SELECT @newid:=0)r, test WHERE people>100

4.将相同的差值 放在同一张表中,并取出连续数量大于3的

select if(count(id)>=3,count_concat(id),null)e from(
SELECT @newid:=@newid+1 AS newid,test.* ,@cha:=id-@newid AS cha 
FROM(SELECT @newid:=0)r, test WHERE people>100)
as d group by cha

5.将上步得到的表和主表 取得所需要的

SELECT id,DATE,people FROM test,
(SELECT IF (COUNT(id)>3,GROUP_CONCAT(id),NULL)e 
FROM (SELECT @newid:=@newid+1 AS newid,test.* ,@cha:=id-@newid AS cha 
FROM(SELECT @newid:=0)r, test WHERE people>100)AS d   GROUP BY cha ) AS f 
WHERE f.e IS NOT NULL AND FIND_IN_SET(id,f.e);

听说还可以用存储过程来完成,不过我没尝试,稍后尝试

以上

   找了一些资料,然后我是用到了MySQL字符串处理中的两个函数concat()和left()

MySQL 查询in操作,查询结果按in集合顺序显示

MySQL 查询in操作,查询结果按in集合顺序显示

在mysql中利用select语句的一个特性就可以很方便地实现查询结果的分页,select语句的语法:
    SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT]
[SQL_BIG_RESULT]澳门皇冠金沙网站, [HIGH_PRIORITY]
        [DISTINCT | DISTINCTROW | ALL]
        select_expression,…
        [INTO OUTFILE ‘file_name’ export_options]
        [FROM table_references
       ][WHERE where_definition]
       [GROUP BY col_name,…]
       [HAVING where_definition]
       [ORDER BY {unsigned_integer | col_name | formula} ][ASC |
DESC] ,…]
       [LIMIT ][offset,] rows]
       [PROCEDURE procedure_name] ]
LIMIT子句可以用来限制由SELECT语句返回过来的数据数量,它有一个或两个参数,如果给出两个参数,
第一个参数指定返回的第一行在所有数据中的位置,从0开始(注意不是1),第二个参数指定最多返回行
数。例如:
     select * from table LIMIT 5,10; #返回第6-15行数据
     select * from table LIMIT 5;    #返回前5行
     select * from table LIMIT 0,5;    #返回前5行

  1、【CONCAT(str1,str2,…)

复制代码 代码如下:

复制代码 代码如下:

  返回来自于参数连结的字符串。如果任何参数是NULL,
返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。

select * from test where id in(3,1,5) order by
find_in_set(id,’3,1,5′);
select * from test where id in(3,1,5) order by
substring_index(‘3,1,2’,id,1);

select * from test where id in(3,1,5) order by
find_in_set(id,’3,1,5′);
select * from test where id in(3,1,5) order by
substring_index(‘3,1,2’,id,1);

  [示例]

偶尔看到的。。。或许有人会注意过,但我以前真不知道
SQL: select * from table where id IN (3,6,9,1,2,5,8,7);

偶尔看到的。。。或许有人会注意过,但我以前真不知道
SQL: select * from table where id IN (3,6,9,1,2,5,8,7);

  select CONCAT(‘My’, ‘S’, ‘QL’);

这样的情况取出来后,其实,id还是按1,2,3,4,5,6,7,8,9,排序的,但如果我们真要按IN里面的顺序排序怎么办?SQL能不能完成?是否需要取回来后再foreach一下?其实mysql就有这个方法

这样的情况取出来后,其实,id还是按1,2,3,4,5,6,7,8,9,排序的,但如果我们真要按IN里面的顺序排序怎么办?SQL能不能完成?是否需要取回来后再foreach一下?其实mysql就有这个方法

  -> ‘MySQL’

sql: select * from table where id IN (3,6,9,1,2,5,8,7) order by
field(id,3,6,9,1,2,5,8,7);

sql: select * from table where id IN (3,6,9,1,2,5,8,7) order by
field(id,3,6,9,1,2,5,8,7);

  select CONCAT(‘My’, NULL, ‘QL’);

出来的顺序就是指定的顺序了。。。。这个,以前还真的从来没用过,偶尔看到,所以就记录了一下。一是做个笔记,二是希望可以给更多的人看到

出来的顺序就是指定的顺序了。。。。这个,以前还真的从来没用过,偶尔看到,所以就记录了一下。一是做个笔记,二是希望可以给更多的人看到

  -> NULL

MySQL中NOT IN语句对NULL值的处理

MySQL中NOT IN语句对NULL值的处理

  select CONCAT(14.3);

mysql> SELECT COUNT(name) FROM CVE WHERE name NOT IN
(‘CVE-1999-0001’, ‘CVE-1999-0002’);
+————-+
| count(name) |
+————-+
| 17629 |
+————-+
1 row in set (0.02 sec)
mysql> SELECT COUNT(name) FROM CVE WHERE name NOT IN
(‘CVE-1999-0001’, ‘CVE-1999-0002’, NULL);
+————-+
| count(name) |
+————-+
| 0 |
+————-+
1 row in set (0.01 sec)
当在子查询中出现NULL的时候,结果就一定是0了。查了一下手册,确实有这样的说法。所以最后实际采用了这样的查询:
SELECT COUNT(DISTINCT name)
FROM CVE
WHERE name NOT IN (SELECT cveID FROM cve_sig WHERE cveID IS NOT NULL)
顺便提一下MySQL中正则表达式匹配的简单使用:
SELECT COUNT(alarmID)
FROM Alarm
WHERE (CVE NOT RLIKE ‘^CVE-[0-9]{4}-[0-9]{4}$’ OR CVE IS NULL)
当然,RLIKE也可以写作REGEXP,我个人倾向于使用RLIKE,因为拼写接近LIKE,可以见名知义。

mysql> SELECT COUNT(name) FROM CVE WHERE name NOT IN
(‘CVE-1999-0001’, ‘CVE-1999-0002’);
+————-+
| count(name) |
+————-+
| 17629 |
+————-+
1 row in set (0.02 sec)
mysql> SELECT COUNT(name) FROM CVE WHERE name NOT IN
(‘CVE-1999-0001’, ‘CVE-1999-0002’, NULL);
+————-+
| count(name) |
+————-+
| 0 |
+————-+
1 row in set (0.01 sec)
当在子查询中出现NULL的时候,结果就一定是0了。查了一下手册,确实有这样的说法。所以最后实际采用了这样的查询:
SELECT COUNT(DISTINCT name)
FROM CVE
WHERE name NOT IN (SELECT cveID FROM cve_sig WHERE cveID IS NOT NULL)
顺便提一下MySQL中正则表达式匹配的简单使用:
SELECT COUNT(alarmID)
FROM Alarm
WHERE (CVE NOT RLIKE ‘^CVE-[0-9]{4}-[0-9]{4}$’ OR CVE IS NULL)
当然,RLIKE也可以写作REGEXP,我个人倾向于使用RLIKE,因为拼写接近LIKE,可以见名知义。

  -> ‘14.3’

mysql – not in table:info primary key(id, info_type_id)
id, info_type_id, programme_id, episode_id
3, 4, 382, 100034
3, 8, 382, 100034
4, 8, 382, 100034
6, 8, 382, 100034
7, 8, 382, 100034
8, 8, 382, 100034
9, 8, 382, 100034
10, 8, 382, 100034
11, 8, 382, 100034
12, 8, 382, 100034
13, 8, 382, 100034
100001, 4, 382, 100034
100002, 4, 382, 100034

mysql – not in table:info primary key(id, info_type_id)
id, info_type_id, programme_id, episode_id
3, 4, 382, 100034
3, 8, 382, 100034
4, 8, 382, 100034
6, 8, 382, 100034
7, 8, 382, 100034
8, 8, 382, 100034
9, 8, 382, 100034
10, 8, 382, 100034
11, 8, 382, 100034
12, 8, 382, 100034
13, 8, 382, 100034
100001, 4, 382, 100034
100002, 4, 382, 100034

  】

排除(id=3 && info_type_id=8) and (id=4 &&
info_type_id=8)這兩條記錄,即找出其它記錄
error: select * from info where episode_id=100034 and id not in(3,4)
and info_type_id not in (8);
error result:
id, info_type_id, programme_id, episode_id
100001, 4, 382, 100034
100002, 4, 382, 100034
correct: select * from info where episode_id=100034 and (id<>3
or info_type_id<>8) and (id<>4 or
info_type_id<>8);
correct result:
id, info_type_id, programme_id, episode_id
3, 4, 382, 100034
6, 8, 382, 100034
7, 8, 382, 100034
8, 8, 382, 100034
9, 8, 382, 100034
10, 8, 382, 100034
11, 8, 382, 100034
12, 8, 382, 100034
13, 8, 382, 100034
100001, 4, 382, 100034
100002, 4, 382, 100034
理解:id<>3 or info_type_id<>8排除掉id=3 &&
info_type_id=8這條記錄,當表中主鍵多于一個時,不能簡單地使用key1 NOT IN
(……) AND key2 NOT IN (……) ..

排除(id=3 && info_type_id=8) and (id=4 &&
info_type_id=8)這兩條記錄,即找出其它記錄
error: select * from info where episode_id=100034 and id not in(3,4)
and info_type_id not in (8);
error result:
id, info_type_id, programme_id, episode_id
100001, 4, 382, 100034
100002, 4, 382, 100034
correct: select * from info where episode_id=100034 and (id<>3
or info_type_id<>8) and (id<>4 or
info_type_id<>8);
correct result:
id, info_type_id, programme_id, episode_id
3, 4, 382, 100034
6, 8, 382, 100034
7, 8, 382, 100034
8, 8, 382, 100034
9, 8, 382, 100034
10, 8, 382, 100034
11, 8, 382, 100034
12, 8, 382, 100034
13, 8, 382, 100034
100001, 4, 382, 100034
100002, 4, 382, 100034
理解:id<>3 or info_type_id<>8排除掉id=3 &&
info_type_id=8這條記錄,當表中主鍵多于一個時,不能簡單地使用key1 NOT IN
(……) AND key2 NOT IN (……) ..

  2、【LEFT(str,length)

查询in操作,查询结果按in集合顺序显示
复制代码 代码如下: select * from test where id in(3,1,5) order by
find_in_set(id,’3,1,5′); select * from test whe…

您可能感兴趣的文章:

  • mysql中模糊查询的四种用法介绍
  • 解析mysql中:单表distinct、多表group
    by查询去除重复记录
  • Mysql联合查询UNION和UNION
    ALL的使用介绍
  • MySql查询时间段的方法
  • MySQL中基本的多表连接查询教程
  • mysql分页原理和高效率的mysql分页查询语句
  • MySQL里面的子查询实例
  • mysql show processlist
    显示mysql查询进程
  • MySQL查询本周、上周、本月、上个月份数据的sql代码
  • 关于MySQL中的查询开销查看方法详解

  从左开始截取字符串.说明:left(被截取字段,截取长度)

  】

  结合1、2 :concat ( left (数值1 / 数值2 *100,5),’%’) as 投诉率

)
1、【CONCAT(str1,str2,…)
返回来自于参数连结的字符串。如果任何参…

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图