Блог

Главная - Вопросы по программированию - mysql обновить и выбрать запрос объединения

mysql обновить и выбрать запрос объединения

#mysql #sql-update #union

#mysql #sql-обновление #объединение

Вопрос:

У меня есть две таблицы, подобные этим:

 s1

date    quantity
2014-01-02  200
2014-01-05  400
2014-02-10  200
2014-02-13  300

s2

date        Temperature     Humidity
2014-01-01  12              60
2014-01-02  14              80
2014-01-03  12              60
2014-01-04  14              80
2014-01-05  12              60
2014-01-06  16              80
2014-01-07  20              60
2014-01-08  14              80
2014-02-01  13              60
2014-02-02  15              80
2014-02-03  16              60
2014-02-04  18              80
2014-02-05  12              60
2014-02-06  17              80
2014-02-07  28              60
2014-02-08  14              80

Мне нужно обновить таблицу, которая объединяет и суммирует приведенную выше таблицу за месяц следующим образом:

 s1 s2

date    quantity    Temperature Humidity
2014-01     600         14,25       70
2014-02     500         16,62       70
2014-03 
2014-04 
2014-05 
2014-06

У меня есть этот код, который отлично работает для создания объединенных данных, но я не могу создать обновление этой таблицы

 select t2.year as year, t2.month as month, t1.quantity as quantity, t2.avgtemp as avgtemp, t2.avghum as avghum
from
    (select year(s1.`date`) as year, month(s1.`date`) as month, sum(s1.`quantity`) as quantity, 0 as avgtemp, 0 as  avghum from s1 
    group by year, month) t1
right join
    (select year(s2.`date`) as year, month(s2.`date`) as month,0 as quantity, avg(s2.`temperature`) as avgtemp, avg(s2.`humidity`) as  avghum from s2 
    group by year, month )t2
on t1.year=t2.year and t1.month=t2.month

Комментарии:

1. Зачем создавать таблицу: просто используйте свой SQL в качестве представления, которое вычисляет сводку по запросу. но, возможно, я не понимаю вопроса.

2. Мне нужна новая таблица, потому что мне нужно отобразить данные на графике на веб-странице.

3. Да, но если ваш запрос генерирует данные так, как вы хотите, вы можете обработать запрос / представление как таблицу и вызвать его. и поскольку он зависит от данных в базовых таблицах, когда они меняются, меняются и результаты просмотра.

4. Вы хотите обновить таблицу или вставить новые строки?

5. вставка новых строк должна быть в порядке для меня

Ответ №1:

Вы можете проверить правильный результат, используя 

 select t.year, t.month, t1.SumQuantity, t2.avgTemp, t2.avgHum
FROM (
  select  year(s1.date), month(s1.date) from s1
  union 
  select  year(s2.date), month(s2.date) from s2
) left join (
  select  year(s1.date), month(s1.date), SUM(s1.`quantity`) SumQuantity
  from s1
  group by  year(s1.date), month(s1.date)
) t1 on t1.year = t.year 
        and t1.month = t.month 
left JOIN (
  select  year(s2.date), month(s2.date),  avg(s2.`Temperature`) avgTemp , avg(s2.`Humidity`) avgHum
  from s1
  group by  year(s1.date), month(s1.date)
) t2 on t2.year = t.year 
        and t2.month = t.month

и если приведенный выше запрос возвращает правильный результат, используйте это для обновления

 update my_table m 
inner join  (
  select t.year, t.month, t1.SumQuantity, t2.avgTemp, t2.avgHum
  FROM (
    select  year(s1.date), month(s1.date) from s1
    union 
    select  year(s2.date), month(s2.date) from s2
  ) left join (
    select  year(s1.date), month(s1.date), SUM(s1.`quantity`) SumQuantity
    from s1
    group by  year(s1.date), month(s1.date)
  ) t1 on t1.year = t.year 
          and t1.month = t.month 
  left JOIN (
    select  year(s2.date), month(s2.date),  avg(s2.`Temperature`) avgTemp , avg(s2.`Humidity`) avgHum
    from s1
    group by  year(s1.date), month(s1.date)
  ) t2 on t2.year = t.year 
          and t2.month = t.month 
) tt  ON tt.year = year(m.date)       
        and tt.month = month(m.date)
set m.quantity  = tt.SumQuantity, 
    m.Temperature = tt.AvgTemp,  
    m.Humidity = tt.AvgHum

Комментарии:

1. какой тип данных соответствует дате столбца в вашей таблице агрегирования?? это дата или строка в формате гггг-мм??

2. это тип даты

3. обновлен ответ .. преобразование даты в таблице агрегирования в качестве выбранного результата

4. нет выбора запрос внутреннего соединения возвращает пустую таблицу. Я думаю, что это что-то связанное с «форматом даты» или чем-то подобным … как я могу избежать этой проблемы?

5. Я отредактировал свой первый пост. Я уточнил свой код и название таблицы. Ваш код не работает. Я получаю запрос emtpy. Два центральных оператора select с оператором union создают таблицу с 0 данными в первом столбце количества ….!