来自 澳门威尼斯人注册网站 2019-12-29 05:22 的文章
当前位置: 澳门威尼斯人平台 > 澳门威尼斯人注册网站 > 正文

MySQL 查询结果以百分比显示澳门威尼斯人注册网站

#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中利用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);

  [示例]

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

本文由澳门威尼斯人平台发布于澳门威尼斯人注册网站,转载请注明出处:MySQL 查询结果以百分比显示澳门威尼斯人注册网站

关键词: