GREEN'S KM

年度各月銷售紀錄與累計紀錄

三.年度各月銷售紀錄與累計紀錄
銷售額累計
一月 1200 1200
二月 1300 2500
三月 1000 3500
四月 3500 7000
...
..
.

首先建立一個範例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

)

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

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

SELECT
  *
FROM
  MYTABLE
ORDER BY
  CASE
    WHEN ID IS NULL THEN 1
    ELSE 0
  END,
  ID
其實就是先對ID這個欄位做排序(NULL為1,NOT NULL為0),然後再用ID排序

取得第m到第n的資料

一.取得第m到第n的資料
前題是你一定要有一個欄位(ID)為排序用(ORDER BY)<<廢話>>

方法一
將資料讀到暫存#TMPTABLE

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

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

Cursor

之前在碰Oracle時，也有撰寫過Store Procedure，但是Oracle有一個比較特別的地方是，他有Package的功能，什麼是Package呢，Package就是可以包含很多Store Procedure，如我們撰寫

Create or replace package tPakcage
        …..
Procedure tProc1(

)
Procedure tProc2(
)

End tPakcage;

然後在每個Procedure可以輕鬆的呼叫對方，不過我這邊要講的不是Package的好用，而是在講我在撰寫Oracle時，有用到Cursor(迴圈)，因此我想SQL SERVER應該本身也有提供。

在找了一些文件後，開始來試著撰寫Store Procedure的Cursor

CREATE PROCEDURE dbo.ttt @tname nvarchar(100)
AS
        DECLARE MyCur CURSOR FOR
        SELECT * FROM f_split(@tname, ',') AS t
        OPEN MyCur
        DECLARE @uname nvarchar(100)

        FETCH Next FROM myCur INTO @uname
                WHILE (@@Fetch_Status = 0)
                        BEGIN
                        INSERT INTO testTable (name) values (@uname )
                        FETCH NEXT FROM myCur INTO @uname
                        END
        CLOSE MyCur
        DEALLOCATE MyCur
GO

當然在撰寫時，我們必須要Create Procedure，緊接著我們要宣告變數為Cursor，那他要跑哪一段sql字句的資料呢？，就利用For這個關鍵字。

接著我們要將cursor打開，之後我們要撰寫for-loop了，在SQL SERVER我們是利用FETCH NEXT的方式來跑遞迴，因為我們的sql字句

SELECT * FROM f_split(@tname, ',') AS t

只會select出一個欄位，所以在這邊我只宣告一個@uname的變數去接他，如果這邊我select2、3個欄位以上，那如果你想接值的話，就必須相對應的宣告變數去接值，接著我判斷@@Fetch_Status的狀態是不是正常的

傳回值	描述
0	FETCH 陳述式成功。
-1	FETCH 陳述式失敗，或資料列已超出結果集。
-2	遺漏提取的資料列。

如果是正常的話，接下來我們就可以執行我們欲做的事了，接著我們再

FETCH NEXT FROM myCur INTO @uname

讓他跑下一筆，一直遞迴下去。

最後我們執行完後，我們必須關閉我們宣告的Cursor，然後讓記憶體重新分配。

上述提供的程式與操作，僅供大家參考。

將同筆資料不同欄位擺在同一列

這是我在用oracle時碰到的問題。一般我們查詢資料出來的結果是

這表示1筆資料那如果今天我想要將這1筆資料以列來顯示而不是以欄來顯示結果應該是如下

而其相關的sql語法如下

我利用了wx的table，裡面有idno、c、codename等3個欄位

select unique c,idno,max(aa),max(bb),max(cc),max(dd),max(ee) from( select c,decode(rownum,1,a1) aa,decode(rownum,2,a1) bb,decode(rownum,3,a1) cc,decode(rownum,4,a1) dd,decode(rownum,5,a1) ee from (select c, codename a1 from wx t,(select count(1) c from wx where idno=1) where t.idno=1)),wx where wx.idno=1 group by c,idno

上述提供的程式與操作，僅供大家參考

一次刪除所有的Table

最近碰到資料更新的問題，就是要刪除全部Table的舊資料，然後再倒進新的資料。

一般正常的作法，就是一個Table慢慢Truncate或是Drop掉，我找到一個方法，可以一下子就將所有Table刪除了。

方法如下：

Declare @table_name varchar(8000)
Declare @drop_table varchar(8000)
Set @table_name =’’

select name into #temp_1 from sysobjects where xtype =’U’

update #temp_1 set @table_name=@table_name +',' + 'SchemaName.'+ '[' + name +']' from #temp_1

set @table_name = substring(@table_name,3,len(@table_name))

select substring(@table_name,3,len(@table_name))

set @drop_table =
'drop table '+substring(@table_name,3,len(@table_name))

EXEC (@drop_table)

以我例子而言，如果我的SchemaName是dbo，我只要將上面的SchemaName換成我的dbo，就可以一下子刪除此資料庫的資料表，Truncate Table的作法一樣。

上述提供的程式與操作，僅供大家參考

分離字串

最近在處理SQL SERVER分離字串的方式。

這對SQL SERVER來講，算是滿困難達到的，如我傳進來算是一個『變相的陣列』，什麼是變相的陣列呢，因為我們在撰寫store procedure時，必須要限制傳幾個參數進來，可是如果今天我們有一個參數是要傳多個值進來(不確定有幾個)，這時我們就必須在傳進來時用單引號把這個參數包住，讓SQL SERVER視其為一個數，可是我們要怎麼解開這個數呢？

我今天撰寫了一個Store Procedure，他允許傳入4個參數，但我其中1個參數是要傳入多值的，這可怎麼辦呢？因為我找不到在SQL 2000可以處理陣列的，如果有人找到的話，可以教我一下。

因此我如果要傳入多值的話，我就把他視為一個字串，

如我執行Store Procedure時，是

exec dbo.tPro ‘” & para1 & “’,’” & para2 & “’,’” & para3 & “’,’” & para4 & “’

，當然para1到para4是我的變數名稱，那如果今天我para3想要傳入多值的話怎麼辦，因此para3我就必須用一個符號每個值隔開，如利”；”或”||”等等，那這時候我們來假設值好了，para1=1，para2= 2，para3=3;4;5，para4=6，那我這一個Store Procedure該怎麼執行呢?

Exec dbo.tProc ‘1’,’2’,’3;4;5’,’6’

因此這時候我在sql server 該怎麼處理這樣子的數值，這時就必須用一個function，把這個para3解開，

create function f_split(@c varchar(2000),@split varchar(2))
returns   @t   table(col   varchar(20))
as
    begin

      while(charindex(@split,@c)<>0)
        begin
          insert   @t(col)   values   (substring(@c,1,charindex(@split,@c)-1))
          set   @c   =   stuff(@c,1,charindex(@split,@c),'')
        end
      insert   @t(col)   values   (@c)
      return
    end

這個function，第一個參數是傳入字串，第二個參數是用什麼當作split，

SELECT * FROM f_split(para3, ';') AS t

這時他就把會幫我把這個變成1個table

2011年3月27日 星期日

2011年3月27日星期日