代码之家  ›  专栏  ›  技术社区  ›  ere

在mySQL中压缩SQL

  •  1
  • ere  · 技术社区  · 16 年前

    如何在MySQL中简化这些代码?

    SELECT name,
      MAX(IF(to_days(thedate) - to_days('2009-06-13') = 0, price, '')) AS date1,
      MAX(IF(to_days(thedate) - to_days('2009-06-13') = 1, price, '')) AS date2,
      MAX(IF(to_days(thedate) - to_days('2009-06-13') = 2, price, '')) AS date3,
      MAX(IF(to_days(thedate) - to_days('2009-06-13') = 3, price, '')) AS date4,
      MAX(IF(to_days(thedate) - to_days('2009-06-13') = 4, price, '')) AS date5,
      MAX(IF(to_days(thedate) - to_days('2009-06-13') = 5, price, '')) AS date6,
      MAX(IF(to_days(thedate) - to_days('2009-06-13') = 6, price, '')) AS date7,
    AVG(price),SUM(price)
    FROM `personals`
    WHERE personal_id = '1234'
    GROUP BY name
    

    所以计算的日期数量是动态的?

    3 回复  |  直到 16 年前
        1
  •  1
  •   Martijn    16 年前

    最有效的方法是将其分成两个查询,一个用于加载平均价格和每人的价格总和:

    SELECT
      AVG(price), SUM(price)
    FROM `personals`
    WHERE personal_id = '1234'
    GROUP BY name;
    

    第二个是你想知道的最大值:

    SELECT
      MAX(price)
    FROM `personals`
    WHERE personal_id = '1234'
    GROUP BY name, to_days(thedate) - to_days('2009-06-13');
    

    如果您真的希望所有列都在同一个查询中,请对第一个查询使用子查询(在大型数据库上可能效率不太高)

    SELECT
      MAX(price),
      AVG(price),
      SUM(price)
    FROM `personals`
    LEFT JOIN (
      SELECT
        AVG(price), SUM(price), name
      FROM `personals`
      WHERE personal_id = '1234' -- # this line is optional
      GROUP BY name
    ) totals
    ON totals.name = personals.name
    WHERE personal_id = '1234'
    GROUP BY name, to_days(thedate) - to_days('2009-06-13');
    
        2
  •  0
  •   grahamparks    16 年前

    不能动态更改列数,但可以很容易地为每个日期获取单独的行:

    SELECT to_days(thedate) - to_days('2009-06-13') as interval,
       max(price) FROM `personals`
    WHERE personal_id = '1234'
    GROUP BY name, thedate
    

    您需要执行单独的查询以获取平均数据:

    SELECT name,
       AVG(price),SUM(price)
    FROM `personals`
    WHERE personal_id = '1234'
    GROUP BY name
    
        3
  •  0
  •   Mike Woodhouse    16 年前

    SELECT
      name
    , to_days(thedate) - to_days('2009-06-13') AS num_days
    , MAX(price) As max_price
    , NULL AS avg_price
    , NULL AS sum_price
    FROM `personals`
    WHERE personal_id = '1234'
    GROUP BY
      name
    , to_days(thedate) - to_days('2009-06-13') 
    UNION ALL
    SELECT
      name
    , NULL
    , NULL
    , AVG(price)
    , SUM(price)
    FROM `personals`
    WHERE personal_id = '1234'
    GROUP BY name