mysql 根据日期分组后再分组统计数据

-- 存在则删除重新创建存储过程
DROP PROCEDURE IF EXISTS get_goods_item_count;
-- 创建存储过程
CREATE PROCEDURE get_goods_item_count()
BEGIN
    SELECT
    date_format(FROM_UNIXTIME(create_time),'%Y-%m-%d') AS '日期',
    SUM( CASE WHEN goods_price = "198.00" THEN 1 ELSE 0 END ) "198总单数",
    SUM( CASE WHEN goods_price = "198.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "198接单数",
    
    SUM( CASE WHEN goods_price = "258.00" THEN 1 ELSE 0 END ) "258总单数",
    SUM( CASE WHEN goods_price = "258.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "258接单数",
    
    SUM( CASE WHEN goods_price = "298.00" THEN 1 ELSE 0 END ) "298总单数",
    SUM( CASE WHEN goods_price = "298.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "298接单数",
    
    SUM( CASE WHEN goods_price = "398.00" THEN 1 ELSE 0 END ) "398总单数",
    SUM( CASE WHEN goods_price = "398.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "398接单数",
    
    SUM( CASE WHEN goods_price = "498.00" THEN 1 ELSE 0 END ) "498总单数",
    SUM( CASE WHEN goods_price = "498.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "498接单数",
        
    SUM( CASE WHEN goods_price = "598.00" THEN 1 ELSE 0 END ) "598总单数",
    SUM( CASE WHEN goods_price = "598.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "598接单数",
    
    SUM( CASE WHEN goods_price = "698.00" THEN 1 ELSE 0 END ) "698总单数",
    SUM( CASE WHEN goods_price = "698.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "698接单数"

FROM
    (SELECT id, goods_price, create_time, date_format(FROM_UNIXTIME(create_time),'%Y-%m-%d') AS date, is_accept FROM anmo_orders ORDER BY id desc) as t
GROUP BY t.date
ORDER BY date desc;
END;


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

-- 查看全部存储过程
show PROCEDURE STATUS


    SELECT
    date_format(FROM_UNIXTIME(create_time),'%Y-%m-%d') AS '日期',
    SUM( CASE WHEN goods_price = "198.00" THEN 1 ELSE 0 END ) "198总单数",
    SUM( CASE WHEN goods_price = "198.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "198接单数",
    
    SUM( CASE WHEN goods_price = "258.00" THEN 1 ELSE 0 END ) "258总单数",
    SUM( CASE WHEN goods_price = "258.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "258接单数",
    
    SUM( CASE WHEN goods_price = "298.00" THEN 1 ELSE 0 END ) "298总单数",
    SUM( CASE WHEN goods_price = "298.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "298接单数",
    
    SUM( CASE WHEN goods_price = "398.00" THEN 1 ELSE 0 END ) "398总单数",
    SUM( CASE WHEN goods_price = "398.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "398接单数",
    
    SUM( CASE WHEN goods_price = "498.00" THEN 1 ELSE 0 END ) "498总单数",
    SUM( CASE WHEN goods_price = "498.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "498接单数",
        
    SUM( CASE WHEN goods_price = "598.00" THEN 1 ELSE 0 END ) "598总单数",
    SUM( CASE WHEN goods_price = "598.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "598接单数",
    
    SUM( CASE WHEN goods_price = "698.00" THEN 1 ELSE 0 END ) "698总单数",
    SUM( CASE WHEN goods_price = "698.00" AND is_accept = 2 THEN 1 ELSE 0 END ) "698接单数"

FROM
    (SELECT id, goods_price, create_time, date_format(FROM_UNIXTIME(create_time),'%Y-%m-%d') AS date, is_accept FROM anmo_orders ORDER BY id desc) as t
GROUP BY t.date
ORDER BY date desc
;