SQL依日期群組化製作報表

當我們在製作統計報表時通常使用日期、時間做為群組來計算總額、訂單、平均等等的數據統計
以前都笨笨的使用迴圈計算，效率真的很低落，例子如下

for($i = 1 ; $i < $limitDate ; $i++)
{
$sql = "SELECT COUNT(*) AS daily FROM orders WHERE createDate between '2008/05/".$limitDate." 00:00:00' AND '2008/05/".$limitDate." 23:59:59'"
}

現在可以改用SQL內建 CONVERT 函數來完成相同的工作，資料量愈大節省的時間愈可觀
例子假設情況為統計每日訂單數量

MySQL︰
SELECT CONVERT(createDate, DATE) AS daily , COUNT(*) AS dayOrders FROM order
GROUP BY daily

MsSQL︰
SELECT CONVERT(varchar, createDate, 101) AS daily, COUNT(*) AS dayOrders
FROM order
GROUP BY CONVERT(varchar, regdate, 101)
ORDER BY dayOrders DESC
完整的程式縮排可至
http://mamba.zapto.org/bbs/showthread.php?p=1593#post1593