mysql 日期仅保留年月

-- 创建示例表
CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO sales (sale_date, amount) VALUES
('2023-01-15', 100.00),
('2023-02-20', 150.00),
('2023-03-10', 200.00);

-- 查询并格式化日期
SELECT DATE_FORMAT(sale_date, '%Y-%m') AS year_month, SUM(amount) AS total_amount
FROM sales
GROUP BY year_month;

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE FormatSaleDate()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_date DATE;
    DECLARE cur_amount DECIMAL(10, 2);
    DECLARE cur_id INT;
    DECLARE cur_year_month VARCHAR(7);

    -- 假设有一个临时表 temp_sales 存储格式化后的数据
    CREATE TEMPORARY TABLE temp_sales (
        id INT,
        year_month VARCHAR(7),
        amount DECIMAL(10, 2)
    );

    DECLARE cur CURSOR FOR SELECT id, sale_date, amount FROM sales;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO cur_id, cur_date, cur_amount;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET cur_year_month = DATE_FORMAT(cur_date, '%Y-%m');
        INSERT INTO temp_sales (id, year_month, amount) VALUES (cur_id, cur_year_month, cur_amount);
    END LOOP;

    CLOSE cur;

    -- 清空原表并插入格式化后的数据
    TRUNCATE TABLE sales;
    INSERT INTO sales (id, sale_date, amount)
    SELECT id, STR_TO_DATE(year_month, '%Y-%m') AS sale_date, amount FROM temp_sales;

    DROP TEMPORARY TABLE temp_sales;
END //
DELIMITER ;

-- 调用存储过程
CALL FormatSaleDate();