常用SQL指令碼記錄一

標籤：mil   between   注意   table   去除   位置   where   聲明   returns   

20、SUM()和 列+ 統計結果時：如果列裡有一行為null,SUM函數會忽略它；如果+，則結果集也為NULL了

19 SUBSTRING (expression,startIndex, endIndex)

　　SELECT SUBSTRING (‘Los Angeles‘,1, 3)

　　返回結果Los，預設索引從1開始

18 SQL分隔字串函數返回一個table資料集
，拆成結果集
SELECT * FROM dbo.fn_split(‘A,B,CD,EFG,H‘,‘,‘)
Table裡"-"列SET_006列拆分
SELECT T1.SET_003,T2.splitcolumn
FROM Table AS T1
OUTER APPLY(SELECT splitcolumn FROM dbo.split(T1.SET_006,‘-‘)) AS T2
WHERE T2.splitcolumn<>‘‘

CREATE FUNCTION [dbo].[fn_split]

(
輸入字串
@InputString NVARCHAR(MAX),
分隔字元號
@Seprator NVARCHAR(10)
) RETURNS @tempTable TABLE ([value] NVARCHAR(200))
AS
BEGIN
DECLARE @index int
DECLARE @value NVARCHAR(200)
去除輸入字串前後的空格
SET @InputString = RTRIM(LTRIM(@InputString)) 分隔字元號@Seprator在輸入字串@InputString中的開始位置
SET @index=CHARINDEX(@Seprator, @InputString)
WHILE @index>0
BEGIN
返回輸入字串(@InputString)左邊開始指定個數(@index-1)的字元
SET @value=LEFT(@InputString,@index-1)
插入資料
INSERT @tempTable VALUES(@value)

重新設定輸入字串 截取輸入字串從輸入字串@index+1處開始且長度為LEN(@InputString)[email protected]
SET @InputString = SUBSTRING(@InputString, @index+1, LEN(@InputString)[email protected])
分隔字元號@Seprator在輸入字串@InputString中的開始位置
SET @index=CHARINDEX(@Seprator, @InputString)
END
如果輸入字串不為空白
IF(@InputString<>‘\‘)
BEGIN
INSERT @tempTable VALUES(@InputString)
END
RETURN
END

取一個userid對應多個roleid集合字串
CREATE FUNCTION [dbo].[fn1GetRoleNane]
(
@userid INT
)
returns varchar(500)
AS
BEGIN
DECLARE @tmp VARCHAR(500)
SELECT @tmp=isnull(@tmp+‘,‘, )+RTRIM(LTRIM(r.roleName)) FROM UserAndRole ar, Role r WHERE r.RoleID=ar.roleID AND [email protected]
RETURN ISNULL(@tmp, )
END

17 強制將一個資料表的定序按照另一個表的定序，進行查詢
UPDATE dbo.Table1
SET Col_167 = ISNULL(D.Col_120,0)
FROM dbo.Table1 AS M
LEFT JOIN dbo.MobileInfo AS B2 ON M.Col_047 = B2.M_User
COLLATE Chinese_PRC_CI_AS

16 Alter新增欄位，及給預設值
ALTER table Table1 Add DD_031 nvarchar(50) not null default N

15 RIGHT 和LEFT 返回最右側的n個字元的字串str，或NULL如果任何參數是NULL。
補位: right(‘000000‘ + cast(isnull(MAX(InNO),0)+1 as nvarchar), 6)

15 CAST函數用於將某種資料類型的運算式顯式轉換為另一種資料類型
cast((ISNULL(Col_018,0)*ISNULL(JTAsRate,0)/100-isnull(Col_308,0)) as decimal(18,2))<>Col_305

14 case when 寫法
SELECT [id] ,[name],[school],[score],
case
when score>=90 then ‘優秀‘
when score>=80 then ‘良好‘
when score>=70 then ‘一班‘
when score>=60 then ‘及格‘
else ‘不及格‘
end
from [Table_1]

13 while break流程式控制制語句
while (select avg([score]) from [Table_1] where id between 3 and 15)<85
begin
update [Table_1]
set score=score+5
if (select max(score) from [Table_1] where id between 3 and 15)>=100
break 跳出本層迴圈
continue 跳出本次迴圈
end

12 GOTO 流程式控制制語句的精簡運用（可以從多個迴圈中直接跳出，而Break語句只可以跳出一個While迴圈。）
IF NOT EXISTS(SELECT 1 FROM #TR_LogisticsPH_LH WHERE UniqId=11)
BEGIN
GOTO GetRelust
END

GetRelust:SELECT * FROM #TR_LogisticsPH_LH

11 SQL SERVER 不區分大小寫 -
用不上 UPPER() 和 LOWER(),

10 顯示參數清單和其資料類型
EXEC sp_help Table1

9 通常使用NULLIF()函數可以防止除零錯誤。
select a/nullif(b,0)

8 DATEDIFF功能 返回兩個日期之間的間隔。
文法 DATEDIFF ( date-part, date-expression-1, date-expression-2 )
date-part : year | quarter | month | week | day | hour | minute | second | millisecond
參數 date-part 指定要測量其間隔的日期部分。
date-expression-1 某一間隔的起始日期。從 date-expression-2 中減去該值，返回兩個參數之間 date-parts 的天數。

AND Col_004>DATEADD(MONTH,-3,GETDATE()) 最近三個月內

7、髒讀，
FROM Table1 AS YM WITH(READPAST)
WITH(NOLOCK) 可能把沒有提交事務的資料也顯示出來．
WITH(READPAST) 會把被鎖住的行不顯示出來

6 建立非聚集索
CREATE UNIQUE CLUSTERED INDEX IX_Table1 ON Table1(Col_113)
with schemabinding
AS
select Userinfo *,UserClass * from Userinfo inner join UserClass on uid=cid
注意，表的運算式必須使用兩段式 dbo.mytable 否則會報
"名稱必須由兩部分構成，並且對象不能引用自身。"
而且必須先建一個唯一的聚集所引，否則也是不能進行接下為的所引建立的。
也不是說必須要先用一個唯一的聚集所引才行哦
如果大家想正常使用視圖所引的話就保證你的表中在建完視圖後有一個列是唯一的哦

5 暫存資料表裡判斷是否存在行，修改其值
select t1.col_001 ,t1.col_002 ,t1.sys_guid,t2.col_049,t2.col_005,t2.col_010 ,T2.col_011 ,
t2.col_014,t2.col_013,CAST((isnull(t2.col_036,0)-4.18) as decimal(18,2)) * t2.col_010 as oliCharge
INTO #TEMP
from
Table1 t1 LEFT JOIN Table2 t2 on t1.col_001=t2.col_006
UPDATE temp SET temp.col_011= CAST(ISNULL(temp.col_010,0) AS DECIMAL(18,2)) *4.18
FROM #TEMP temp
WHERE EXISTS(SELECT 1 FROM Table1 WHERE col_001=temp.col_005)
DROP TABLE #TEMP

4 尋找重複值
select top 10 * from Table1 a where exists (select Col_003 from Table1 b group by col_003 having count(Col_003)>2 and a.col_003=b.Col_003)

3 charindex判斷 0，表示不存在
select * from Table1
where (( (CHARINDEX(N‘飛越‘,Col_043,1)=0 OR CHARINDEX(N‘飛越‘,Col_051,1)>0))
AND ISNULL(Col_113, ) <> AND Col_006<=2 AND Col_004=1

-2、NOT IN 另一種寫法
select * from (values(‘13000452388‘,12),(‘13001215092‘,23),(‘13001557229‘,33)) dddd(phoneno,noa)

批量插入
insert into persons
(id_p, lastname , firstName, city )
values
(200,‘haha‘ , ‘deng‘ , ‘shenzhen‘),
(201,‘haha2‘ , ‘deng‘ , ‘GD‘),
(202,‘haha3‘ , ‘deng‘ , ‘Beijing‘);

/****** 簡單說明(作者***：日期)**/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = ‘‘) AND type in (N‘U‘))
DROP TABLE table
GO
CREATE TABLE table
(
UniqueID bigint primary key identity(1,1),
YDNo NVARCHAR(50),
YDSJ DATETIME,
ReadMan NVARCHAR(50),
ReadDepartment NVARCHAR(50),
ReadDate DATETIME,
PDANumber NVARCHAR(50),
sys_guid varchar(50) DEFAULT (replace(newid(),‘-‘, ))
)

聲明表變數 -
declare @Detail table(ID int,Price float)
insert into @order(ID,Name) values(1,‘aa‘),(2,‘bb‘),(3,‘cc‘)

1、統計每月，每日資料
每月
select year(ordertime) 年，
month(ordertime) 月,
sum(Total) 銷售合計
from 訂單表
group by year(ordertime)，
month(ordertime

每日
select year(ordertime) 年，
month(ordertime) 月,
day(ordertime) 日，
sum(Total) 銷售合計
from 訂單表
group by year(ordertime)，
month(ordertime),
day(ordertime)

另外每日也可以這樣:
select convert(char(8),ordertime,112) dt，
sum(Total) 銷售合計
from 訂單表
group by convert(char(8),ordertime,112)

查詢當天：
select * from info where DateDiff(dd,datetime,getdate())=0

查詢24小時內的:
select * from info where DateDiff(hh,datetime,getDate())<=24
本月記錄
SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0

化作字串比較
Select * From VIEW_CountBill Where Convert(varchar(10),[time],120) = Convert(varchar(10),getDate(),120)

 

常用SQL指令碼記錄一