[鷹問]SQL問題

sql 蒼鴻

老鷹(eagle) 2013-05-03 16:35:16 ‧ 6304 瀏覽

小弟遇到一個問題,
我有個頁面需要計算訂單筆數,依照帳號去看日,周,月,年.

畫面大約顯示
帳號日週月年
從資料庫撈出帳號今日筆數帳號這週筆數帳號今月筆數帳號今年筆數

不過帳號有51個
51*4 = 204
所以一個畫面要撈204次

有什麼方法可以比較簡單.

我的方法土法煉鋼
寫4個 function
去跑日週月年
是否有比較好的方法

看更多先前的討論...收起先前的討論...

richardsuma iT邦大師 1 級 ‧ 2013-05-03 16:53:14 檢舉

不曉得你們公司有沒有每日過帳機制？
如果有將每日資料匯入每日業務訂單彙整檔
YY MM YYMM Week Sales_ID Orders

不然就要使用 Pivot
PIVOT語法剖析：

PIVOT的語法分三層，用三個步驟來使用。
第一步驟：先把要PIVOT的原始資料查詢(Query)好。
第二步驟：設定好PIVOT的欄位與方式。
第三步驟：依PIVOT好了的資料，呈現結果。

SELECT <non-pivoted column>, ---- 第三步驟在此，呈現PIVOT後的資料。
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>) ---- 第一步驟在此，準備資料(Query)。
AS <alias for the source query>
PIVOT ---- 第二步驟在此,依第一步驟的資料欄位來設定PIVOT方式。
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

請參考下列網頁：
http://rely1020.blog.ithome.com.tw/post/1606/39111

老鷹(eagle) iT邦高手 1 級 ‧ 2013-05-03 16:58:09 檢舉

這個我研究一下,希望不要用到我的function不然感覺撈的資料次數非常多~~!

總裁 iT邦好手 1 級 ‧ 2013-05-03 17:15:47 檢舉

chingfeng提到：
撈的資料次數非常多

這樣才有時間休息....

不能只撈日的資料，其他的用FUNCTION去算嗎??...

richardsuma iT邦大師 1 級 ‧ 2013-05-03 17:29:22 檢舉

不知道你的資料庫架構

不然先將每日資料匯入 temp talbe
然後在 select 所有 data
再用變數array 加總。

funforever iT邦新手 4 級 ‧ 2013-05-03 21:36:48 檢舉

chingfeng提到：
望不要用到我的function不然感覺撈的資料次數非常

chingfeng您好

小弟這有一個想法，是否能提供您一些思考方向。
第一個沿用您的function
但是在每天固定的一個時間點，做一次全查詢。
查詢完之後，建立一個屬於"要經常調閱的畫面-非即時每日或每小時更新"的view或table
這樣降低系統的負擔。
是否有達到您的需求?

funforever iT邦新手 4 級 ‧ 2013-05-03 21:37:46 檢舉

將view或table接到你的查詢畫面..

海綿寶寶 iT邦大神 1 級 ‧ 2013-05-03 22:33:05 檢舉

chingfeng提到：
51*4 = 204
所以一個畫面要撈204次

如果這樣可不可行
只要SELECT 4 次

每日：SELECT ACNT, COUNT(ACNT) FROM TABLE WHERE PODATE IS TODAY GROUP BY ACNT
每週：SELECT ACNT, COUNT(ACNT) FROM TABLE WHERE PODATE IN THIS WEEK GROUP BY ACNT
每月：SELECT ACNT, COUNT(ACNT) FROM TABLE WHERE PODATE IN THIS MONTH GROUP BY ACNT
每年：SELECT ACNT, COUNT(ACNT) FROM TABLE WHERE PODATE IN THIS YEAR GROUP BY ACNT

老鷹(eagle) iT邦高手 1 級 ‧ 2013-05-04 02:58:04 檢舉

我有一個欄位是紀錄時間的格式 date("Y-m-d"), 這個思路不錯一次撈完,就固定在一個暫存的資料表,會再試試看,謝謝指導~~!

炎之虛空 iT邦高手 3 級 ‧ 2013-05-04 22:07:49 檢舉

就變成大鵰兄的四個function的做法了
不過這個在於沒做INDEX的話是很好辦
其實直接使用黑膜大的做一個function
輸出採取陣列方式輸出最為恰當
這樣的話可以減少多餘程式編寫

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

wiseguy

iT邦超人 1 級 ‧ 2013-05-04 07:47:00

最佳解答

MySQL 的單一 query 解法：

&lt;pre class="c" name="code">select 帳號,
sum(if (日期=CURDATE(),1,0)) as 日,
sum(if (WEEK(日期)=WEEK(CURDATE()),1,0)) as 週,
sum(if (MONTH(日期)=MONTH(CURDATE()),1,0)) as 月,
count(帳號) as 年
from TABLE
group by 帳號

記得把帳號跟日期設一個索引：
ALTER TABLE 資料表名稱 ADD INDEX (帳號, 日期);

回應 7
分享
檢舉

看更多先前的回應...收起先前的回應...

Albert iT邦高手 1 級 ‧ 2013-05-04 12:32:05 檢舉

贊

Albert iT邦高手 1 級 ‧ 2013-05-04 12:35:09 檢舉

只是 [沒有交易資料] 的就進不來了 ?

wiseguy iT邦超人 1 級 ‧ 2013-05-04 18:12:51 檢舉

albertachen提到：
只是 [沒有交易資料] 的就進不來了 ?

謝謝阿伯大指教。
資料不全的問題，當然就只有資料不全的答案。
如果樓主不懂 join 的話，他會再發問的不是嗎？

Albert iT邦高手 1 級 ‧ 2013-05-05 10:32:39 檢舉

讚

Albert iT邦高手 1 級 ‧ 2013-05-05 16:22:06 檢舉

wise guy 這一招真利害

SELECT b.Value,b.Name,
SUM( CASE WHEN DATE_TRUNC('DAY', o.DateOrdered) =DATE_TRUNC('DAY', NOW()) THEN 1 ELSE 0 END) AS 本日,
SUM( CASE WHEN DATE_TRUNC('WEEK', o.DateOrdered) =DATE_TRUNC('WEEK',NOW()) THEN 1 ELSE 0 END) AS 本周,
SUM( CASE WHEN DATE_TRUNC('MONTH',o.DateOrdered) =DATE_TRUNC('MONTH',NOW()) THEN 1 ELSE 0 END) AS 本月,
SUM( CASE WHEN DATE_TRUNC('YEAR', o.DateOrdered) =DATE_TRUNC('YEAR',NOW()) THEN 1 ELSE 0 END) AS 本年
FROM C_BPartner b
LEFT OUTER JOIN C_Order o ON b.C_BPartner_ID=o.C_BPartner_ID
GROUP BY b.Value,b.Name

richardsuma iT邦大師 1 級 ‧ 2013-05-05 21:37:49 檢舉

老鷹(eagle) iT邦高手 1 級 ‧ 2013-05-07 14:16:31 檢舉

感謝 wiseguy大大指導,
這個可以使用,繼續研究一下SQL(比較弱),
用CodeIgniter開發習慣了,SQL太少使用了
回不去了

登入發表回應

Albert

iT邦高手 1 級 ‧ 2013-05-03 19:06:34

SELECT Value,Name,
(SELECT COUNT(*) FROM C_Order o WHERE o.C_BPartner_ID=b.C_BPartner_ID AND DATE_TRUNC('DAY',o.DateOrdered)=DATE_TRUNC('DAY',NOW())) AS 本日,
(SELECT COUNT(*) FROM C_Order o WHERE o.C_BPartner_ID=b.C_BPartner_ID AND DATE_TRUNC('WEEK',o.DateOrdered)=DATE_TRUNC('WEEK',NOW())) AS 本周,
(SELECT COUNT(*) FROM C_Order o WHERE o.C_BPartner_ID=b.C_BPartner_ID AND DATE_TRUNC('MONTH',o.DateOrdered)=DATE_TRUNC('MONTH',NOW())) AS 本月,
(SELECT COUNT(*) FROM C_Order o WHERE o.C_BPartner_ID=b.C_BPartner_ID AND DATE_TRUNC('YEAR',o.DateOrdered)=DATE_TRUNC('YEAR',NOW())) AS 本年
FROM C_BPartner b