這是我的MYSQL語法 : SELECT year(`create_time`) AS year, month(`create_time`) AS month, day(`create_time`) AS day, count(*) AS total FROM `user` WHERE month(`create_time`)='$month' AND year(`create_time`)='$year' AND `create_time` BETWEEN '09/12/28' AND '10/12/31' GROUP BY year(`create_time`),month(`create_time`),day(`create_time`) ORDER BY `create_time` ASC
SELECT year(`visitday`) AS year, month(`visitday`) AS month, day(`visitday`) AS day, count(*) AS total FROM `record` BETWEEN '2010/12/1' AND '2010/12/31' GROUP BY year(`visitday`),month(`visitday`),day(`visitday`) ORDER BY `visitday` ASC
SELECT year(`create_time`) AS year, month(`create_time`) AS month, day(`create_time`) AS day, count(*) AS total FROM `user` BETWEEN '2010/12/1' AND '2010/12/31' GROUP BY year(`create_time`),month(`create_time`),day(`create_time`) ORDER BY `create_time` ASC
老師您好~我有照老師的方式貼過去...但是出現SQL ERROR
ERROR顯示如下 : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN '2010/12/1' AND '2010/12/31' GROUP BY year(`create_time`),month(`create_' at line 2
SELECT year(`visitday`) AS year, month(`visitday`) AS month, day(`visitday`) AS day, count(*) AS total FROM `record` where visitday BETWEEN '2010/12/1' AND '2010/12/31' GROUP BY year(`visitday`),month(`visitday`),day(`visitday`) ORDER BY `visitday` ASC
SELECT year(`create_time`) AS year, month(`create_time`) AS month, day(`create_time`) AS day, count(*) AS total FROM `user` where create_time BETWEEN '10/12/01' AND '10/12/31' GROUP BY year(`create_time`),month(`create_time`),day(`create_time`) ORDER BY `create_time` ASC
12 則留言:
老師您好~想要請教您一個問題就是我想要取得一年份(2010/01/01~2010/12/31)的總會員人數，總人數取得我是知道怎麼取，但是重點在於想要echo出"該日有多少會員加入"
ex.
2010/09/08 - 有20個會員加入
2010/09/09 - 有10個會員加入
2010/09/10 - 有50個會員加入
....
....
2010/12/31 - 有30個會員加入
以此類推
感謝老師的費心~~^^
謝謝您的提醒，簡報檔案我近日會再傳遞新的檔案。
您可於SQL語法內以date()函數擷取每一天作為分組，就可得到
每一天的人數統計，請參考21-2節說明。
感謝老師的指教~那老師~我還有一個問題就是當我取到2010/12/01~2010/12/31，但是在MYSQL語法卻只顯示到30號，而31號卻沒有顯示出來。想問說要怎麼樣才能取得31號呢~?
這是我的MYSQL語法 :
SELECT year(`create_time`) AS year, month(`create_time`) AS month, day(`create_time`) AS day, count(*) AS total
FROM `user`
WHERE month(`create_time`)='$month' AND year(`create_time`)='$year' AND `create_time` BETWEEN '09/12/28' AND '10/12/31' GROUP BY year(`create_time`),month(`create_time`),day(`create_time`) ORDER BY `create_time` ASC
10到15章簡報已經修改..皓哥提的問題我這幾天再做回應
因為您的SQL語法內還有PHP變數，我不知道您的變數要攜帶的資料是什麼..
所以我把變數刪除後，運用我的資料表查詢，12月31日是可以查詢的到：
SELECT year(`visitday`) AS year, month(`visitday`) AS month, day(`visitday`) AS day, count(*) AS total
FROM `record`
BETWEEN '2010/12/1' AND '2010/12/31' GROUP BY year(`visitday`),month(`visitday`),day(`visitday`) ORDER BY `visitday` ASC
請您把欄位名稱及資料表名稱改掉，試試看是否OK
SELECT year(`create_time`) AS year, month(`create_time`) AS month, day(`create_time`) AS day, count(*) AS total
FROM `user` BETWEEN '2010/12/1' AND '2010/12/31' GROUP BY year(`create_time`),month(`create_time`),day(`create_time`) ORDER BY `create_time` ASC
老師您好~我有照老師的方式貼過去...但是出現SQL ERROR
ERROR顯示如下 :
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN '2010/12/1' AND '2010/12/31' GROUP BY year(`create_time`),month(`create_' at line 2
SELECT year(`visitday`) AS year, month(`visitday`) AS month, day(`visitday`) AS day, count(*) AS total
FROM `record` where visitday
BETWEEN '2010/12/1' AND '2010/12/31' GROUP BY year(`visitday`),month(`visitday`),day(`visitday`) ORDER BY `visitday` ASC
不好意思.原本張貼語法於
FROM `record`之後遺漏了 where visitday
SELECT year(`create_time`) AS year, month(`create_time`) AS month, day(`create_time`) AS day, count(*) AS total
FROM `user` where create_time
BETWEEN '10/12/01' AND '10/12/31' GROUP BY year(`create_time`),month(`create_time`),day(`create_time`) ORDER BY `create_time` ASC
嗯嗯~語法有成功執行~但還是只顯示到30號而已...那我的`create_time`欄位的內容是這樣子的
09/12/28 11:02 但我只取日期而已，時間沒取~不好意思~打擾到老師時間~ ><''
你三十一日確定有資料嗎？
若確定有資料仍無法讀取..
若方便資料SQL可否寄給我？
信箱為jiannrong@gmail.com
張貼留言