MS SQL指令應用篇

2022/12/06

查詢某個區段的資料，我們可以在條件上使用between，如下。

(欄位 between CONVERT(date,'2022-10-01') and CONVERT(date,'2022-10-31'))

2021/12/03

1. 字串1101203，拆出年、月、日，年轉西元年

Begin

Declare @StrTmp varchar(7);

Set @StrTmp='1101031';

Select (left(@StrTmp,3)+1911) as yy,CONVERT(int,substring(@StrTmp,4,2)) as mm,CONVERT(int,substring(@StrTmp,6,2)) as dd

End

2023/05/17 另一種方法就是將民國直接轉成數字，再加上19110000

CAST(str(CONVERT(int,left('1120101',7))+19110000) AS Date)

2019/07/02
Select Update指令如下
update fs set fs.Name=m.Name
From Table1 as fs left join Table2 as m on fs.ID=m.ID where m.Name is not null

Select Insert指令如下
Insert Table1 (t1,t2)
Select c1,c2 From Table2 where email='TEST@abc.com'

2019/03/28
通常會使用union去將很多張表合併成一張表，而union與union all的差別在於union all會將所有的資料都合併成一張表，所以得到的結果可能同一筆資料會出現兩筆以上的資料；如果使用union重複的資料則會顯示一筆。

2018/04/17
STATISTICS TIME
參考資料：[SQL SERVER] SET STATISTICS 語法執行時間分析與統計訊息
參考資料：SET STATISTICS TIME (Transact-SQL)
可以檢視自己的SQL指令，花了多少時間執行。
範例：
SET STATISTICS TIME ON;
GO

--SQL 語句--
Select  * from table1

GO

SET STATISTICS TIME OFF;
GO

2017/10/24
datalength函數：可以知道資料的長度，例如姓名中文3個字，在資料庫中的資料長度是6。
replicate(' ',4)函數：第一個參數是自己想要插入的字元，第二個參數是插入幾個字元。

2017/05/23
有一種狀況是某個欄位它不是主鍵，但它不能有重複的資料，這個時候可以透過UNIQUE來限制這個欄位的資料必須為唯一，就能避免重複的資料進入資料表時，指令如下：
ALTER TABLE 資料表名稱
ADD CONSTRAINT 自己給一個識別名稱 UNIQUE(欄位)
有一點要注意，欄位不能為NULL

2017/05/22
針對現有的資料表，運用指令方式加入外部索引鍵，以下是1對N的關係，每1張訂單會包含很多筆的訂單明細
ALTER TABLE test_order_detail
ADD
CONSTRAINT FK_test_order_detail_test_order
FOREIGN KEY (Order_no)
REFERENCES test_order(Order_no);
說明：
CONSTRAINT設定外部索引鍵的識別名稱，FK_test_order_detail_test_order是我自己自訂名稱。
FOREIGN KEY設定test_order_detail資料表的Order_no為外部索引鍵。
REFERENCES指定test_order資料表的Order_no為主鍵(主索引鍵)。

2015/05/11
利用CHARINDEX函數(搜尋字元位置)，找出符合的資料；假設我們要針對某個資料表(名為table1)，某ㄧ個欄位(名為status1，這個欄位會儲存A~D的英文代碼)，找出符合A或C的代碼，如下：
Select *,CHARINDEX(status1,'AC') form table1 where CHARINDEX(status1,'AC')>0
說名：如果CHARINDEX函數有找到A或C，此函數就會回傳字元目前的位置。

2014/11/13
區分大小寫-在欄位後加上 Collate Chinese_Taiwan_Stroke_CS_AS(繁中區分大小寫)
select adm.userid,adm.password from s1 as adm
where adm.userid Collate Chinese_Taiwan_Stroke_CS_AS=@userid
and adm.password Collate Chinese_Taiwan_Stroke_CS_AS=@password

2014/10/29
使用CTE暫存資料，並執行刪除重複的資料，此方法適用於MS SQL 2005以上版本
說明：idno為學生學號，values為學生成績；由學生成績有高到低排序，並取最高的成績。
;WITH temp1 AS (
  select idno,values,ROW_NUMBER() OVER(partition by idno order by values desc) as aid from cv
)
DELETE FROM temp1
WHERE aid > 1;

補充：相同成績要相同排名ROW_NUMBER()改成RANK()

2014/09/03
--將年月日轉換為2014/09/03 10:00
select CONVERT(varchar(16),getdate(),120) as dt
--補充：如果要取到秒數，請將varchar(16)改為varchar(19)；如果只想取得日期，請將varchar(16)改為varchar(10) 。
--取得日期時間的時間，例如：2014-09-03 08:02:24；執行下列指令會取得時間 08:02:24
select CONVERT(varchar(8),getdate(),108)
--補充：如果要取幾點幾分，請將varchar(8)改為varchar(5)。

2014/02/27
取得日期時間
參考資料：CAST 和 CONVERT (Transact-SQL)
--getdate取得現在時間；
select convert(varchar(10),getdate(),120) as str_date,convert(varchar(16),getdate(),120) as str_datetime

轉換int型態
select (CONVERT(int,'2014')-1911)-1 as s
轉換varchar型態
CONVERT(varchar, 欄位) as s