MySQL

来源：互联网发布：淘宝外卖和口碑的区别编辑：程序博客网时间：2024/05/16 03:28

案例背景：按周统计周中每天销售额。

要求结果如下图所示：

这里写图片描述

常用方式如下：

【1】子查询

SELECT    week_year,    (        SELECT            SUM(net_price)        FROM            goods_sale        WHERE            day_week = 2        AND week_year = gs.week_year        AND year_num = 2016    ) AS 'Monday',    (        SELECT            SUM(net_price)        FROM            goods_sale        WHERE            day_week = 3        AND week_year = gs.week_year        AND year_num = 2016    ) AS 'Tuesday',    (        SELECT            SUM(net_price)        FROM            goods_sale        WHERE            day_week = 4        AND week_year = gs.week_year        AND year_num = 2016    ) AS 'Wednesday',    (        SELECT            SUM(net_price)        FROM            goods_sale        WHERE            day_week = 5        AND week_year = gs.week_year        AND year_num = 2016    ) AS 'Thursday',    (        SELECT            SUM(net_price)        FROM            goods_sale        WHERE            day_week = 6        AND week_year = gs.week_year        AND year_num = 2016    ) AS 'Friday',    (        SELECT            SUM(net_price)        FROM            goods_sale        WHERE            day_week = 7        AND week_year = gs.week_year        AND year_num = 2016    ) AS 'Saturday',    (        SELECT            SUM(net_price)        FROM            goods_sale        WHERE            day_week = 1        AND week_year = gs.week_year        AND year_num = 2016    ) AS 'Sunday'FROM    goods_sale gsWHERE    1 = 1AND year_num = 2016GROUP BY    gs.week_year;

抛开表分区不谈，这种方式效率很低。

结果如下图所示，耗时167.180S：

这里写图片描述

【2】IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

实例如下：

SELECT    gs.week_year,    sum(if(gs.day_week=1,gs.net_price,0)) AS 'Sunday',    sum(if(gs.day_week=2,gs.net_price,0)) AS 'Monday',    sum(if(gs.day_week=3,gs.net_price,0)) AS 'Tuesday',    sum(if(gs.day_week=4,gs.net_price,0)) AS 'Wednesday',    sum(if(gs.day_week=5,gs.net_price,0)) AS 'Thursday',    sum(if(gs.day_week=6,gs.net_price,0)) AS 'Friday',    sum(if(gs.day_week=7,gs.net_price,0)) AS 'Saturday',    sum(gs.net_price)FROM    goods_sale gsWHERE    1 = 1AND gs.year_num = 2016GROUP BY    gs.week_year

效率显然比第一种方式(子查询)要高，结果如下(耗时0.119S)：

这里写图片描述

【3】CASE…WEHN..THEN..ELSE..END

实例如下 :

SELECT    week_year,    SUM(        (            CASE gs.day_week            WHEN 1 THEN                gs.net_price            ELSE                0            END        )    ) Sunday,    SUM(        (            CASE gs.day_week            WHEN 2 THEN                gs.net_price            ELSE                0            END        )    ) Monday,    SUM(        (            CASE gs.day_week            WHEN 3 THEN                gs.net_price            ELSE                0            END        )    ) Tuesday,    SUM(        (            CASE gs.day_week            WHEN 4 THEN                gs.net_price            ELSE                0            END        )    ) Wednesday,    SUM(        (            CASE gs.day_week            WHEN 5 THEN                gs.net_price            ELSE                0            END        )    ) Thursday,    SUM(        (            CASE gs.day_week            WHEN 6 THEN                gs.net_price            ELSE                0            END        )    ) Friday,    SUM(        (            CASE gs.day_week            WHEN 7 THEN                gs.net_price            ELSE                0            END        )    ) Saturday,    SUM(gs.net_price)FROM    goods_sale gsWHERE    year_num = 2016GROUP BY    week_year

效率比子查询显然快。结果如下，耗时0.101S：

这里写图片描述

综上，MySQL行转列时，要综合考虑运用函数提高效率！

阅读全文

0 0