作者 主題: SQL 語法查詢?  (閱讀 2386 次)

0 會員 與 1 訪客 正在閱讀本文。

NARs

  • 活潑的大學生
  • ***
  • 文章數: 227
    • 檢視個人資料
SQL 語法查詢?
« 於: 2012-06-11 12:27 »
我有一個shoplog table,
表格中的欄位有:
buyer (varchar),buyer_region(varchar),shop(varchar),shop_region(varchar),shoppingtime(datetime),
假設資料內容如下:
+---------------+---------+----------------+---------+---------------------+
| buyer | buyer_region| shop | shop_region| shoppingtime |
+---------------+---------+----------------+---------+---------------------+
| alan | taipei | a_shop | kao | 2012-06-11 00:12:13 |
| john | tainan | b_shop | tainan | 2012-06-12 00:13:15 |
| mary | taichung | c_shop | taipei | 2012-06-11 00:20:40 |
| vivi | kao | d_shop | kao | 2012-06-14 00:21:11 |
| tom | taipei | a_shop | taichung | 2012-06-11 00:22:15 |
| alan | taipei | a_shop | kao | 2012-06-11 00:12:13 |
| alan | taipei | a_shop | kao | 2012-06-12 05:31:23 |
| tom | taipei | a_shop | hsinchu | 2012-06-11 00:22:15 |
| alan | hsinchu | a_shop | kao | 2012-06-19 00:31:23 |

請問要怎麼取出本週內,在同一個buyer_region的buyer向同一個shop_region的shop的次數,也就是buyer,buyer_region| shop,shop_region這四個值要一樣,歸類為同一群,並且也可以顯示各群購買的時間區間??

以上面的例子的話,如果是取出6/11~6/17內在同一個buyer_region的buyer向同一個shop_region的shop的次數,
結果應該是

+---------------+---------+----------------+---------+----------------+---------------------+
| buyer | buyer_region| shop | shop_region| counts | firsttime | endtime
+---------------+---------+----------------+---------+----------------+---------------------+
| alan | taipei | a_shop | kao |3 |2012-06-11 00:12:13 |2012-06-12 05:31:23
| john | tainan | b_shop | tainan | 1 |2012-06-12 00:13:15 |2012-06-12 00:13:15
| mary | taichung | c_shop | taipei | 1 |2012-06-11 00:20:40 |2012-06-11 00:20:40
| vivi | kao | d_shop | kao | 1 |2012-06-14 00:21:11 |2012-06-14 00:21:11
| tom | taipei | a_shop | taichung | 1 |2012-06-11 00:22:15 |2012-06-11 00:22:15
| tom | taipei | a_shop | hsinchu | 1 |2012-06-11 00:22:15 |2012-06-11 00:22:15

請問這要怎麼做呢??