TSQL 常用備忘語法（一）

TSQL永遠都是想到才會寫一次，

每次寫每次忘記怎麼宣告，

怎麼寫IF （或許以後會有其他的補充，等碰到再說了）

1.宣告變數

declare @countAll int
declare @countItem datetime
declare @item_no varchar(20)

1-1.宣告table

declare @t table
(sn int primary key NOT NULL identity(1,1),name varchar(10),category varchar(20),amt int)

insert into @t values ('Liu','Car',5000),('Chiu','Enforcement',3000),('Wang','Car',4000),('Liu','Enforcement',2500)

2.將值傳給變數

set @item_no = 'PC170010'

select @countAll = count(*) from bomdl where CP_ITEM_NO = @item_no

3.顯示訊息

print('bom 內的數量:' + convert(varchar,@countAll))

4.if判斷式

if @countAll > 0
    Begin
        select * from item
        where ITEM_NO = @item_no       
    End

5.while 迴圈

declare @min int
set @min = 1
while(@min <= @id)
begin
    --insert into @tempTable
    select groupid,username,classSubject,score,Last_Update_Time
    from (
    select row_number() over(partition by classsubject order by username) as groupid,* from @t
    ) a   
    where a.groupid = @min
    order by last_update_time desc
   
    set @min = @min+1
end

6.row_number用法

select row_number() over(partition by  ….  order by ….  ) as aid