跳到主要內容

發表文章

目前顯示的是有「SQL Server」標籤的文章

ROW_NUMBER() / PARTITION BY / RANK() OVER / DENSE_RANK() OVER

SELECT ROW_NUMBER() OVER (ORDER BY account_no) as RowNumNo , * from account_item --RowNumNo         seq_no                  code_no                   account_no        name --30                15                A                5201115                其他材料 --31                16                A                5201120                投入成本 --32                17                A                5201200  ...

CURSOR INCLUDE CURSOR

DECLARE @AEName VARCHAR(50) DECLARE @StartDate DATETIME DECLARE @Qty float DECLARE CURSOR1 CURSOR FOR SELECT AEName , Qty , StartDate FROM TBCursor WHERE  CONVERT(VARCHAR(10),StartDate,111) BETWEEN '2018/01/10' AND '2019/07/01' AND QTY > 400 OPEN CURSOR1 FETCH NEXT FROM CURSOR1 INTO @AEName, @Qty  ,@StartDate WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY IF @Qty > 500 BEGIN DECLARE @TBCursor2AEName  VARCHAR(50); DECLARE @TBCursor2AEID VARCHAR(50); DECLARE CURSOR2 CURSOR FOR SELECT AEID , AEName FROM [TBCursor2] WHERE AEName = @AEName OPEN CURSOR2 FETCH NEXT FROM CURSOR2 INTO @TBCursor2AEID , @TBCursor2AEName WHILE @@FETCH_STATUS = 0 BEGIN          PRINT @TBCursor2AEID +'/'+ @TBCursor2AEName            FETCH NEXT FROM CURSOR2 INTO @TBCursor2AEID , @TBCursor2AEName; END CLOSE CURSOR2;           ...

CURSOR TRY CATCH

DECLARE @AEName VARCHAR(50) DECLARE @StartDate DATETIME DECLARE @Qty float DELETE FROM SubTB; DECLARE CURSOR1 CURSOR FOR SELECT AEName , Qty , StartDate FROM TBCursor WHERE  CONVERT(VARCHAR(10),StartDate,111) BETWEEN '2018/01/10' AND '2019/07/01 ' OPEN CURSOR1 FETCH NEXT FROM CURSOR1 INTO @AEName, @Qty  ,@StartDate WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY IF @Qty > 500 BEGIN INSERT INTO SubTB(AEName,Qty) Values(@AEName,@Qty); DECLARE @MaxQty float; DECLARE @NO VARCHAR(10); SET @MaxQty = (SELECT MAX(Qty) FROM TBCursor); SELECT @NO = CONVERT(VARCHAR(8),GETDATE(),112) ; SET @NO = REPLACE(STR(@NO,10),SPACE(1),'0'); INSERT INTO TBCursor2(AEID,AEName,Qty,StartDate,EndDate) Values(@NO,@AEName, @MaxQty, DATEADD(MONTH,-3,GETDATE()) ,DATEADD(MONTH,1,GETDATE())); END ELSE BEGIN INSERT INTO SubTB(AEName,Qty) Values(@AEName + '不足' , 500 - @Qty ); END FETCH NEXT FROM CURSOR...

CREATE PROCEDURE AND CURSOR

回傳資料集 CREATE PROCEDURE HumanResources.uspGetAllEmployees  AS      SET NOCOUNT ON;      SELECT LastName, FirstName, JobTitle, Department      FROM HumanResources.vEmployeeDepartment;  EXEC HumanResources.uspGetAllEmployees; c#: cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "HumanResources.uspGetAllEmployees"; //cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1; SqlDataReader reader = cmd.ExecuteReader(); while ((dr.Read()){} ----------------------------------------------------------------------------------- 回傳 OUTPUT CREATE PROCEDURE GetImmediateManager     @employeeID INT,     @managerID INT OUTPUT  AS  BEGIN     SELECT @managerID = ManagerID     //將抓出的值止訂給 OUTPUT變數    FROM HumanResources.Employee    WHERE EmployeeID = @employeeID  END DECLARE @managerID INT EXEC GetImmediateMana...

T-SQL 次序函數

select row_number() over(order by zip1 asc) as tt ,zip1 from hc_cust where zip1 <> ''  --直接排序 select row_number() over(partition by zip1  order by zip1 asc) as tt ,zip1 from hc_cust where zip1 <> ''  --單一群組內各自做排序 select rank() over(order by zip1 asc) as tt ,zip1 from hc_cust where zip1 <> ''  --同群組同號碼,下一群組跳號 select dense_rank() over(order by zip1 asc) as tt ,zip1 from hc_cust where zip1 <> ''  --同群組同號碼,下一群組接號 -------------------------------------------------------------------------------------------------- SELECT CONVERT(VARCHAR(10), DATEADD(week, DATEDIFF(week, 0, getdate()), 0), 111) AS 本周一 SELECT CONVERT(VARCHAR(10), DATEADD(week, DATEDIFF(week, 0, getdate()), 5), 111) AS 本周六 SELECT CONVERT(VARCHAR(10), DATEADD(week, DATEDIFF(week, 6, getdate()), 0), 111) AS 上周一 SELECT CONVERT(VARCHAR(10), DATEADD(week, DATEDIFF(week, -6, getdate()), 0), 111) AS 下周一 DECLARE @myDate as DATETIME      SET @myDate = DATEADD(week, ...

T-SQL ROW_NUMBER()

SELECT ROW_NUMBER() OVER (ORDER BY account_no) as RowNumNo , * from account_item --RowNumNo         seq_no                  code_no                   account_no        name --30                15                A                5201115                其他材料 --31                16                A...

T-SQL 語法 2

//排序函數:ROW_NUMBER()、RANK() 與 DENSE_RANK() 遇到相同的價格時會依其他的依據來決定排名, 因此名次均不會相同 1 2 3 4 5 6 7 SELECT 書籍編號, 書籍名稱, 價格, 出版公司,ROW_NUMBER() OVER(ORDER BY 價格) AS 價格排名 FROM 書籍 RANK() 函數遇到相同的數值會給相同的排名, 其後的排名則會跳過 1 2 2 2 4 5 5 SELECT 書籍編號, 書籍名稱, 價格, 出版公司,RANK() OVER(ORDER BY 價格) AS 價格排名 FROM 書籍 若不想如上例自動跳過後面排名, 想呈現 1 2 2 2 3 4 4 SELECT 書籍編號, 書籍名稱, 價格, 出版公司,DENSE_RANK() OVER(ORDER BY 價格) AS 價格排名 FROM 書籍 ------------------------------------------------------------------------ //ROW_NUMBER WITH result AS (SELECT *, ROW_NUMBER() OVER (ORDER BY seq_no) AS rownum FROM company) SELECT  *  FROM   result  WHERE  rownum > 0 AND rownum < 6 ------------------------------------------------------------------------ //PIVOT SELECT   VendorID, [164] AS EmpNo1 , [198]  AS EmpNo2, [223]  AS EmpNo3 FROM    (SELECT     PurchaseOrderID, EmployeeID, VendorID FROM  Purchasing.PurchaseOrderHeader) p PIVOT (COUNT(Purch...

T-SQL 語法 1

select  cast(c1  as  integer)  from t1  //將欄位值取出並轉換為數值型態 select  cast(substr (c1,1,1)  as  integer)  from t1 select  substr (c1,1,2)  from t1  //DB2取出欄位值並擷取其長度[欄位名稱,開始(從1起始),長度],MS SQL使用substring ------------------------------------------------------------------------------------------------------ create table t1(c1 varchar(8) not null,c2 varchar(5), c3 int(3)) //建立table drop table t1 //刪除table alert table t1 add c1 varchar(5) default 0 not null //新增table欄位 alter table t1 drop column c1  //刪除table欄位 alter table t1 modify c1 varchar(8)  //修改table欄位 alter table t1 rename column c1 to c4  //更改欄位名稱 ----------------------------------------------------------------------- insert into t1(c1,c2,c3) values('str1','str2',int1)  //新增 ----------------------------------------------------------------------- delete from t1 where c1='str1'  //刪除 -------------------------------------------------------------...