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

在mySQL中压缩SQL

complexity-theory refactoring mysql sql

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 年前

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');

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

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