mysql sum中加条件判断，性能哪个更好？

最新推荐文章于 2022-05-09 09:00:00 发布

weixin_34417200

最新推荐文章于 2022-05-09 09:00:00 发布

阅读量1.9k

点赞数

文章标签：数据库 python java

原文链接：https://my.oschina.net/WWWW23223/blog/501012

版权

2019独角兽企业重金招聘Python工程师标准>>>

SELECT
   SUM(

       IF(
           MONTH(B.CREATE_TIME)= '01',
           B.GOODS_NUM,
           0
       )
   ),
   SUM(

       IF(
           MONTH(B.CREATE_TIME)= '02',
           B.GOODS_NUM,
           0
       )
   ),
   SUM(

       IF(
           MONTH(B.CREATE_TIME)= '03',
           B.GOODS_NUM,
           0
       )
   ),
   SUM(

       IF(
           MONTH(B.CREATE_TIME)= '04',
           B.GOODS_NUM,
           0
       )
   ),
   SUM(

       IF(
           MONTH(B.CREATE_TIME)= '05',
           B.GOODS_NUM,
           0
       )
   ),
   SUM(

       IF(
           MONTH(B.CREATE_TIME)= '06',
           B.GOODS_NUM,
           0
       )
   ),
   SUM(

       IF(
           MONTH(B.CREATE_TIME)= '07',
           B.GOODS_NUM,
           0
       )
   ),
   SUM(

       IF(
           MONTH(B.CREATE_TIME)= '08',
           B.GOODS_NUM,
           0
       )
   ),
   SUM(

       IF(
           MONTH(B.CREATE_TIME)= '09',
           B.GOODS_NUM,
           0
       )
   ),
   SUM(

       IF(
           MONTH(B.CREATE_TIME)= '10',
           B.GOODS_NUM,
           0
       )
   ),
   SUM(

       IF(
           MONTH(B.CREATE_TIME)= '11',
           B.GOODS_NUM,
           0
       )
   ),
   SUM(

       IF(
           MONTH(B.CREATE_TIME)= '12',
           B.GOODS_NUM,
           0
       )
   )
FROM

order_goods AS B

WHERE

YEAR(B.CREATE_TIME)= '2015'

GROUP BY

B.GOODS_ID

--------------------------------------------------------

SELECT
SUM(
       CASE
       WHEN MONTH(B.CREATE_TIME)= "01" THEN
       B.GOODS_NUM
ELSE
       0
       END
   ),
SUM(
       CASE
       WHEN MONTH(B.CREATE_TIME)= "02" THEN
       B.GOODS_NUM
ELSE
       0
       END
   ),
SUM(
       CASE
       WHEN MONTH(B.CREATE_TIME)= "03" THEN
       B.GOODS_NUM
ELSE
       0
       END
   ),
SUM(
       CASE
       WHEN MONTH(B.CREATE_TIME)= "04" THEN
       B.GOODS_NUM
ELSE
       0
       END
   ),
SUM(
       CASE
       WHEN MONTH(B.CREATE_TIME)= "05" THEN
       B.GOODS_NUM
ELSE
       0
       END
   ),
SUM(
       CASE
       WHEN MONTH(B.CREATE_TIME)= "06" THEN
       B.GOODS_NUM
ELSE
       0
       END
   ),
SUM(
       CASE
       WHEN MONTH(B.CREATE_TIME)= "07" THEN
       B.GOODS_NUM
ELSE
       0
       END
   ),
SUM(
       CASE
       WHEN MONTH(B.CREATE_TIME)= "08" THEN
       B.GOODS_NUM
ELSE
       0
       END
   ),
SUM(
       CASE
       WHEN MONTH(B.CREATE_TIME)= "09" THEN
       B.GOODS_NUM
ELSE
       0
       END
   ),
SUM(
       CASE
       WHEN MONTH(B.CREATE_TIME)= "10" THEN
       B.GOODS_NUM
ELSE
       0
       END
   ),
SUM(
       CASE
       WHEN MONTH(B.CREATE_TIME)= "11" THEN
       B.GOODS_NUM
ELSE
       0
       END
   ),
SUM(
       CASE
       WHEN MONTH(B.CREATE_TIME)= "12" THEN
       B.GOODS_NUM
ELSE
       0
       END
   )

FROM
   order_goods B
WHERE
   YEAR(B.CREATE_TIME)= "2015"
GROUP BY
   B.GOODS_ID