常用SQL腳本記錄一

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)-@index
SET @InputString = SUBSTRING(@InputString, @index+1, LEN(@InputString)-@index)
分隔符號@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 ar.userid=@userid
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)

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)

posted @ 2017-01-12 17:02 Mark1997 閱讀(385) 評論(0) 收藏舉報

刷新頁面返回頂部

常用SQL腳本記錄一

公告