mysql实现行转列作为临时表、以及字符分割行转列

最新推荐文章于 2023-06-27 18:38:03 发布

唯♧

最新推荐文章于 2023-06-27 18:38:03 发布

阅读量1.1k

点赞数

分类专栏：日常 MySQL 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/qq_37158147/article/details/128444387

版权

日常同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

MySQL

5 篇文章 0 订阅

订阅专栏

1.需求：实现两个日期段转换为具体的日期天数（2022-10-23至2022-10-26得到一张2022-10-23、2022-10-24、2022-10-25、2022-10-26的临时表）

SELECT
    DATE_FORMAT( DATE_ADD( '2022-10-23 22:00:00', INTERVAL ( help_topic_id ) DAY ), '%Y-%m-%d' ) AS days 
FROM
    mysql.help_topic 
WHERE
    help_topic_id <= TIMESTAMPDIFF(
        DAY,
    CONCAT( '2022-10-23 22:00:00' ),
    CONCAT( '2022-12-26 22:00:00' ))

help_topic本身是Mysql一个帮助解释注释表，用于解释Mysql各种专有名词，由于这张表数据ID是从0顺序增加的，方便我们用于计数，但是8.0.17版本的只有686条数据，超过这个数字，我们就需要己自定义一张表。

可以用做计数的临时表，查询的语句只会用help_topic计数，超出的部分其实都是脏数据。

help_topic 是数据库mysql的一个表，该表提供查询帮助主题给定关键字的详细内容（详细帮助信息）
表字段含义:

       help_topic_id：帮助主题详细信息在表记录中对应的ID（从0开始自增到686）
        name：帮助主题给定的关键字名称。
        help_category_id：帮助主题类别ID，与help_category表中的help_category_id字段值相等。
        description：帮助主题的详细信息（这里就是我们通常查询帮助信息真正想看的内容，例如：告诉我们某某语句如何使用的语法与注意事项等）。
        example：帮助主题的示例信息（告诉我们语句如何使用的示例）。
        url：该帮助主题对应在MySQL官方在线手册中的URL链接地址。

#查询本周第一天
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY) AS TIME

#查询本周的最后一天
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE())-6 DAY) AS TIME

#查询本月的第一天
SELECT DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY) AS TIME

#查询本月的最后一天
SELECT LAST_DAY(CURDATE()) AS TIME

#当前quarter的第一天：  
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM  CURDATE()),1) + interval QUARTER(CURDATE())*3-3 month),'%Y-%m-'),'01'); 
 
#当前quarter的最后一天：  
select LAST_DAY(MAKEDATE(EXTRACT(YEAR  FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-1 month);

#当年第一天：
SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY);
 
#当年最后一天：
SELECT concat(YEAR(now()),'-12-31'); 

#新增一个星期
SELECT DATE_ADD(now(), INTERVAL 1 week) time

#新增一个月
SELECT DATE_ADD(now(), INTERVAL 1 month) time

#新增一个季度
SELECT DATE_ADD(now(), INTERVAL 1 quarter) time

#新增一年
SELECT DATE_ADD(now(), INTERVAL 1 year) time

查询本日，本周，本月，本年的数据

#查询本日的数据
SELECT T.* FROM buz_sampling_data T
WHERE TO_DAYS(TIME) = TO_DAYS(NOW())


#查询本周的数据
SELECT T.* FROM buz_sampling_data T
WHERE YEARWEEK(TIME,1) = YEARWEEK(NOW(),1)


#查询本月的数据
SELECT T.* FROM buz_sampling_data T
WHERE MONTH(TIME) = MONTH(NOW())


#查询本年的数据
SELECT T.* FROM buz_sampling_data T
WHERE YEAR(TIME) = YEAR(NOW())

需要将字符串（电脑IP）192.168.211.35（按照.进行拆分）


SELECT
    SUBSTRING_INDEX( SUBSTRING_INDEX( t.ip, '.', h.help_topic_id + 1 ), '.', - 1 )
FROM
    test.test t
    JOIN mysql.help_topic h ON h.help_topic_id < ( LENGTH( t.ip ) - LENGTH( REPLACE ( t.ip, '.', '' )) + 1 );

SUBSTRING_INDEX(str,delim,count)函数

str: 要分割的字符串

delim: 分割符（比如逗号,和点.）

count: 计数（分割到第几个），正数表示从左向右数，负数表示从右向左数

LENGH函数

LENGTH(str) 统计字符串的长度

REPLACE(str,from_str,to_str) 替换字符串，

用to_str替换from_str得到最终的str

参数：

str：最终得到的字符串

from_str: 旧的字符（被替换的字符）

to_str: 新的字符

结果就是用新的字符替换旧的字符，得到的最终的字符串

SELECT REPLACE('192.168.211.35','.',''); -- 得到19216821135

SELECT LENGTH(REPLACE('192.168.211.35','.','')); -- 得到长度是11

遇到的问题
sql执行报错：SELECT command denied to user '###' for table 'help_topic'
SELECT命令拒绝用户 '###‘用于表’help_topic’

原因：
mysql用户没有执行查询help_topic表的权限，需要root用户授权。
解决
用mysql的root账户执行 GRANT SELECT ON mysql.help_topic TO 'wp'@'localhost'（给用户授予mysql.help_topic的查询权限）

创建用户
方式1： CREATE USER 'wp'@'localhost' IDENTIFIED BY '123456';
方式2： GRANT USAGE ON *.* TO 'wp'@'localhost' IDENTIFIED BY '132456';
删除用户
DROP USER 'wp'@'localhost';
查询用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
用户授权
GRANT SELECT ON mysql.help_topic TO 'wp'@'localhost'
取消用户授权
REVOKE SELECT ON mysql.help_topic FROM 'wp'@'localhost';
查询用户授权
SHOW GRANTS FOR 'wp'@'localhost'；