迭代(iterate)資料表的每一個row

現在一個資料表用來記錄網頁瀏覽率:

欄位[Views]為累加的瀏覽率, ItemId & ItemType 為頁面物件

現在想要詳細記錄每一個使用者的瀏覽時間, 因為之前沒有記錄使用者, 為了讓舊的瀏覽記錄沿用, 所以必須製作假資料, 例如資料表中的第二筆資料的[Views]為4, 則 insert 4筆假資料

備註 :
    1. 為了可以加入到entity framework, 所以加了 Id 當主鍵
    2. UserId 用 -1 代表匿名使用者, 表示沒有登入狀態下瀏覽網頁


T-SQL :

declare @i int

declare @max int -- 要產生幾筆資料

declare @itemId nvarchar(50)

declare @itemType nvarchar(50)

declare @createdOn datetime

declare cur CURSOR LOCAL for

    select ItemId, ItemType,[Views],ModifiedOn from PageViews

open cur

fetch next from cur into @itemId, @itemType, @max,@createdOn

while @@FETCH_STATUS = 0 begin

    set @i = 0

        while (@i<@max) begin

            insert into UserPageView (UserId,ItemId,ItemType,CreatedOn)

                 values (-1,@itemId,@itemType,@createdOn)

                 set @i = @i+1 -- 累加

        end

    fetch next from cur into @itemId, @itemType, @max,@createdOn

end

close cur

deallocate cur

LINQ to Entities 使用 rank / row_number / dense_rank 技巧

T-SQL

    rank : 

        遇到相同數值會給相同的排名, 其後的排名則跳過, 例如: 1,2,2,4 (會重複號碼, 也會跳號)

    row_number : 

        遇到相同數值會依其他的依據來排名, 例如: 1,2,3,4 (不重複號碼, 也不跳號)

    dense_rank : 

        遇到相同數值會給相同的排名, 其後的繼續排名, 例如: 1,2,2,3 (會重複號碼, 但不跳號)

    

======================================================

LINQ to Entities

    rank : 

   

    row_number : 

          說明 :

TopicSortingRank.ToList() 後才能使用在 .Select 中使用索引, 否則會出現 NotSupportedException 的錯誤 :
LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[<>f__AnonymousType0`3[System.Int32,System.Decimal,System.Int32]] Select[TopicSortingRank,<>f__AnonymousType0`3](System.Linq.IQueryable`1[HappyMovie.Model.TopicSortingRank], System.Linq.Expressions.Expression`1[System.Func`3[HappyMovie.Model.TopicSortingRank,System.Int32,<>f__AnonymousType0`3[System.Int32,System.Decimal,System.Int32]]])' method, and this method cannot be translated into a store expression. 

 

    dense_rank :

               

     

使用 Group By 組合某一個欄位的數值成為一個字串

假設有一個資料表TopicItem :

想要看VideoId有哪些TopicId, 所以想組合成:

語法 :

select
    VideoId,
    STUFF
    (
        (
            select DISTINCT ',' + convert(varchar(10),TopicId) from TopicItem where VideoId = a.VideoId
             FOR XML PATH ('')
   ), 1, 1, ''
     ) as Topics
from TopicItem a
group by VideoId

分割字串

split string column into multiple records

電影資料表有三個字串欄位分別記錄男演員/女演員/導演, 欄位的字串是由不同資料組成, 現在想要把字串分割成單筆資料 :

DECLARE @videoId nvarchar(max) = 'M00000000007105' --電影編號

DECLARE @temp TABLE

(

  RoleType VARCHAR(Max),

  ColumnB VARCHAR(Max),

  ID INT IDENTITY(1,1)

)

-- 放置 Video 資料表的演員欄位字串

INSERT INTO @temp (RoleType,ColumnB) VALUES ('Actor',(select Actors from Video where ID=@videoId))

INSERT INTO @temp (RoleType,ColumnB) VALUES ('Actress',(select Actresses from Video where ID=@videoId))

INSERT INTO @temp (RoleType,ColumnB) VALUES ('Director',(select Directors from Video where ID=@videoId))

--select * from @temp

DECLARE @idx INT, @cnt INT

SET @idx = 1 -- 給迴圈使用的初始值

SELECT @cnt = COUNT(*) FROM @temp -- 筆數

DECLARE @SplitStr nvarchar(Max), --   暫時放置ColumnB : 演員們的姓名

        @SplitChar nvarchar(Max), -- 分割的字元

        @Columns VARCHAR(Max) --  暫時放置RoleType : 演員腳色有三種 (Actor / Actress / Director)

SET @SplitChar = ';'

DECLARE @actorInVideo table

(

    RoleType nvarchar(50),

    Name nvarchar(50)

)

-- 逐一跑每個row

WHILE @idx <= @cnt BEGIN

        SELECT @SplitStr = ColumnB,@Columns = RoleType FROM @temp WHERE id = @idx

         Declare @Count int =1  -- 巢狀迴圈的初始值

        

         -- 利用CHARINDEX函數，可抓取字串中指定字串的起始位置

         -- 利用SUBSTRING函數 ( expression , start , length ) : 可截取字符串expression，從 start 個字符開始，到Length 結束

         While (Charindex(@SplitChar,@SplitStr)>0) Begin -- 表示有分割字元存在, 這裡設定的是分號

            Insert Into @actorInVideo (RoleType,Name)

                Select @Columns,ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1))) -- 擷取第一個分割字元前面的文字

            Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr)) -- 擷取第一個分割字元後面的所有文字

            Set @Count = @Count + 1

        End

        Insert Into @actorInVideo (RoleType,Name) Select @Columns,ltrim(rtrim(@SplitStr)) -- 剩下沒有分割字元的字串

        SET @idx = @idx + 1 -- 下一個 row

end

select * from @actorInVideo

全文檢索(Full-text)的停用字詞(stopwords)

在資料庫中有一個電影資料表Video , 為了做搜尋功能所以使用了全文索引, 但是要找這一部"Now You See Me",影片時 卻找不到該影片, 即使只用"Now"或是"You"這些常出現的字眼也找不到資料!

上網查了一些資料發現, 原來有所謂的"停用字詞"
http://technet.microsoft.com/zh-tw/library/ms142551.aspx

如果要使用"停用字詞" , 就要在資料庫內下SQL語法 :
ALTER FULLTEXT INDEX ON Video SET STOPLIST = OFF