MySQL 查询结果以百分比展现

发布时间:2019-03-31  栏目:SQL  评论:0 Comments

#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()

  1、【CONCAT(str1,str2,…)

  再次回到来自于参数连结的字符串。如果此外参数是NULL,
重临NULL。能够有超过三个的参数。一个数字参数被变换为等价的字符串格局。

  [示例]

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

  -> ‘MySQL’

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

  -> NULL

  select CONCAT(14.3);

  -> ‘14.3’

  】

  2、【LEFT(str,length)

  从左先导截取字符串.表明:left(被截取字段,截取长度)

  】

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

http://www.bkjia.com/Mysql/434746.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/434746.htmlTechArticle找了一些资料,然后我是用到了MySQL字符串处理中的两个函数concat()和left()
① 、【CONCAT(str1,str2,…)
再次回到来自于参数连结的字符串。借使其余参…

留下评论

网站地图xml地图