代码之家  ›  专栏  ›  技术社区  ›  Elias Cort Aguelo

按ID和日期列出的mysql sum行值[重复]

  •  -5
  • Elias Cort Aguelo  · 技术社区  · 7 年前

    这个问题已经有了答案:

    我需要根据varchar列值和年份范围对mysql表的列值求和。也许这会让人困惑,所以我举一个基本的例子:

    列:id(对此无效)、水果类型、销售日期、销售类型、价格

    基本查询:

    SELECT * FROM fruits
    

    给我:

      1  APPLES   2018-05-30  GOOD   50.50 
      2  APPLES   2018-05-30  BAD   -20.50 
      3  APPLES   2018-05-30  GOOD   40.00 
      4  APPLES   2018-05-30  BAD   -10.50 
      5  APPLES   2017-05-29  GOOD   39.50 
      6  APPLES   2017-05-29  BAD   -10.00 
      7  APPLES   2017-05-29  GOOD   30.00 
      8  APPLES   2017-05-29  BAD   -20.00 
      9  BANANAS  2018-05-29  GOOD   20.00 
     10  BANANAS  2018-05-29  BAD    20.00 
     11  BANANAS  2018-05-29  GOOD   10.00 
     12  BANANAS  2018-05-29  BAD    -5.00 
     13  BANANAS  2017-05-29  GOOD   50.00 
     14  BANANAS  2017-05-29  BAD    -3.00 
     15  BANANAS  2017-05-29  GOOD   10.00 
     16  BANANAS  2017-05-29  BAD    -3.00 
     17  ORANGES  2018-05-28  GOOD    5.00 
     18  ORANGES  2018-05-28  BAD    -1.00 
     19  ORANGES  2017-05-28  GOOD   10.00 
     20  ORANGES  2017-05-28  BAD    -1.00 
    

    我需要做的是:

      Fruit      Sales 2017    Bad Sales 2017    Total 2017    Sales 2018    Bad Sales 2018    Total 2018       
      APPLES     69.50         -30.00            39.50         90.50         -30.50            60.00            
      BANANAS    60.00         -6.00             54.00         30.00         -10.00            20.00            
      ORANGES    10.00         -1.00             9.00          5.00          -1.00             5.00   
    

    我一直在尝试的问题是:

    SELECT fruit_type AS Fruit,\
    (SELECT SUM(price) FROM fruits WHERE sale_type='GOOD' AND date_of_sale BETWEEN '2017-01-01' AND '2017-12-31') AS Sales_2017,\
    (SELECT SUM(price) FROM fruits WHERE sale_type='BAD' AND date_of_sale BETWEEN '2017-01-01' AND '2017-12-31') AS Bad_Sales_2017,\
    (SELECT SUM(Sales_2017-Bad_Sales_2017)),\
    (SELECT SUM(price) FROM fruits WHERE sale_type='GOOD' AND date_of_sale BETWEEN '2018-01-01' AND '2018-12-31') AS Sales_2018,\
    (SELECT SUM(price) FROM fruits WHERE sale_type='BAD' AND date_of_sale BETWEEN '2018-01-01' AND '2018-12-31') AS Bad_Sales_2018,\
    (SELECT SUM(Sales_2018-Bad_Sales_2018)),\
    FROM fruits
    GROUP BY fruit_type;
    

    问题是查询返回的值是值为good或bad的整列的总和,而不是值good by fruit type match。

    需要一些技巧来解决这个问题。

    3 回复  |  直到 7 年前
        1
  •  4
  •   Wei Lin    7 年前

    使用 sum + case when condition 总结你的销售

    试试看:

    select 
      fruit_type AS Fruit,
      sum(
        case when date_of_sale between '2017-01-01 00:00:00' and '2017-12-31 00:00:00' then
          case when sale_type = 'GOOD' then price else 0 end
        else 0 end
      ) "Sales 2017",
      sum(
        case when date_of_sale between '2017-01-01 00:00:00' and '2017-12-31 00:00:00' then
          case when sale_type = 'BAD' then price else 0 end
        else 0 end
      ) "Bad Sales 2017", 
      sum(
        case when date_of_sale between '2017-01-01 00:00:00' and '2017-12-31 00:00:00' then
          price
        else 0 end
      ) "Total 2017",  
      sum(
        case when date_of_sale between '2018-01-01 00:00:00' and '2018-12-31 00:00:00' then
          case when sale_type = 'GOOD' then price else 0 end
        else 0 end
      ) "Sales 2018",
      sum(
        case when date_of_sale between '2018-01-01 00:00:00' and '2018-12-31 00:00:00' then
          case when sale_type = 'BAD' then price else 0 end
        else 0 end
      ) "Bad Sales 2018", 
      sum(
        case when date_of_sale between '2018-01-01 00:00:00' and '2018-12-31 00:00:00' then
          price
        else 0 end
      ) "Total 2018"
    FROM fruits
    GROUP BY fruit_type;
    

    SQL Fiddle Demo Link

        2
  •  1
  •   cdaiga    7 年前
    SELECT T.FRUIT, 
      SUM(IF(T.YEAR='2017' AND T.sale_type='GOOD',T.price_per_year,0)) `Sales 2017`,
      SUM(IF(T.YEAR='2017' AND T.sale_type='BAD',T.price_per_year,0)) `Bad Sales 2017`,
      SUM(IF(T.YEAR='2017',T.price_per_year,0)) `Total 2017`,
      SUM(IF(T.YEAR='2018' AND T.sale_type='GOOD',T.price_per_year,0)) `Sales 2018`,
      SUM(IF(T.YEAR='2018' AND T.sale_type='BAD',T.price_per_year,0)) `Bad Sales 2018`,
      SUM(IF(T.YEAR='2018',T.price_per_year,0)) `Total 2018`
    FROM (SELECT
      FRUIT_TYPE FRUIT,
      YEAR(date_of_sale) YEAR,
      sale_type,
      SUM(price) price_per_year
    FROM fruits 
    GROUP BY 
    FRUIT_TYPE,
    YEAR(date_of_sale),
    sale_type) T
    GROUP BY FRUIT
    ORDER BY T.FRUIT;
    

    看工作 DEMO on SQL Fiddle .

        3
  •  -1
  •   forsek    7 年前

    试试这个,只需为每一行添加更多的子选择。

    SELECT all2017.fruit_type as Fruit
    ,Sales_2017
    ,Bad_Sales_2017
    from (SELECT fruit_type
    ,SUM(ABS(price)) Sales_2017
    FROM fruits
    WHERE year(date) = 2017
    GROUP BY fruit_type) as all2017
    join (SELECT fruit_type
    ,SUM(ABS(price)) Bad_Sales_2017
    FROM fruits
    WHERE year(date) = 2017
    AND sales_type = 'BAD'
    GROUP BY fruit_type) as bad2017
    on all2017.fruit_type = bad2017.fruit_type