JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » JDBC/SQL討論區 » SQL 語法  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to topicthreaded modego to previous topicgo to next topic
己加入精華區
by browser at 2006-03-11 09:23
本主題所含的標籤
無標籤
作者 [分享]MSSQL一些語法 [精華]
ajax

Thiago Alves



發文: 675
積分: 3
於 2006-03-09 00:43 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
一.取得第m到第n的資料
前題是你一定要有一個欄位(ID)為排序用(ORDER BY)<<廢話>>

方法一
將資料讀到暫存#TMPTABLE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
  IDENTITY(INT,1,1) AS ROWNUMBER
  ,*
INTO
  #TMPTABLE
FROM
  TABLENAME
ORDER BY
ID;
暫存TABLE完成
 
SELECT
  *
FROM
  #TMPTABLE
WHERE
  ROWNUMBER  BETWEEN M AND N;
  

註記:
第一個方法在SQL 7.0上是有問題的,因為在7.0上有時候IDENTITY會先計算但是有時候,ORDER BY會先排序...但是在SQL 2000已經將順序確定為ORDER BY>>IDENTITY!!
方法二
善用DESC與ASC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
  *
FROM (
  SELECT
    TOP 10 *
  FROM
    (SELECT
       TOP 30 *
     FROM
       dbo.JavaRanch
     ORDER BY ID )AS B
  ORDER BY ID DESC
) AS A
  ORDER BY ID ASC


二.讓NULL排序時不要搶前面的位子

1
2
3
4
5
6
7
8
9
10
11
SELECT
  *
FROM
  MYTABLE
ORDER BY
  CASE
    WHEN ID IS NULL THEN 1
    ELSE 0
  END,
  ID
其實就是先對ID這個欄位做排序(NULL為1,NOT NULL為0),然後再用ID排序


三.年度各月銷售紀錄與累計紀錄
銷售額 累計
一月 1200 1200
二月 1300 2500
三月 1000 3500
四月 3500 7000
...
..
.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
首先建立一個範例Table(Java2)
CREATE TABLE Java2(
   nRecIdn     [int]                IDENTITY (1, 1) NOT NULL ,
   cMonth      [char] (2)                           NOT NULL ,
   nInvoiceAmt  [int]                                NOT NULL
)
塞值...
nRecIdn   nMonth   nInvoiceAmt
1       01           10
2       02           20
3       03           30
4       04           40
5       05           0
6       06           33
7       07           22
8       08           12
9       09           13
10      10           99
11      11          100
12      12          200
 
SELECT 
    cMonthAS MONTH
   ,nInvoiceAmt AS MONTHLY
   ,( SELECT
         SUM(Java2_B.nInvoiceAmt) AS CUMULATIVE
      FROM Java2 AS Java2 _B
      WHERE Java2_B.cMonth<= Java2_A.cMonth
)
FROM
   Java2 AS Java2_A
GROUP BY cMonth,nInvoiceAmt
ORDER BY cMonth

其實觀念很簡單,大家看了應該就會清楚...
不過還是要強調一點,當資料量多的時候還是需要去評量到底是要在SQL上處理,還是要將資料抓到前端之後再處理...
還有,這是一個範例table,所以日期欄位(cMonth)我使用了char,不過我還是強烈的覺得日期欄位還是用datetime的Data Type(西元年)會是最好的選擇!!!!
我沒有統獨情結喔!!!(最近民國年改制為西元年的話題...Big Smile)


ajax edited on 2006-03-14 10:59
reply to postreply to post
作者 Re:[分享]MSSQL一些語法 [Re:ajax]
RR

~Nintendo64~



發文: 686
積分: 3
於 2006-03-10 16:45 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
ajax wrote:
一.取得第m到第n的資料
前題是你一定要有一個欄位(ID)為排序用(ORDER BY)<<廢話>>

方法一
將資料讀到暫存#TMPTABLE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
  IDENTITY(INT,1,1) AS ROWNUMBER
  ,*
INTO
  #TMPTABLE
FROM
  TABLENAME
ORDER BY
ID;
暫存TABLE完成
 
SELECT
  *
FROM
  #TMPTABLE
WHERE
  ROWNUMBER  BETWEEN M AND N;
  

方法二
善用DESC與ASC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
  *
FROM (
  SELECT
    TOP 10 *
  FROM
    (SELECT
       TOP 30 *
     FROM
       dbo.JavaRanch
     ORDER BY ID )AS B
  ORDER BY ID DESC
) AS A
  ORDER BY ID ASC


以上兩種方法,感覺上建立temp table似乎比較有效率。
因為temp table只有在首次建立時才會對原table做full scan的樣子...

請問,SQL Server的temp table會維持多久哩?
在書上查到的:
local暫存資料表,以#開頭。
僅對使用者目前工作階段(Session)的期間有效,工作階段終止時則消失。

global暫存資料表,以##開頭。
對所有使用者工作階段都有作用,至最後一個使用者工作階段終止則消失。

對一個WEB AP而言,即使是不同客戶端(怕和db user搞混)登入WEB AP,
通常都是用一樣的帳號與密碼存取資料庫,
所以對DB而言也許該算是同一個user session?

不知道有沒有大大有相關經驗可以指點的,
因為目前WEB AP還在初期階段無法自己測試..............


RR edited on 2006-03-10 18:13
reply to postreply to post

Focus TDCi Powershift,純正德國血統
作者 Re:[分享]MSSQL一些語法 [Re:ajax]
ajax

Thiago Alves



發文: 675
積分: 3
於 2006-03-10 23:34 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
第一要考慮該檔案的資料筆數,如果對一個700多萬筆的table去建立一個tmp Table,只是為了抓取其中M到N的資料,這樣一定是不可行的...
所以針對資料量多的table建議使用第二種方法...

以下圖片是對70多萬筆的資料取21至30筆的預估執行計畫...

ajax.chien


reply to postreply to post
作者 Re:[分享]MSSQL一些語法 [Re:ajax]
ajax

Thiago Alves



發文: 675
積分: 3
於 2006-03-11 01:12 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
SQL Server的暫存Table,分為兩種
一種可以把它當作是local variable,也就是一個井字號[#]為開頭的暫存表格,他的生命週期是,暫存表格的建立到使用者手動drop或是 user connection的中斷...

注意一個處理序就是代表一個PID也就是一條Connection

另一種是Instance variable,它的特徵是兩個井字號[##]它的生命週期是該暫存表格的建立到使用者drop或是SQL Server的停止...它是可以讓任何在SQL 上執行的執行緒抓取的..所以說它是不矜持的,任何人都可以得它!!!!

好玩的地方來了...
請各位去想一想,如果不使用Connection Pool時,每一次的Query都是一次的建立Connection,當然Satement的覆用除外,這裡我是指一次的Query就要重新連一次資料庫,這樣不就無法使用local variable[#]類別的暫存表格了嗎??因為每次的中斷Connection.close(),就是drop你所建立類型是[#]屬性的所有表格???

但是另一種情況是使用Connection Pool,Connection Pool就是要避免造成資料庫連線所耗損的大量資源,因此AP與DB之間都會有一定數量的Connection存在,因此看起來這種類型[#]的暫存表格在Connection Pool應該是OK的...??但是這是真的嗎???你能保證你上一個Query跟現在要使用的Query所使用的Connection會是同一條嗎??想像一下AP與DB之間有十條Connection在Connection Pool裡等待接客,你能保證你這次遇到的小姐會是上一個那位阿花嗎??會運氣那麼好每次都可以遇到長的像林志玲的那位阿花嗎???如果可以介紹給我....Big Smile

當然上面所提到的這些問題其實都有變通的方法可以解決......

如果有人有興趣的話我們再來討論討論吧.....

ajax.chien


ajax edited on 2006-03-11 01:14
reply to postreply to post
作者 Re:[分享]MSSQL一些語法 [Re:ajax]
RR

~Nintendo64~



發文: 686
積分: 3
於 2006-03-14 14:58 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
ajax兄可否解釋一下取m~n筆資料的第二個方法?

目前擔心的是暫存資料表的時效性。
例如user A query了資料,所以建立了暫存資料表。
此時user B也登入系統,新增了一筆資料。
Boss C進去query,因為用的是A當時建立的temp table,
結果C沒看到B新增的資料。
B被誤會瀆職,走人......Cry

(怕會因為所有WEB AP的使用者都是以同個帳號登入DB....)


RR edited on 2006-03-14 15:12
reply to postreply to post

Focus TDCi Powershift,純正德國血統
作者 Re:[分享]MSSQL一些語法 [Re:ajax]
ajax

Thiago Alves



發文: 675
積分: 3
於 2006-03-14 16:36 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
題外話

我的觀點:
暫存表格的目的是要方便做資料加工.....
目前我使用TEMP TABLE的時機幾乎都是在Store Procedure,有時候想破腦袋的SQL Statement,其實用TEMP TABLE處理就簡單許多,因為TEMP TABLE跟一般的TABLE除了生命週期有差別外,其實TEMP TABLEE跟一般TABLE的功能幾乎是沒有任何差別的,你可以任意的蹂躪它..你想INSERT或UPDATE甚至DELETE他都不會反抗!!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT                     --外圈開始 
  *
FROM (
  SELECT                  --中圈開始 
    TOP 10 *
  FROM
    (SELECT                --內圈開始
       TOP 30 *
     FROM
       dbo.JavaRanch
     ORDER BY ID )AS B     --內圈結束
  ORDER BY ID DESC         --中圈結束
) AS A
  ORDER BY ID ASC         --外圈結束

內圈先以ID排序前30筆的資料ASC
1
2
3
..
...
..
28
29
30

中圈在以ID排序抓取前10筆資料DESC
30
29
..
...
..
22
21

外圈再以ID排序ASC

21
22
..
...
..
29
30

ajax.chien


reply to postreply to post
作者 Re:[分享]MSSQL一些語法 [Re:ajax]
RR

~Nintendo64~



發文: 686
積分: 3
於 2006-03-15 10:30 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
感謝ajax大教學,弟現在才知道原來是倒著看的...

不過目前實用上有個小問題,
例如table內共有33筆資料,前端分業設定為每次顯示10筆資料,
所以第四次query會是這樣:
1
2
3
4
5
6
7
SELECT * FROM
(
 SELECT TOP 10 * FROM
 (
  SELECT TOP 40 * FROM table ORDER BY ID
 )AS B ORDER BY ID DESC
) AS A ORDER BY ID ASC

結果會顯示出24~33的資料...........
變成前端user看到了7筆重複的資料。
萬一總資料是31筆那就......圀rz


reply to postreply to post

Focus TDCi Powershift,純正德國血統
作者 Re:[分享]MSSQL一些語法 [Re:ajax]
ajax

Thiago Alves



發文: 675
積分: 3
於 2006-03-16 10:18 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
沒想到這個問題...Disapproved
不過如果要解決的話可能要寫一個Store Procedure或是Function來解決,程式裡面要判斷欲查詢的最後筆數跟資料最大筆數做比較,這樣來解決這個問題....
然後由Store Procedure或是Function來丟資料給USER,目前我只想到這個方法...
不知道各位前輩是否有更好的Solution???

ajax.chien


reply to postreply to post
作者 Re:[分享]MSSQL一些語法 [Re:ajax]
RR

~Nintendo64~



發文: 686
積分: 3
於 2006-03-17 11:57 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
ajax wrote:
沒想到這個問題...Disapproved
不過如果要解決的話可能要寫一個Store Procedure或是Function來解決,程式裡面要判斷欲查詢的最後筆數跟資料最大筆數做比較,這樣來解決這個問題....
然後由Store Procedure或是Function來丟資料給USER,目前我只想到這個方法...
不知道各位前輩是否有更好的Solution???

ajax.chien

查到另一種方法,資料來源"http://www.move-to.net/comm/forum/ADO.NET/3143.aspx"
請ajax大大看看批評指教一下:
1
2
3
4
5
6
7
8
9
SELECT TOP x *
FROM         table
WHERE        id NOT IN
(
 SELECT TOP y-1 [id]
 FROM           table
 ORDER BY       [id]
)
ORDER BY [id]


查詢從第y筆資料開始的x筆資料
另附上執行計畫:


RR edited on 2006-03-17 11:59
reply to postreply to post

Focus TDCi Powershift,純正德國血統
作者 Re:[分享]MSSQL一些語法 [Re:ajax]
ajax

Thiago Alves



發文: 675
積分: 3
於 2006-03-19 01:37 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
看到你的NOT IN,讓我想到另一個方法...
更簡單..
1
2
3
4
5
6
7
8
9
10
11
12
  SELECT
    TOP 10 *
  FROM
    dbo.JavaRanch
  WHERE
    ID NOT IN
    (SELECT
       TOP 10 ID
     FROM
       dbo.JavaRanch
     ORDER BY ID )
  ORDER BY ID

這樣就是第11至20筆的資料...
而且你提到的問題也就避免掉了...
如果要抓第21到30筆的資料就把SUBQUERY裡的10改成20就好了...

ajax.chien


reply to postreply to post
» JWorld@TW »  JDBC/SQL討論區 » SQL 語法

reply to topicthreaded modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8