Блог

У меня есть данные в следующем формате:

date (мм / дд /гггг), desc (подробное описание), category (a, b, c), tran_type (дебет, кредит), amount spent (суммы).

Я хотел бы получить данные в следующем формате:

 category  ||  tran_type   ||   Jan_total  ||  feb_total  ||  mar_total  
A         ||  debit       ||   $101       ||  $201       ||  $302  
A         ||  credit      ||   $500       ||  $600       ||  $200  
  

Этот запрос должен дать вам требуемый результат.

 SELECT category
  ,tran_type
  ,SUM(IF(month(date) = 1,ABS(amount),0)) as jan_total
  ,SUM(IF(month(date) = 2,ABS(amount),0)) as feb_total
  ,SUM(IF(month(date) = 3,ABS(amount),0)) as mar_total
  ,SUM(IF(month(date) = 4,ABS(amount),0)) as apr_total
  ,SUM(IF(month(date) = 5,ABS(amount),0)) as may_total
  ,SUM(IF(month(date) = 6,ABS(amount),0)) as jun_total
  ,SUM(IF(month(date) = 7,ABS(amount),0)) as jul_total
  ,SUM(IF(month(date) = 8,ABS(amount),0)) as aug_total
  ,SUM(IF(month(date) = 9,ABS(amount),0)) as sep_total
  ,SUM(IF(month(date) = 10,ABS(amount),0)) as okt_total
  ,SUM(IF(month(date) = 11,ABS(amount),0)) as nov_total
  ,SUM(IF(month(date) = 12,ABS(amount),0)) as dec_total
 FROM transactions
 WHERE YEAR(date) = '2011'
 GROUP BY category, tran_type
  

Не забудьте отфильтровать по году, если не хотите нарваться на неприятности.

А как насчет лет? Вы хотите, чтобы в столбец Janury добавлялись транзакции за январь 2011 с январем 2010?

Я предполагаю, что вам потребуется добавить столбец Year отдельно или использовать для этого предложение WHERE, поэтому включил оба варианта для примера:

В принципе, вам нужно будет создать двенадцать подзапросов для достижения этого, и SQL будет выглядеть беспорядочно и не является обычным методом достижения этого:

 SELECT category, YEAR(date), tran_type,

    (SELECT SUM(amounts) FROM TableName s1 WHERE YEAR(t.date)=YEAR(s1.date) AND MONTH(s1.date)=1 AND t.category=s1.category AND t.tran_type=s1.tran_type) AS 'Jan_Total',

    (SELECT SUM(amounts) FROM TableName s2 WHERE YEAR(t.date)=YEAR(s2.date) AND MONTH(s2.date)=2 AND t.category=s2.category AND t.tran_type=s2.tran_type) AS 'Feb_Total',

     ....REPEAT ABOVE 2 LINES FOR EACH MONTH

FROM TableName t
WHERE t.date>'2011-01-01'
GROUP BY t.category, YEAR(t.date), t.tran_type;
  

как упоминалось выше, это не совсем элегантно, и лучшим решением было бы использовать следующий SQL и отформатировать данные на уровне представления для пользователя:

 SELECT category, YEAR(date), MONTH(date), tran_type, SUM(amounts)
FROM TableName
GROUP BY TableName;
  

Надеюсь, это поможет.

Для одного месяца (январь 2011) вы можете использовать:

 SELECT category, tran_type, SUM(amount_spent) AS total FROM myTable WHERE date>="2011-01-01" AND date<"2011-02-01" GROUP BY category, tran_type;
  

предполагая, что у вас есть индекс по крайней мере date , тогда это должно быть достаточно быстро. Лучшим индексом был бы (date, category, tran_type) , поскольку это также помогло бы с группировкой.

Если вы хотите объединить несколько месяцев в описанном вами формате, вы могли бы собрать несколько этих запросов в виде подзапросов, что-то вроде:

 SELECT jan.category, jan.tran_type, jan.total, feb.total FROM
    (SELECT category, tran_type, SUM(amount_spent) AS total FROM myTable WHERE date>="2011-01-01" AND date<"2011-02-01" GROUP BY category, tran_type) AS jan,
    (SELECT category, tran_type, SUM(amount_spent) AS total FROM myTable WHERE date>="2011-02-01" AND date<"2011-03-01" GROUP BY category, tran_type) AS feb
 WHERE jan.category = feb.category
 AND jan.tran_type = feb.tran_type;
  

Хотя я подозреваю, что самым простым способом извлечения данных, которые вы хотите (хотя и не очень эффективным и не совсем в том формате, который вы описали), было бы:

 SELECT category, tran_type, MONTH(date) AS theMonth, SUM(amount_spent) AS total FROM myTable WHERE date>="2011-01-01" AND date<"2012-01-01" GROUP BY category, tran_type, theMonth;
  

Такое использование MONTH() функции далеко от оптимального, но если вы не выполняете запрос, который часто может быть правильным.