Mysql方法收集（持续更新）

1、自动生成日期表

1.1 创建表

CREATE TABLE `date_warehouse` (
  `date` date DEFAULT NULL COMMENT '日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='日期仓库';

1.2 存储过程（1）创建指定日期到今天

DELIMITER $$
CREATE PROCEDURE generate_date ()
BEGIN
    declare indexdate Date;
    set indexdate=date("2022-01-01");--指定日期
    while indexdate <= curdate()
    do
        insert into date_warehouse(date) values (indexdate);
        set indexdate = adddate(indexdate,1);
    end while;
END;

1.3 存储过程（2）创建指定表中最后一天到今天

DELIMITER $$
CREATE PROCEDURE generate_date ()
BEGIN
    declare indexdate Date;
    set indexdate=(SELECT adddate(date,1)date FROM date_warehouse ORDER BY date DESC LIMIT 1);
    while indexdate <= curdate()
    do
    	--查询日期表中最后一个日期并加一天插入表
        insert into date_warehouse(date) SELECT adddate(date,1)date FROM date_warehouse ORDER BY date DESC LIMIT 1;
        set indexdate = (SELECT adddate(date,1)date FROM date_warehouse ORDER BY date DESC LIMIT 1);
    end while;
END;

1.4 执行指定存储过程

CALL generate_date()

1.5 删除指定存储过程

--generate_date为存储过程名称
drop procedure generate_date

2、数据库查询的日期转字符串格式

date_format(date, '%Y-%m-%d')

3、对逗号分割的数据进行分组统计

3.1 对rice_type进行分组统计

期望结果：

3.2 统计sql语句

SELECT
	s.rice_type,
	COUNT(*) count 
FROM
	(
	SELECT
		'1' AS rice_type UNION ALL
	SELECT
		'2' AS rice_type UNION ALL
	SELECT
		'3' AS rice_type 
	) s
	JOIN consume_asq u ON find_in_set( s.rice_type, u.rice_type ) 
WHERE
	YEAR = '2021'
GROUP BY
	s.rice_type

3.3 注释

--1、2、3分别代表rice_type会出现且需要统计的情况，相当于一个字典表。
--如果存在另外一个字典表直接关联即可，这里是手动生成的一个关联表。
	(
	SELECT
		'1' AS rice_type UNION ALL
	SELECT
		'2' AS rice_type UNION ALL
	SELECT
		'3' AS rice_type 
	) s

4、mysql按条件分组取每组前五（必须是数字类型）

4.1 方法一

 SELECT t.* from (
        SELECT
             t1.*,
             (SELECT count(*) + 1 FROM 表名 t2 WHERE t2.分组条件 = t1.分组条件 AND t2.排序字段 > t1.排序字段 ) top
         FROM
         表名 t1
     ) t where top <=5 order by t.分组条件,top

4.1 方法二

 SELECT
        t1.*    
    FROM
    表名 t1
    where (SELECT count(*) + 1 FROM 表名 t2 WHERE t2.分组条件 = t1.分组条件 AND t2.排序字段 > t1.排序字段 ) <=5
    ORDER BY t1.分组条件 desc,t1.排序字段 desc

4.1 方法三

SELECT
        t1.*    
    FROM
    表名 t1
    where exists (SELECT count(*) + 1 FROM info t2 WHERE t2.分组条件 = t1.分组条件 AND t2.排序字段 > t1.排序字段 having (count(*) + 1) <= 5) 
    ORDER BY t1.分组条件 desc,t1.排序字段 desc

5、连表查询更新

UPDATE orders o
JOIN customers c
ON o.customer_id = c.customer_id
SET o.total_amount = o.total_amount * 1.1,
    c.last_order_date = NOW()
WHERE o.order_date BETWEEN '2021-01-01' AND '2021-12-31';

6、用mysql实现按条件分组并且每组去时间最大的一条

实现逻辑 ：先查出每组最大的，再和结果关联

可以考虑使用子查询或者连接查询实现。以下是两种语法：

6.1 子查询 ：

SELECT *
FROM 表名
WHERE (组内条件, 时间) IN (
    SELECT 组内条件, MAX(时间)
    FROM 表名
    GROUP BY 组内条件
);

其中，组内条件是按照哪个字段进行分组的条件，可以是一个或多个字段。时间是需求中要筛选的时间字段，假设为time字段。这个查询会返回满足组内条件下时间最大的记录。

6.2 连接查询 ：

SELECT t1.*
FROM 表名 t1
INNER JOIN (
    SELECT 组内条件, MAX(时间) as max_time
    FROM 表名
    GROUP BY 组内条件
) t2 
ON t1.组内条件 = t2.组内条件 AND t1.时间 = t2.max_time;

其中，组内条件和时间的含义同上。这个查询会先用子查询找到每组对应的最大时间，再用连接查询将原表中的记录与这些最大时间进行匹配并返回相应的记录。