close
請問如何計算MY SQL平均？
不好意思各位，我想請問該如何計算每個月的平均呢？我先取日期作為分類、再計算每個月的平均SELECTAVG(A)asAAFROMdataWHEREid='001'ANDdatebetweenSUBDATE(now(),INTERVAL4MONTH)andSUBDATE(now(),INTERVAL3MONTH)GROUPBYidunionallSELECTAVG(A)asAAFROMdataWHEREid='001'ANDdatebetweenSUBDATE(now(),INTERVAL3...顯示更多不好意思各位，我想請問該如何計算每個月的平均呢？我先取日期作為分類、再計算每個月的平均SELECTAVG(A)asAAFROMdataWHEREid='001'ANDdatebetweenSUBDATE(now(),INTERVAL4MONTH)andSUBDATE(now(),INTERVAL3MONTH)GROUPBYidunionallSELECTAVG(A)asAAFROMdataWHEREid='001'ANDdatebetweenSUBDATE(now(),INTERVAL3MONTH)andSUBDATE(now(),INTERVAL2MONTH)GROUPBYidunionallSELECTAVG(A)asAAFROMdataWHEREid='001'ANDdatebetweenSUBDATE(now(),INTERVAL2MONTH)andSUBDATE(now(),INTERVAL1MONTH)GROUPBYid我想請問一下各位，我可以計算出這三個月的平均，但是我不知道如何將這三個月的平均數再次相加平均。麻煩各位指教喔。謝謝各位喔^^"更新:您好喔，很謝謝您的回答喔!!!剛剛試過您的方式有顯示錯誤訊息"1222theusedselectstatementhaveadifferentnumberofcolumns"不好意思，還請您多多指教~!!＞＂＜
SELECT AVG(sub.AA) FROM ( SELECT AVG(A) as AA FROM data WHERE id ='001' AND date between SUBDATE(now(),INTERVAL 4 MONTH) and SUBDATE(now(),INTERVAL 3 MONTH) GROUP BY id union all SELECT AVG(A) as AA FROM data WHERE id ='001' AND date between SUBDATE(now(),INTERVAL 3 MONTH) and SUBDATE(now(),INTERVAL 2 MONTH) GROUP BY id union all SELECT AVG(A) as AA FROM data WHERE id ='001' AND date between SUBDATE(now(),INTERVAL 2 MONTH) and SUBDATE(now(),INTERVAL 1 MONTH) GROUP BY id ) sub 2010-11-13 08:34:01 補充： 這個錯誤訊息是跟你中間的 union all 有關係... 跟我外面加上 SELECT AVG(sub.AA) FROM ( ) sub 沒有任何關係... 你確定中間的三個 select 加上 union all 的指令有完全一樣嗎? 有沒有漏掉什麼? 要除錯我建議你可以中間先試試看只用一個 select, 可以跑之後再加一個 union all, 然後可以跑的話再加最後一個 union all...
以上文章來自奇摩知識家，如有侵犯請留言告知
https://tw.answers.yahoo.com/question/index?qid=20101111000015KK06490