/*==========================代码格式规范=====================*/
/*
1.T-SQL脚本有必要的缩进和换行，代码层次结构清晰，
一行的最大长度一般不要87个字符；代码使用统一的风格，
例如：如果使用空格作为缩进，则不能再使用TAB做缩进处理
*/
USE Test
GO
--错误的格式
DECLARE@TransactionNumber INT
,@purnoCHAR(8)
SET@purno='105336'
IFISNULL(@purno,'')<>''
BEGIN
SELECTTOP 1 @TransactionNumber=TransactionNumber FROM [SCM].[dbo].[potran01] WITH(NOLOCK) WHERE purno=@purno
END
--正确的格式:
DECLARE@TransactionNumber INT
,@purnoCHAR(8)
SET@purno='105336'
IFISNULL(@purno,'')<>''
BEGIN
SELECTTOP 1 @TransactionNumber=TransactionNumber
FROM[SCM].[dbo].[potran01] WITH (NOLOCK)
WHEREpurno=@purno
END
/*
2.脚本中的所有关键字、系统变量名、系统函数名全部大写
（可以参考一个SQL联机帮助中对于该关键字描述时使用的是大写还是小写）
*/
--错误写法
selectcase @@servicename
when'mssqlserver' then @@servername
else@@servicename
end asInstanceName
--正确写法
SELECTCASE @@SERVICENAME
WHEN'MSSQLSERVER' THEN @@SERVERNAME
ELSE@@SERVICENAME
END ASInstanceName
/*创建对象请添加必要的注释
3.存储过程、视图、用户定义函数有合理的注释，至少包括：创建人、
创建日期、修改人、修改日期、功能描述、参数说明。
*/
USE Test
GO
/*===========================CreateSP==========================
**DB:Test
**Type:Procedure
**ObjectName:dbo.Up_Test_Print
**Creater:Cherish
**Createdate: 2008-11-7
**Modifyby: Cherish
**Modifydate: 2008-11-8
**Function:Testing print in SSB
**Variable:N/A
=====================================================================*/
CREATEPROCEDURE dbo.Up_Test_Print
AS
SETNOCOUNT ON
BEGIN
DECLARE@do INT
,@loopINT
SET@do=0
SET@loop=100
WHILE@do<@loop
BEGIN
PRINT'Test Print in sp.'
SET@do=@do+1
END
END
GO
/*==========================Secondpart:代码规范=====================*/
/*
--BYDBA1.请在代码的开始处添加USE GO指令
*/
USE DB
GO
/*
--BYDBA3.请指定对象的架构者(SQL2K中称为所有者)（一般都是dbo）
*/
SELECTC1,C2
FROMdbo.Test WITH (NOLOCK)
/*
--BYDBA1.已经在数据库中，不用加库名。
*/
USE Test
GO
CREATEVIEW dbo.V_TestView
AS
SELECTC1,C2
FROM[Test].[dbo].[Test] WITH (NOLOCK)--BYDBA 1.已经在数据库中，不用加库名。
/*
--BYDBA1.请显示罗列表字段。
*/
USE TEST
GO
IFOBJECT_ID('dbo.Test_SELECT') IS NOT NULL
DROPTABLE dbo.Test_SELECT
CREATETABLE dbo.Test_SELECT
(
ID INTIDENTITY(1,1) NOT NULL
,SONumberINT
,CustomerNumberINT
,ShippingCodeCHAR(15)
,CONSTRAINTPK_Test_SELECT PRIMARY KEY
(
ID ASC
)
)
--不规范的写法
SELECT*
FROMdbo.Test_SELECT WITH (NOLOCK)
--推荐的写法
SELECTID
,SONumber
,CustomerNumber
,ShippingCode
FROMdbo.Test_SELECT WITH (NOLOCK)
/*
如果一个T-SQL语句涉及到多个表，则引用的每个列必须指定该列所属的对象
*/
--不规范的写法
SELECTedate --BYDBA 1.请指明字段的表别名。
,ISNULL(vendno,'')--BYDBA 1.请指明字段的表别名。
FROM[SCM].[dbo].[arinvt01] AS A WITH (NOLOCK)
INNERJOIN [SCM].[dbo].[potran01] AS B WITH (NOLOCK)
ONA.Item=B.Item
WHEREpurno='105336'--BYDBA 1.请指明字段的表别名。
--规范的写法
SELECTA.edate
,ISNULL(B.vendno,'')
FROM[SCM].[dbo].[arinvt01] AS A WITH (NOLOCK)
INNERJOIN [SCM].[dbo].[potran01] AS B WITH (NOLOCK)
ONA.Item=B.Item
WHEREA.purno='105336'
/*
6.在INSERT语句中，必须指定插入列的列表, 否则的话,
表结构略有差异就会导致插入失败或者插入到不正确的列中
*/
USE TEST
GO
IFOBJECT_ID('dbo.Test_INSERT') IS NOT NULL
DROPTABLE dbo.Test_INSERT
CREATETABLE dbo.Test_INSERT
(
ID INT NOT NULL
,SONumberINT
,CustomerNumberINT
,CONSTRAINTPK_Test_INSERT PRIMARY KEY
(
ID ASC
)
)
IFOBJECT_ID('dbo.Test_INSERT1') IS NOT NULL
DROPTABLE dbo.Test_INSERT1
CREATETABLE dbo.Test_INSERT1
(
ID INTNOT NULL
,SONumberINT
,CustomerNumberINT
,CONSTRAINTPK_Test_INSERT1 PRIMARY KEY
(
ID ASC
)
)
INSERTINTO dbo.Test_INSERT1(ID,SONumber,CustomerNumber)
SELECT1,1,3
--错误的写法
INSERTINTO dbo.Test_INSERT--BYDBA 1.请显示指定为INNER JOIN。
SELECT *
FROMdbo.Test_INSERT1 AS A WITH (NOLOCK)
WHERENOT EXISTS(SELECT TOP 1 1
FROMdbo.Test_INSERT AS B WITH (NOLOCK)
WHEREA.ID=B.ID)
GO
--修改表
ALTERTABLE Test_INSERT
ADDShippingCode CHAR(15)
GO
--再次插入数据报错。
INSERTINTO dbo.Test_INSERT
SELECT *
FROMdbo.Test_INSERT1 AS A WITH (NOLOCK)
WHERENOT EXISTS(SELECT TOP 1 1
FROMdbo.Test_INSERT AS B WITH (NOLOCK)
WHEREA.ID=B.ID)
GO
--正确的写法为:
INSERTINTO dbo.Test_INSERT(ID
,SONumber
,CustomerNumber)
SELECTID
,SONumber
,CustomerNumber
FROMdbo.Test_INSERT1 AS A WITH (NOLOCK)
WHERENOT EXISTS(SELECT TOP 1 1
FROMdbo.Test_INSERT AS B WITH (NOLOCK)
WHEREA.ID=B.ID)
/*
7.对于SELECT中涉及的表和视图，在非事务和特别的完整性要求的上下文中，
使用TABLE Hints—WITH(NOLOCK)
*/
--Demo:
USE TEST
GO
IFOBJECT_ID('dbo.Test_NOLOCK') IS NOT NULL
DROPTABLE dbo.Test_NOLOCK
CREATETABLE dbo.Test_NOLOCK
(
ID INTIDENTITY(1,1)
,NAMECHAR(36)
)
DECLARE@i INT
SET @i=0
BEGINTRY
TRUNCATETABLE dbo.Test_NOLOCK
BEGINTRAN
WHILE@i<10
BEGIN
INSERTINTO Test_NOLOCK(NAME)
SELECTNEWID()
SET@i=@i+1
END
--COMMIT
END TRY
BEGINCATCH
ROLLBACK
ENDCATCH
--不允许读脏，只能读取已经提交的数据
SELECTID,NAME
FROMdbo.Test_NOLOCK
--可以读取没有提交的数据
SELECTID,NAME
FROMdbo.Test_NOLOCK WITH (NOLOCK)
/*
为了简化控制, 所有的锁提示都需要加WITH, 即FROM tb (NOLOCK) 需要修改为 FROM tb WITH(NOLOCK)
*/
--BYDBA1.请使用WITH(NOLOCK)，而非NOLOCK。
--IND2WHP01
SELECTVendorNumber,
WareHouseNumber,
SoNumber
FROMS7EDIDB01.EDI.DBO.EDIInvoiceMatchTransaction /*WITH*/ (NOLOCK)
WHEREsonumber = '86022942'
/*
8.子查询中，只查询出必须的列，不要包含与处理需求无关的列
*/
--错误的写法
SELECTID,NAME
FROMdbo.Test_NOLOCK WITH (NOLOCK)
WHEREEXISTS (
SELECT 1AS ID
, 'Name'AS Name
, 'Test'AS C3
UNIONALL
SELECT 2
, 'ABCE'AS Name
,'Test_NOLOCK' AS C3
)
--正确的写法
SELECTID,NAME
FROMdbo.Test_NOLOCK WITH (NOLOCK)
WHEREEXISTS( SELECT TOP 1 1
FROM
(
SELECT 1AS ID
UNIONALL
SELECT 2
) AS A
)
/*
--BYDBA1.变量赋值,请修改为SELECT TOP 1...
*/
USE TEST
GO
IFOBJECT_ID('dbo.Test_TOP1') IS NOT NULL
DROPTABLE dbo.Test_TOP1
CREATETABLE dbo.Test_TOP1
(
IDINT
,TransactionNumberCHAR(25)
,purnoCHAR(8)
)
INSERTINTO dbo.Test_TOP1(ID,TransactionNumber,purno)
SELECT234434,'1111111111','105336'
UNIONALL
SELECT234445,'2222222222','105336'
UNIONALL
SELECT234345,'fdfdrynkjs','1053334'
SELECTTransactionNumber,purno
FROMdbo.Test_TOP1 WITH (NOLOCK)
--错误的写法一
DECLARE@TransactionNumber CHAR(25)
SELECT@TransactionNumber=ISNULL(TransactionNumber,'')--BYDBA 1.变量赋值,请修改为SELECT TOP 1...
FROM[dbo].[Test_TOP1] WITH (NOLOCK)
WHEREpurno='105336'
SELECT@TransactionNumber
----错误的写法二
SET@TransactionNumber= ISNULL(
(SELECTTOP 1 TransactionNumber
FROM[dbo].[Test_TOP1] WITH (NOLOCK)
WHEREpurno='105336' )
,'')
SELECT@TransactionNumber
--正确的写法
SELECTTOP 1 @TransactionNumber=ISNULL(TransactionNumber,'')
FROM[dbo].[Test_TOP1] WITH (NOLOCK)
WHEREpurno='105336'
SELECT@TransactionNumber
/*
创建索引时,显示定义索引的类型(CLUSTERED OR NONCLUSTERED)、FILLFACTOR
*/
CREATENONCLUSTERED INDEX ix_TableName_Column1_Column2 ON dbo.TableName
(
Column1ASC
,Column2DESC
)
WITHFILLFACTOR = 80
/*
禁止在使用了事务的情况下，不编写防止造成未提交或者未回滚事务的情况的处理代码
*/
USE TEST
GO
IFOBJECT_ID('dbo.Test_TRAN') IS NOT NULL
DROPTABLE dbo.Test_TRAN
CREATETABLE dbo.Test_TRAN
(
ID INTIDENTITY(1,1)
,NumberINT
)
BEGIN
BEGINTRAN
INSERTINTO dbo.Test_TRAN(Number)
SELECT12
UNIONALL
SELECT1/0
COMMIT
END
/*
对于类型不相同的两个数据进行逻辑处理（例如：比较或者赋值）时，使用显式的数据类型转换(CAST或者CONVERT)，特别是在WHERE语句中
*/
/*
--BYDBA3.请保证WHERE语句中=两边的数据类型一致，否则SQLServer走不到Index
使用显式的数据类型转换（CAST或者CONVERT）
*/
--D2WHP01
--不正确的写法
SELECTA.edate
,B.vendno
FROM[SCM].[dbo].[arinvt01] AS A WITH (NOLOCK)
INNERJOIN [SCM].[dbo].[potran01] AS B WITH (NOLOCK)
ONA.Item=B.Item
WHEREB.purno=105336
--正确的写法
SELECTA.edate
,B.vendno
FROM[SCM].[dbo].[arinvt01] AS A WITH (NOLOCK)
INNERJOIN [SCM].[dbo].[potran01] AS B WITH (NOLOCK)
ONA.Item=B.Item
WHEREB.purno=CONVERT(CHAR(8),105336)--B.purno的数据类型为char(8)
--或者是
SELECTA.edate
,B.vendno
FROM[SCM].[dbo].[arinvt01] AS A WITH (NOLOCK)
INNERJOIN [SCM].[dbo].[potran01] AS B WITH (NOLOCK)
ONA.Item=B.Item
WHEREB.purno='105336'
/*
SELECTTOP 1 ... ORDER BY与MAX，MIN
*/
SELECTTOP 1 item
FROM[SCM].[dbo].[potran01] WITH (NOLOCK)
ORDER BYitem DESC
--建议使用MAX函数。
SELECTMAX(item)
FROM[SCM].[dbo].[potran01] WITH (NOLOCK)
/*----------------------BYDBA3.去掉RTRIM函数。--------------------
1.去掉Isnull里面的RTRIM函数
2.使用函数 LEN()的时候，建议去掉 RTRIM。
3.做字符串比较，SQLServer会忽略掉尾部的空格
*/
DECLARE@string CHAR(50)
SET@string='Test isnull '
--Isnull
SELECTLEN(ISNULL(@string,''))
,LEN(ISNULL(RTRIM(@string),''))
--使用Len函数
SELECTLEN(RTRIM(@string))
,LEN(@string)
--字符串比较
IF@string<>'Test isnull'
SELECT'Not Equal'
ELSE IF@string='Test isnull'
SELECT'Equal'
ELSE
SELECT'Unknow'
/*创建Index需要注意的地方。
--BYDBA1.请按照规范对INDEX命名，IX_表名_字段名。
--BYDBA1.创建索引时请显式申明聚集类型 NONCLUSTERED
--BYDBA1.添加索引的时候，请指定填充因子 WITH FILLFACTOR = 90
*/
CREATENONCLUSTERED INDEX IX_DBMOVE_Training_Trn_Date
ONdbo.DBMOVE_Training
(
Trn_DateASC
)WITHFILLFACTOR=80
/*
12.禁止在使用了事务的情况下，不编写防止造成未提交或者未回滚事务的情况的处理代码
--BYDBA1.SP中禁止使用PRINT语句。
*/
BEGINTRANSACTION;
BEGINTRY
INSERTINTO dbo.users(id
,name
,Age)
SELECTUID,U_Name,U_Age
FROM#Temp AS a
WHERENOT EXISTS(SELECT TOP 1 1
FROMdbo.users AS b WITH (NOLOCK)
WHEREb.id=a.UID)
PRINT'Insert successfully'--BYDBA 1.禁止在存储过程中输出不需要的信息
COMMIT TRANSACTION
END TRY
BEGINCATCH--BYDBA 1.请编写防止造成未提交或者未回滚事务的情况的处理代码。
ENDCATCH
/*
14.定义游标时，如果不是特别需要，使用LOCAL关键显式的将游标定义为局部游标，
尽量避免使用全局（GLOBAL，这是数据库的默认行为
）游标；
没有特殊需要的话，尽量使用FORWARD_ONLY READONLY 游标,同时尽量使用静态游标 STATIC；
避免大量数据的处理避免使用游标"
*/
DECLAREcur_MyCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECTTOP 10 QUOTENAME(SCHEMA_NAME(schema_id),'[]')+'.'+QUOTENAME(name,'[]')
FROMsys.tables WITH (NOLOCK)
/*---------------BYDBA3.尽量不使用游标,请使用其他的方法。----------------
1.拼接字符串
2.update/delete/insert记录
*/
/*
15.缓存临时数据时，使用真正的临时表（#或者##开头）的表，避免将临时表缓存到正式表中
--这个会在稍后的专题Demo中有演示
*/
/*
16.动态T-SQL处理语句中，如果涉及到变量，尽量使用sp_executesql，通过参数传递进行处理，
避免使用EXEC硬拼SQL语句存储过程或函数中不允许包含未使用的参数或变量
--这个会在稍后的专题Demo中有演示
*/
/*
17.脚本中，禁止出现对正式对象的DROP
*/
DROPPROC dbo.up_test
DROPTABlE dbo.Test
DROPVIEW dbo.V_Test
--MarkDelete
/*
18.能使用INNERJOIN实现的处理，不要使用外连接
*/
DECLARE@t1 TABLE
(
id INT
,nameVARCHAR(10)
)
DECLARE@t2 TABLE
(
id INT
,nameVARCHAR(10)
)
INSERTINTO @t1
SELECT1,'a'
UNIONALL
SELECT2,'b'
UNIONALL
SELECT3,'c'
INSERTINTO @t2
SELECT4,'a'
UNIONALL
SELECT2,'b'
UNIONALL
SELECT5,'c'
---不推荐的写法
SELECTA.id
FROM @t1AS A
LEFTJOIN @t2 AS B
ONA.id=B.id AND B.name='B'
WHEREB.id IS NOT NULL
--推荐写法一
SELECTA.id
FROM @t1AS A
INNERJOIN @t2 AS B
ONA.id=B.id
WHEREB.name='B'
--推荐写法二
SELECTA.id
FROM @t1AS A
WHEREEXISTS(
SELECTTOP 1 1
FROM @t2AS B
WHEREA.id = B.id
ANDB.name='B'
)
--推荐写法三
SELECTA.id
FROM @t1AS A
WHERE idIN(
SELECTid
FROM @t2AS B
WHEREB.name='B'
)
--BYDBA3.请将Left join连接修改为Inner Join连接。（Left连接表中在Where子句中有筛选条件）。
SELECTA.id,B.name
FROM @t1AS A
LEFTJOIN @t2 AS B
ONA.id=B.id
WHEREB.name='B'
SELECTA.id,B.name
FROM @t1AS A
INNERJOIN @t2 AS B
ONA.id=B.id
WHEREB.name='B'
/*
19.数据本身不会重复，或者不需要防止重复的UNION，改用UNION ALL
*/
DECLARE@t1 TABLE
(
id INT
,nameVARCHAR(10)
)
DECLARE@t2 TABLE
(
id INT
,nameVARCHAR(10)
)
INSERTINTO @t1
SELECT1,'a'
UNIONALL
SELECT2,'b'
UNIONALL
SELECT3,'c'
INSERTINTO @t2
SELECT4,'d'
UNIONALL
SELECT2,'e'
UNIONALL
SELECT5,'f'
SELECTname
FROM @t1
UNION
SELECTname
FROM @t2
/*
20.使用IF语句时，如果满足条件和不满足条件都会做相应的处理，则不允许使用NOT做负向判断
*/
IFEXISTS(SELECT TOP 1 1
FROMdbo.TableName WITH (NOLOCK)
)
BEGIN
--dosomething
END
ELSE
BEGIN
--dosomething
END
/*
产品环境Replication的连接:http://s7dbm02/sync/syn.xml
21.不允许修改 Replication Chain 源头之外的表结构及数据
*/
/*
ReplicationChain：
ABS_SQL.Abs.dbo.APCHCK01->NEWSQL2.Abs.dbo.APCHCK01->S1RPT02.Act.dbo.AbsAPChck01
那么我们只允许：
*/
ALTERTABLE ABS_SQL.Abs.dbo.APCHCK01
ADD XXXDATATYPE
/*
22.如果 Replication Chain 经过某服务器，则在该服务器上做查询时，
不允许跨服务器查询该Replication Chain 上的相关表
*/
/*
ReplicationChain：
ABS_SQL.Abs.dbo.APCHCK01->NEWSQL2.Abs.dbo.APCHCK01->S1RPT02.Act.dbo.AbsAPChck01
那么我们不允许：
*/
--InNEWSQL2
SELECTTOP 1 *
FROMABS_SQL.Abs.dbo.APCHCK01 WITH (NOLOCK)
--或者
SELECTTOP 1 *
FROMS1RPT02.Abs.dbo.AbsAPChck01 WITH (NOLOCK)
--正确的做法是：
--InNEWSQL2
SELECTTOP 1 *
FROMAbs.dbo.APCHCK01 WITH (NOLOCK)
-------操作Replication链上的表应该特别小心
/*
DECLARE
@rows int,
@rows_limit int,
@row_batch int,
@row_count int
;
SELECT
@rows = 0,
@rows_limit = 50000, -- 处理的最大记录数限制
@row_batch = 1000, -- 每批处理的记录数
@row_count = @row_batch
;
WHILE@row_count = @row_batch
AND @rows < @rows_limit
BEGIN;
DELETE TOP(@row_batch) SRC
OUTPUT deleted.* -- 如果是数据转移, 则有OUTPUT 删除记录到目标表, 否则没有这个
INTO target_table -- 目的表
--OUTPUT deleted.col1, deleted.col2 -- 如果源和目标表的列序不一样, 或者只转移指定的列
-- INTO tabger_table(
-- col1, col2)
FROM source_table SRC -- 源表
WHERE filter = 1 -- 记录处理条件
;
SELECT
@row_count = @@ROWCOUNT,
@rows = @rows + @row_count
;
WAITFOR DELAY '00:00:10'; -- 每批处理之间的延时
END;
*/
/*==========================Thethird part:Commonly Errors=====================*/
/*
1.试图在 ON 条件中过滤不满足条件的记录:
*/
/*
USE TEST
GO
IFOBJECT_ID('dbo.T1') IS NOT NULL
DROPTABLE dbo.T1
CREATE
TABLEdbo.T1
(
ID INT
,nameVARCHAR(20)
)
IFOBJECT_ID('dbo.T2') IS NOT NULL
DROPTABLE dbo.T2
CREATETABLE dbo.T2
(
ID INT
,name
VARCHAR(20)
)
INSERTINTO dbo.T1(ID,name)
SELECT1,'a'
UNIONALL
SELECT1,'B'
UNIONALL
SELECT2,'c'
UNIONALL
SELECT2,'d'
UNION
ALL
SELECT3,'a'
UNIONALL
SELECT4,'h'
INSERTINTO dbo.T2(ID,name)
SELECT1,'c'
UNIONALL
SELECT1,'f'
UNIONALL
SELECT1,'C'
UNIONALL
SELECT2,'g'
UNIONALL
SELECT2,'h'
UNIONALL
SELECT3,'C'
*/
--错误的写法
SELECTA.ID,A.NAME, B.ID,B.name
FROMdbo.t1
A WITH(NOLOCK)
LEFTJOIN dbo.t2 B
ONA.ID = B.ID
AND A.name = 'a'
AND B.name = 'c'
--正确的写法
SELECTA.ID,A.NAME, B.ID,B.name
FROMdbo.t1 A WITH (NOLOCK)
LEFTJOIN dbo.t2 B WITH (NOLOCK)
ONA.ID = B.ID AND B.name =
'c'
WHEREA.name = 'a'
--比较以下两种写法的差异
SELECTA.ID,A.NAME, B.ID,B.name
FROMdbo.t1 A WITH (NOLOCK)
LEFTJOIN dbo.t2 B WITH (NOLOCK)
ONA.ID = B.ID AND B.name =
'c'
SELECTA.ID,A.NAME, B.ID,B.name
FROMdbo.t1 A WITH (NOLOCK)
LEFTJOIN dbo.t2 B WITH (NOLOCK)
ONA.ID = B.ID
WHEREB.name =
'c'
/*
4.使用LEFTJOIN配合WHERE条件中，判断右边表关键值是否为NULL来查询出仅在左边表出现的记录
*/
SELECTA.*
FROMdbo.T1 AS A WITH (NOLOCK)
LEFTJOIN dbo.T2 AS B WITH (NOLOCK)
ONA.id=B.id
WHEREB.id IS NULL
--建议修改：
SELECT *
FROMdbo.T1 AS A WITH (NOLOCK)
WHERENOT EXISTS(SELECT TOP 1 1
FROMdbo.T2 AS B WITH (NOLOCK)
WHEREB.id=A.id
)
--或者
SELECT *
FROMdbo.T1 AS A WITH (NOLOCK)
WHERE IDNOT IN (SELECT ID
FROMdbo.T2 AS B WITH (NOLOCK)
)
/*----------------------------------
--BYDBA1.避免使用SET XACT_ABORT ON 自动回滚事务，尽量使用TRY...CATCH语句
*/
--不建议
SETXACT_ABORT ON
BEGIN
BEGINTRAN
UPDATEdbo.Users
SETName='Test'
WHEREID=123456
COMMIT
END
--建议做法
BEGINTRY
BEGINTRAN
UPDATEdbo.Users
SETName='Test'
WHEREID=123456
COMMIT
END TRY
BEGINCATCH
ROLLBACK
ENDCATCH
/*-------------------------------------------------
--BYDBA3.建议采用临时表，因为最少会对同一张表查询两次
并且两次查询的大部分条件相同。
--------------------------------------------------*/
--GQC:D2WHP01
SELECT *
FROM[SCM].[dbo].[arinvt01] WITH (NOLOCK)
WHEREitem IN(
SELECTitem
FROM[SCM].[dbo].[potran01] WITH (NOLOCK)
wherepurno='105336')
SELECT *
FROM[SCM].[dbo].[arinvt01] WITH (NOLOCK)
WHEREitem IN(
SELECTitem
FROM[SCM].[dbo].[potran01] WITH (NOLOCK)
wherepurno ='110983')
--建议做法:
IFOBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROPTABLE #temp
SELECTitem,purno
INTO#temp
FROM[SCM].[dbo].[potran01] WITH (NOLOCK)
wherepurno IN ('110983','105336')
SELECT *
FROM[SCM].[dbo].[arinvt01] WITH (NOLOCK)
WHEREitem IN (SELECT item
FROM#temp
WHEREpurno='110983')
SELECT *
FROM[SCM].[dbo].[arinvt01] WITH (NOLOCK)
WHEREitem IN (SELECT item
FROM#temp
WHEREpurno='105336')
/*----------------------------------------------------
--BYDBA3.INNER Join连接操作,请将筛选条件与连接条件分离，
--筛选条件放到Where字句中。
---------------------------------------------------*/
SELECTA.edate
,B.vendno
FROM[SCM].[dbo].[arinvt01] AS A WITH (NOLOCK)
INNERJOIN [SCM].[dbo].[potran01] AS B WITH (NOLOCK)
ONA.Item=B.Item AND B.purno='105336'
--修改为：
SELECTA.edate
,B.vendno
FROM[SCM].[dbo].[arinvt01] AS A WITH (NOLOCK)
INNERJOIN [SCM].[dbo].[potran01] AS B WITH (NOLOCK)
ONA.Item=B.Item
WHEREB.purno='105336'
/*------------------------------------------------------------------
--BYDBA1.使用COUNT(*)判断存在与否
------------------------------------------------------------------*/
IF(SELECT COUNT(*)
FROM[SCM].[dbo].[VendorReturnPolicy] WITH (NOLOCK)
WHEREVendorNumber='20001') >0
BEGIN
SELECT VendorCreditDays,VRPNumber
FROMdbo.VendorReturnPolicy WITH (NOLOCK)
WHEREVRPNumber = 3279
END
--修改为:
IFEXISTS(SELECT TOP 1 1
FROM[SCM].[dbo].[VendorReturnPolicy] WITH (NOLOCK)
WHEREVendorNumber='20001'
)
BEGIN
SELECT VendorCreditDays,VRPNumber
FROMdbo.VendorReturnPolicy WITH (NOLOCK)
WHEREVRPNumber = 3279
END
/*==========================The4th part:Suggestions=====================*/
/*
1.使用ISNULL(Col, 0) 代替 CASE WHEN Col IS NULL THEN 0 ELSE Col END
*/
SELECTCASE
WHEN ColIS NULL THEN 0
ELSECol
END ASCol
FROMdbo.T1 WITH (NOLOCK)
SELECTISNULL(Col,0)
FROMdbo.T1 WITH (NOLOCK)
/*
2.通过合理的方法避免在 SELECT 语句中使用 DISTINCT
*/
SELECTDISTINCT
A.au_fname
,A.au_lname
FROMdbo.authors AS A WITH (NOLOCK)
INNERJOIN dbo.titleAuthor AS T WITH (NOLOCK)
ONT.au_id = A.au_id
--避免DISTINCT的写法
SELECTau_fname
,au_lname
FROMdbo.authors AS A WITH (NOLOCK)
WHEREEXISTS (
SELECTTOP 1 1
FROMdbo.titleAuthor AS T WITH (NOLOCK)
WHERET.au_id = A.au_id
)
/*
"CREATETABLE #(...)
INSERTINTO #"
*/
--Question:比较以下两种的差别
SELECT *
INTOtemp
FROMdbo.T1 WITH (NOLOCK)
INSERTINTO temp
SELECT *
FROMdbo.T1 WITH (NOLOCK)
/*
3.通过合理的方法来避免Like字句，在sql语句 WHERE 语句中使用 col LIKE '%a'
*/
/*
IFOBJECT_ID('dbo.Test_Like') IS NOT NULL
Executiontime:00:01:50
*/
SELECT*--45991
FROM[SCM].[dbo].[potran01] AS B WITH (NOLOCK)
WHEREB.purno LIKE '6%'
/*
Executiontime:00:01:05
*/
SELECT*--45991
FROM[SCM].[dbo].[potran01] AS B WITH (NOLOCK)
WHERELEFT(B.purno,1)='6'
SELECT*
FROMdbo.T1 WITH (NOLOCK)
WHEREname LIKE '%a'
--建议修改为：
SELECT*
FROMdbo.T1 WITH (NOLOCK)
WHERELEFT(RTRIM(name),1)='a'
/*
5.在条件中， 不要包含无意义的条件
*/
SELECT *
FROMdbo.T1 WITH (NOLOCK)
WHERE1=1
/*
6.删除整表数据时，建议使用 TRUNCATE TABLE
*/
DELETEFROM dbo.T1
--建议
TRUNCATETABLE dbo.T1
--Question:
/*
在什么情况下，只能使用DELETE而不能使用TRUNCATE呢？
*/
/*
--BYDBA3.ntext, text, and image 数据类型不再允许使用，请用nvarchar(max), varchar(max), andvarbinary(max),xml 代替。
*/
/*--BYDBA3.在CREAET Function的时候，建议显示申明表结构。
*/
--d2WHP01
USEInventory2005
GO
SELECTX.Item
FROMdbo.fn_CC_ItemLatestReceivingCostQuery ('07') as X
WHEREX.Item='22-136-012'
/*
/*
OBJECT: dbo.fn_CC_ItemLatestReceivingCostQuery
SERVER: D2whp01
DB: Inventory2005
DESCRIPTION:Query item's latest receiving cost
CREATOR: Wewe.J.Huang
CREATEDATE: 2008-11-19
SELECTItem, LatestReceivingCost
FROMdbo.fn_CC_ItemLatestReceivingCostQuery('02')
*/
CREATEFUNCTION dbo.fn_CC_ItemLatestReceivingCostQuery_ByCherish (@warehouseNumberCHAR(15))
RETURNS@result TABLE(Item char(25),LatestReceivingCost DECIMAL(9,2))
AS
BEGIN
INSERTINTO @result(Item,LatestReceivingCost)
SELECTItem
, CostAS LatestReceivingCost
FROMSCM.dbo.POReceivedDetail AS POReceiving WITH (NOLOCK)
WHEREEXISTS (SELECT LastPOReceiving.LatestTransactionNumber
FROM (SELECT Item
, MAX(TransactionNumber) AS LatestTransactionNumber
FROM SCM.dbo.POReceivedDetail WITH (NOLOCK)
WHERE WarehouseNumber = @warehouseNumber
AND Cost > 0
GROUP BY Item) AS LastPOReceiving
WHERE LastPOReceiving.LatestTransactionNumber =POReceiving.TransactionNumber)
RETURN
END
GO
SELECTX.Item
FROMdbo.fn_CC_ItemLatestReceivingCostQuery_ByCherish ('07') as X
WHEREX.Item='22-136-012'
dropfunction fn_CC_ItemLatestReceivingCostQuery_ByCherish
*/
----========================================
--IND2WHP02,SCM
SELECTdistinct e.ReferenceSonUmber,
f.VendorNumber
FROM dbo.ediInterchangeControlNumber a WITH (NOLOCK)
INNER JOIN dbo.eDigroUpControlNumber b WITH(NOLOCK)
ON a.InterchangeControlNumber =b.InterchangeControlNumber
INNER JOIN dbo.EditRanSactIonSetControlNumberc WITH (NOLOCK)
ON b.GroupControlNumber =c.GroupControlNumber
INNER JOIN DropShip.dbo.DropShipMaster d WITH(NOLOCK)
ON d.ReferenceSonUmber =dbo.Uf_edi_filteredifilenumer(a.[FileName])
INNER JOIN DropShip.dbo.DropShipTransaction eWITH (NOLOCK)
ON d.ReferenceSonUmber =e.ReferenceSonUmber
INNER JOINCodeCenter.dbo.DropShipWarehouseMap f WITH (NOLOCK)
ON e.WarehouseNumber =f.WarehouseNumber
WHERE (c.TransactionSetIdentIfierCode <> '850'
ORc.TransactionSetAcknowledgmentCode IS NULL
ORc.TransactionSetAcknowledgmentCode <> 'A')
AND d.Status <> 'V'
AND GETDATE() > DATEADD(HOUR, 12,d.sodate)
GROUP BY e.ReferenceSoNumber,f.VendorNumber
HAVING MAX(e.DownLoadDate) < @TOLERANCEDATE
AND MAX(e.DownLoadDate) >= @GIVEUPDATE
--修改为：
IFOBJECT_ID('tempdb.dbo.#temp','U') IS NOT NULL
DROP TABLE #temp
CREATETABLE #temp
(
ID INTIDENTITY(1,1)
,ReferenceSONumberINT
,VendorNumberCHAR(15)
,DownLoadDateDATETIME
,CONSTRAINTPK_#temp PRIMARY KEY
(
ID ASC
)
)
INSERTINTO #temp(ReferenceSONumber,VendorNumber,DownLoadDate)
SELECTe.ReferenceSonUmber
,f.VendorNumber
,e.DownLoadDate
FROMDropShip.dbo.DropShipTransaction e WITH (NOLOCK)
INNERJOIN CodeCenter.dbo.DropShipWarehouseMap f WITH (NOLOCK)
ON e.WarehouseNumber =f.WarehouseNumber
CREATENONCLUSTERED INDEX IX_#temp_ReferenceSonUmber ON #temp(ReferenceSonUmber ASC)
CREATENONCLUSTERED INDEX IX_#temp_DownLoadDate ON #temp(DownLoadDate ASC)
SELECTdistinct e.ReferenceSonUmber,
e.VendorNumber
FROM dbo.ediInterchangeControlNumber a WITH (NOLOCK)
INNER JOIN dbo.eDigroUpControlNumber b WITH(NOLOCK)
ON a.InterchangeControlNumber =b.InterchangeControlNumber
INNER JOIN dbo.EditRanSactIonSetControlNumberc WITH (NOLOCK)
ON b.GroupControlNumber =c.GroupControlNumber
INNER JOIN DropShip.dbo.DropShipMaster d WITH(NOLOCK)
ON d.ReferenceSonUmber =dbo.Uf_edi_filteredifilenumer(a.[FileName])
INNER JOIN #temp AS e
ON d.ReferenceSonUmber = e.ReferenceSonUmber
WHERE (c.TransactionSetIdentIfierCode <> '850'
OR c.TransactionSetAcknowledgmentCode IS NULL
OR c.TransactionSetAcknowledgmentCode <> 'A')
AND d.Status <> 'V'
AND GETDATE() > DATEADD(HOUR, 12,d.sodate)
GROUP BY e.ReferenceSoNumber,e.VendorNumber
HAVING MAX(e.DownLoadDate) < @TOLERANCEDATE
AND MAX(e.DownLoadDate) >= @GIVEUPDATE
--BYDBA1.请确认是否需要支持多国语言，否则采用UNICODE编码的数据类型。
USE Test
GO
--INS7DBM01
IFOBJECT_ID('dbo.TUnicoude') IS NOT NULL
DROPTABLE dbo.TUnicoude
createtable dbo.TUnicoude
(id intidentity(1,1)
,Name1varchar(10)
,Name2Nvarchar(10)
)
insertinto dbo.TUnicoude
select'dfsdsd',N'abcd'
unionall
select '测试用',N'测试用'
select *from dbo.TUnicoude with (nolock)
droptable dbo.TUnicoude
---------------------------------------------合并字符串
USE TEST
go
IFOBJECT_ID('tempdb.dbo.#test ') IS NOT NULL
DROPTABLE dbo.#test
CREATETABLE #test (id int
,value VARCHAR(100)
)
INSERTINTO #test
SELECT43,N'aa'
UNIONALL
SELECT43,N'bb'
UNIONALL
SELECT43,N'cc'
UNIONALL
SELECT66,N'kk'
UNIONALL
SELECT66,N'zz'
select *from #test
--方法一：
;WITH data
AS (
SELECT DISTINCT id FROM #test
)
SELECT id,
col2= STUFF(REPLACE(REPLACE(CAST((SELECT value
FROM #test
WHERE id=a.id
FOR XML PATH(''),TYPE ) ASNVARCHAR(max)),'</value>',''),'<value>',','),1,1,'')
FROMdata a
--方法二
DECLARE@groupid INT
,@CatalogVARCHAR(MAX)
DECLARE@T TABLE
(
groupidINT
,CatalogVARCHAR(MAX)
)
DECLAREcur_groupid CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECTDISTINCT id
FROM#test
OPENcur_groupid
FETCHNEXT FROM cur_groupid INTO @groupid
WHILE@@FETCH_STATUS=0
BEGIN
SET@Catalog=''
SELECT@Catalog=@Catalog+CAST(value AS VARCHAR(8))+','
FROM#test
WHEREID=@groupid
INSERTINTO @T
SELECT@groupid,@Catalog
FETCHNEXT FROM cur_groupid INTO @groupid
END
CLOSEcur_groupid
DEALLOCATEcur_groupid
SELECTgroupid,LEFT(Catalog,LEN(Catalog)-1) FROM @T
--------------------------------------分割字符串
USE TEST
go
IFOBJECT_ID('tempdb.dbo.#test ') IS NOT NULL
DROPTABLE dbo.#test
CREATETABLE #test (id int
,value VARCHAR(100)
)
INSERTINTO #test
SELECT32,N'a,b,c,dd,ff'
UNIONALL
SELECT23,N'a,kk,ll,dd,ff'
select *from #test
;WITHdata
AS (
SELECTid
,CAST(REPLACE('<value>'+value+'</value>',',','</value><value>')
AS XML)as c
FROM#test
)
SELECTa.id
,T.C.value('(.)[1]','VARCHAR(10)')
FROMdata as a
CROSSAPPLY C.nodes('./value') AS T(C)
------------------------
---================================================总结的一些专题================================================
------------------------------------游标专题----------------------------------------------
/*
14.定义游标时，如果不是特别需要，使用LOCAL关键显式的将游标定义为局部游标，
尽量避免使用全局（GLOBAL，这是数据库的默认行为
）游标；
没有特殊需要的话，尽量使用FORWARD_ONLY READONLY 游标,同时尽量使用静态游标 STATIC；
避免大量数据的处理避免使用游标"
*/
DECLAREcur_MyCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECTTOP 10 QUOTENAME(SCHEMA_NAME(schema_id),'[]')+'.'+QUOTENAME(name,'[]')
FROMsys.tables WITH (NOLOCK)
/*---------------BYDBA3.尽量不使用游标,请使用其他的方法。----------------
1.拼接字符串
2.update/delete/insert记录
*/
DECLARE@Table_name SYSNAME
,@stringNVARCHAR(2000)
DECLAREcur_MyCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECTTOP 10 QUOTENAME(SCHEMA_NAME(schema_id),'[]')+'.'+QUOTENAME(name,'[]')
FROMsys.tables WITH (NOLOCK)
OPENcur_MyCursor
FETCHNEXT FROM cur_MyCursor INTO @Table_name
WHILE@@FETCH_STATUS=0
BEGIN
SET@string=ISNULL(@string,'')+@Table_name+','
FETCH NEXTFROM cur_MyCursor INTO @Table_name
END
CLOSEcur_MyCursor
DEALLOCATEcur_MyCursor
PRINT@string
--是用SELECT实现。
SET@string=''
SELECT@string=ISNULL(@string,'')+QUOTENAME(SCHEMA_NAME(A.schema_id),'[]')
+'.'+QUOTENAME(A.name,'[]')
+','
FROM
(
SELECT TOP10 schema_id,name
FROMsys.tables WITH (NOLOCK)
)AS A
PRINT@string
------------------------------------数据缓存专题----------------------------------------------
/*
15.缓存临时数据时，使用真正的临时表（#或者##开头）的表，避免将临时表缓存到正式表中
*/
--错误的做法
SETNOCOUNT ON
DECLARE@string NVARCHAR(2000)
SET@string=''
SELECTTOP 10 QUOTENAME(SCHEMA_NAME(schema_id),'[]')+'.'+QUOTENAME(name,'[]')+',' ASName
INTOdbo.TableName--正式表用于缓存表名。
FROMsys.tables WITH (NOLOCK)
SELECT@string=ISNULL(@string,'')+Name
FROMdbo.TableName WITH (NOLOCK)
PRINT@string
DROPTABLE dbo.TableName
----正确的做法1.
SETNOCOUNT ON
DECLARE@string NVARCHAR(2000)
SET@string=''
IFOBJECT_ID('tempdb.dbo.#temp','U') IS NOT NULL
BEGIN
DROPTABLE #temp
END
SELECTTOP 10 QUOTENAME(SCHEMA_NAME(schema_id),'[]')+'.'+QUOTENAME(name,'[]')+',' ASName
INTO#temp--临时表缓存数据
FROMsys.tables WITH (NOLOCK)
SELECT@string=ISNULL(@string,'')+Name
FROM#temp
PRINT@string
IFOBJECT_ID('tempdb.dbo.#temp','U') IS NOT NULL
BEGIN
DROPTABLE #temp
END
----正确的做法2.
SETNOCOUNT ON
DECLARE@string NVARCHAR(2000)
SET@string=''
IFOBJECT_ID('tempdb.dbo.##temp','U') IS NOT NULL
BEGIN
DROPTABLE ##temp
END
SELECTTOP 10 QUOTENAME(SCHEMA_NAME(schema_id),'[]')+'.'+QUOTENAME(name,'[]')+',' ASName
INTO##temp--临时表缓存数据
FROMsys.tables WITH (NOLOCK)
SELECT@string=ISNULL(@string,'')+Name
FROM##temp
PRINT@string
IFOBJECT_ID('tempdb.dbo.##temp','U') IS NOT NULL
BEGIN
DROPTABLE ##temp
END
----正确的做法3.
SETNOCOUNT ON
DECLARE@string NVARCHAR(2000)
SET@string=''
DECLARE@Name TABLE
(
Id INTIDENTITY(1,1)
,TableNameSYSNAME
)
--表变量缓存数据
INSERTINTO @Name(TableName)
SELECTTOP 10 QUOTENAME(SCHEMA_NAME(schema_id),'[]')+'.'+QUOTENAME(name,'[]')+','
FROMsys.tables WITH (NOLOCK)
SELECT@string=ISNULL(@string,'')+TableName
FROM@Name
PRINT@string
--在此处讲下临时表与表变量、局部表变量和全局表变量的区别
/*
临时表与表变量
相同点：用于SQL语句中临时的缓存数据
不同点：1.临时表数据存储在物理的磁盘上(数据表存放在Tempdb中)，表变量存储在内存中；
2.由于1，所以缓存少量数据时，表变量效率比临时表高；
3.表变量无法创建INDEX，无法修改结构，无法跨作用域，但是临时表刚好相反；
4.由于3，所以缓存大量数据时，使用临时表加INDEX效率表变量高；
5.表变量不受事务的影响，临时表会受事务的影响。
局部临时表和全局临时表
它们在名称、可见性以及可用性上有区别。
本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。
全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。
*/
------------------------------------动态SQL专题----------------------------------------------
/*
16.动态T-SQL处理语句中，如果涉及到变量，尽量使用sp_executesql，通过参数传递进行处理，
避免使用EXEC硬拼SQL语句存储过程或函数中不允许包含未使用的参数或变量
*/
----------------------------------------------Demo1(使用两种方式执行动态SQL)
DECLARE@sql NVARCHAR(MAX)
,@TableNameSYSNAME
,@topINT
SET@TableName=N'Test_Select'
SET@top=2
SET@sql=N'SELECT TOP '+CAST(@top AS NVARCHAR(4))+N' *
FROMsys.tables WITH (NOLOCK)
WHEREname='''+@TableName+''''
EXEC(@sql)
--推荐写法
SET@sql=N'SELECT TOP (@top) *
FROMsys.tables WITH (NOLOCK)
WHEREname=@TableName'
EXECsp_executesql @sql
,N'@TableNameSYSNAME
,@topINT'
,@TableName
,3
----------------------------------------------demo2(将变量放到动态SQL执行串中)
declare@sql nvarchar(max)
,@type sysname
,@crdate datetime
set@type=N'u'
set@crdate=N'2004-10-12 17:37:17.437'
SET@sql=N'
selecttop 5 *
fromsys.sysobjects with (Nolock)
WHERE1=1'
IF @typeIS NOT NULL AND @type<>''
BEGIN
SET@sql=@sql+' AND type='''+@type+''''
END
IF@crdate IS NOT NULL
BEGIN
SET@sql=@sql+' AND crdate>='''+CONVERT(CHAR(20),@crdate,120)+''''
END
execsp_executesql @sql
SET@sql=N'
selecttop 5 *
fromsys.sysobjects with (Nolock)
WHERE1=1'
IF @typeIS NOT NULL AND @type<>''
BEGIN
SET @sql=@sql+' AND type=@type'
END
IF@crdate IS NOT NULL
BEGIN
SET @sql=@sql+' AND crdate>=@crdate'
END
print@sql
execsp_executesql @sql
,N'@type sysname
,@crdatedatetime'
,@type
,@crdate
selecttop 5 *
fromsys.sysobjects with (Nolock)
wherecrdate>='2004-10-12 17:37:17.437' and type='U'
---------------------Demo3:动态SQL中的like子句中
use test
go
IFOBJECT_ID('dbo.TestLike','U') IS NOT NULL
DROPTABLE dbo.TestLike
CREATETABLE dbo.TestLike
(ID INTIDENTITY(1,1)
,NameVARCHAR(10)
)
DECLARE@i INT
SET @i=0
WHILE@i<1000
BEGIN
insertinto dbo.TestLike
SELECTCAST(@i+@i AS VARCHAR(10))
SET@i=@i+1
END
-----以下是测试代码
DECLARE@sql NVARCHAR(max)
,@LikeNVARCHAR(10)
SET@Like=N'76'
--以前采用拼串的处理方式(会导致SQL重编译执行串，影响性能)
SET@sql=N'SELECT * FROM dbo.TestLike WITH (NOLOCK)
WHEREName like ''%'+@Like+'%'''
EXECsp_executesql @sql
--新的处理方式，把变量放到执行串中。(避免SQL重编译执行串)
SET@sql=N'SELECT * FROM dbo.TestLike WITH (NOLOCK)
WHEREName like @Like'
SET@Like=N'%'+@Like+'%'
EXECsp_executesql @sql
,N'@LikeNVARCHAR(10)'
,@Like
----------------比较EXEC和sp_executesql的区别
------sp_executesql输出参数
declare@sql nvarchar(max)
,@type sysname
,@countOUTINT
SET@sql='SELECT @countOUT=COUNT(1)
FROMsys.objects WITH (NOLOCK)
WHEREtype=@type'
EXECsp_executesql @sql
,N'@typesysname
,@countOUTINT OUTPUT'
,'U'
,@countOUTOUTPUT
select@countOUT
------exec和sp_executesql安全性
--DBCCFREEPROCCACHE
declare@sql nvarchar(max)
,@type sysname
SET@sql=N'
selecttop 5 *
fromsys.sysobjects with (Nolock)
WHERE1=1'
--set@type=N'u'
SET@type=N'u'''+' OR 1=1;EXEC master.dbo.xp_create_subdir ''C:\temp''--'----不安全的代码
EXEC(@sql+'AND type='''+@type+'''')
--print@sql+' AND type='''+@type+''''
SET@type=N'U'
SET@sql=N'
selecttop 5 *
fromsys.sysobjects with (Nolock)
WHEREtype=@type'
execsp_executesql @sql
,N'@type sysname'
,@type = @type
SELECTA.bucketid
,A.refcounts
,A.usecounts
,A.size_in_bytes
,A.cacheobjtype
,B.text
FROMsys.dm_exec_cached_plans AS A
CROSSAPPLY sys.dm_exec_sql_text(A.plan_handle) AS B
WHEREA.cacheobjtype='Compiled Plan'
------------------------------------关于Distinct专题----------------------------------------------
--Distinct与Group BY、UNION、UNIONALL
USE Test
GO
/*
CREATETABLE dbo.Test_Author
(
ID INTIDENTITY(1,1)NOT NULL
,AuthorVARCHAR(5) NULL
)
INSERTINTO dbo.Test_Author(Author)
SELECT'John' UNION ALL
SELECT'Jim' UNION ALL
SELECT'Mark' UNION ALL
SELECT'Lucy' UNION ALL
SELECT'Mary'
GO
CREATETABLE dbo.Test_Artiles
(
ID INTIDENTITY(1,1)NOT NULL
,AuthorIDINT
,ArtilesNameVARCHAR(50) NULL
)
INSERTINTO dbo.Test_Artiles(AuthorID,ArtilesName)
SELECT1,'The world is falt' UNION ALL
SELECT2,'Alice''s Adventures in Wonderland'UNION ALL
SELECT2,'Animal Farm'UNION ALL
SELECT3,'Brave New World'UNION ALL
SELECT4,'Treasure Island'UNION ALL
SELECT3,'The Adventures of Tom Sawyer'
*/
--找出已经出版过书的作者
--普遍的写法一
SELECTDISTINCT B.Author
FROMdbo.Test_Artiles AS A WITH (NOLOCK)
INNERJOIN dbo.Test_Author AS B WITH (NOLOCK)
ONA.AuthorID=B.ID
--普遍的写法二
SELECTB.Author
FROMdbo.Test_Artiles AS A WITH (NOLOCK)
INNERJOIN dbo.Test_Author AS B WITH (NOLOCK)
ONA.AuthorID=B.ID
GROUP BYB.Author
--普遍的写法三
SELECTB.Author
FROMdbo.Test_Author AS B WITH (NOLOCK)
WHEREB.ID IN(
SELECTDISTINCT AuthorID
FROMdbo.Test_Artiles AS A WITH (NOLOCK)
)
--不关心数据是否重复的查询请不要使用Distinct
SELECTB.Author
FROMdbo.Test_Artiles AS A WITH (NOLOCK)
INNERJOIN dbo.Test_Author AS B WITH (NOLOCK)
ONA.AuthorID=B.ID
--最优正确的写法
SELECTB.Author
FROMdbo.Test_Author AS B WITH (NOLOCK)
WHEREB.ID IN(
SELECT AuthorID
FROMdbo.Test_Artiles AS A WITH (NOLOCK)
)
--或者是
SELECTB.Author
FROMdbo.Test_Author AS B WITH (NOLOCK)
WHEREEXISTS (
SELECT TOP 1 1
FROMdbo.Test_Artiles AS A WITH (NOLOCK)
WHEREA.AuthorID = B.id
)
--请不要将Distinct与UNION或者UNION ALL联合使用
--错误的写法一
SELECTDISTINCT AuthorID,Author
FROM
(
SELECT 1AS AuthorID,'Jim' AS Author
UNIONALL
SELECT1,'Jim'
) AS A
--错误的写法二
SELECTDISTINCT AuthorID,Author
FROM
(
SELECT 1AS AuthorID,'Jim' AS Author
UNION
SELECT1,'Jim'
) AS A
-- 正确的写法
SELECTAuthorID,Author
FROM
(
SELECT 1AS AuthorID,'Jim' AS Author
UNION
SELECT1,'Jim'
) AS A
--在没有聚合函数的情况下，请优先使用Distinct而不是Group By
--优先使用
SELECTDISTINCT B.Author
FROMdbo.Test_Artiles AS A WITH (NOLOCK)
INNERJOIN dbo.Test_Author AS B WITH (NOLOCK)
ONA.AuthorID=B.ID
--不推荐
SELECTB.Author
FROMdbo.Test_Artiles AS A WITH (NOLOCK)
INNERJOIN dbo.Test_Author AS B WITH (NOLOCK)
ONA.AuthorID=B.ID
GROUP BYB.Author
--在含有聚合函数的情况下，请使用Group BY而不是Distinct
SELECTB.Author,COUNT(AuthorID) AS ArtilesCount
FROMdbo.Test_Artiles AS A WITH (NOLOCK)
INNERJOIN dbo.Test_Author AS B WITH (NOLOCK)
ONA.AuthorID=B.ID
GROUP BYB.Author
ORDER BYArtilesCount DESC
-----------==============================XML专题 ========================================
--BYDBA1.涉及到xml处理时，使用sql server 2005 关于xml的处理方式
DECLARE@idoc INT
DECLARE@doc XML
SET @doc=N'
<ROOT>
<CustomerCustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET"EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11"Quantity="12"/>
</Order>
</Customer>
<CustomerCustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS"EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72"Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--SQLServer2000对XML的处理方式.
--Createan internal representation of the XML document.
EXECSP_XML_PREPAREDOCUMENT @idoc OUTPUT, @doc
--SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID INT '../@OrderID',
CustomerID VARCHAR(10)'../@CustomerID',
OrderDate DATETIME '../@OrderDate',
ProdID INT '@ProductID',
Qty INT '@Quantity')
EXECSP_XML_REMOVEDOCUMENT @idoc
--推荐做法:
SELECTT.c.value('(./Order/@OrderID)[1]','INT')AS OrderID
,T.c.value('(./@CustomerID)[1]','VARCHAR(10)')AS CustomerID
,T.c.value('(./Order/@OrderDate)[1]','DATETIME')AS OrderDate
,T.c.value('(./Order/OrderDetail/@ProductID)[1]','INT')AS ProdID
,T.c.value('(./Order/OrderDetail/@Quantity)[1]','INT')AS Qty
FROM@doc.nodes('/ROOT/Customer') T(c)
-----------======================================================================
--BYDBA1.XML数据类型必须是和UNICODE类型的数据相互转换。
--INS7DBM01
DECLARE
@x1 xml,
@s1 varchar(max)
SET @x1= CONVERT(xml,N'<root>测试</root>')
SET @s1= CONVERT(varchar(max),@x1)
GO
--而下面的代码,则不会出现错误:
DECLARE
@x1 nvarchar(max),
@s1 varchar(max)
SET @x1= N'<root>测试</root>'
SET @s1= CONVERT(varchar(max),@x1)
GO
DECLARE
@x1 xml,
@s1 nvarchar(max) --这里定义为nvarchar
SET @x1= CONVERT(xml,N'<root>测试</root>')
SET @s1= CONVERT(nvarchar(max),@x1)
GO
-----------=======================================================================
--获取XML节点文本值时，请使用text()方法
DECLARE@xml xml
SET @xml= N'<b>text1
<c>text2
<d>text3
<e>text4</e>
</d>
<f>text5</f>
</c>
</b>'
SELECT@xml.value(N'(/b/c)[1]',N'varchar(50)') AS xml_col_val_1
SELECT@xml.value(N'(/b/c/text())[1]',N'varchar(50)') AS xml_col_val_2
/**特别提醒:
在解析XML时，我们现在要求加text() 函数，该函数会将''解析为NULL，
而不加将会解析为'',这里需要特别注意一下，并考虑是否处理为NULL的问题
*/
DECLARE@xml xml
SET @xml= N'<b>text1
<c></c>
</b>'
SELECT@xml.value(N'(/b/c)[1]',N'varchar(50)') AS xml_col_val_1
SELECT@xml.value(N'(/b/c/text())[1]',N'varchar(50)') AS xml_col_val_2
-----------======================================================================
--BYDBA3.解析XML来进行变量赋值,请不要使用到nodes方法
declare@Message xml
,@NameSpaceINT
,@GuidIDINT
,@TagINT
,@ActionINT
set@Message=N'
<Publish>
<Node>
<MessageHead>
<Namespace>1</Namespace>
<OriginalGUID>11</OriginalGUID>
</MessageHead>
<MessageHead>
<Namespace>2</Namespace>
<OriginalGUID>22</OriginalGUID>
</MessageHead>
</Node>
</Publish>
'
--写法一
SELECTTOP 1
@NameSpace=T.c.value('(Namespace)[1]','INT')
,@GuidID=T.c.value('(OriginalGUID)[1]', 'INT')
FROM@Message.nodes('/Publish/Node/MessageHead') T (c)
select@NameSpace,@GuidID
--写法二
DECLARE@T TABLE
(NamespaceINT
,OriginalGUIDINT
)
INSERTINTO @T
SELECTT.c.value('(Namespace/text())[1]', 'INT')
,T.c.value('(OriginalGUID/text())[1]','INT')
FROM@Message.nodes('/Publish/Node/MessageHead') T (c)
SELECTTOP 1
@NameSpace= Namespace
,@GuidID=OriginalGUID
FROM@T
select@NameSpace,@GuidID
--写法三(最优的写法)
--BYDBA 修改后的写法。两点不同：1.不需要去解析nodes 2.加上了text()函数。
SELECT
@NameSpace=@Message.value('(/Publish/Node/MessageHead/Namespace/text())[1]','INT')
,@GuidID=@Message.value('(/Publish/Node/MessageHead/OriginalGUID/text())[1]', 'INT')
select@NameSpace,@GuidID
-----------======================================================================
--BYDBA1.多次重复定义相同的XML namespace
DECLARE@XML xml
SET@XML=N'
<Publishxmlns="http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService">
<Node>
<UserData>TestMsg</UserData>
<SessionID>0xABde12345</SessionID>
<OrderNumber>102365</OrderNumber>
<CustomerNumber>236598</CustomerNumber>
</Node>
</Publish>'
DECLARE@SessionID CHAR(15)
,@OrderNumberINT
,@CustomerNumberINT
SELECT
@SessionID=@XML.value( 'declarenamespace
NEM=''http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService'';
(/NEM:Publish/NEM:Node/NEM:SessionID/text())[1]','CHAR(15)'),
--SessionID
@OrderNumber=@XML.value( 'declarenamespace
NEM=''http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService'';
(/NEM:Publish/NEM:Node/NEM:OrderNumber/text())[1]','int'),
@CustomerNumber=@XML.value('declare namespace
NEM=''http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService'';
(/NEM:Publish/NEM:Node/NEM:CustomerNumber/text())[1]','int')
SELECT@SessionID,@OrderNumber,@CustomerNumber
--BYDBA 修改后的写法。
;WITHXMLNAMESPACES (DEFAULT'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECT@SessionID=@XML.value('(/Publish/Node/SessionID/text())[1]','CHAR(15)')
,@OrderNumber=@XML.value('(/Publish/Node/OrderNumber/text())[1]','int')
,@CustomerNumber=@XML.value('(/Publish/Node/CustomerNumber/text())[1]','int')
SELECT@SessionID,@OrderNumber,@CustomerNumber
-----------======================================================================
--BYDBA3.请指定XML的绝对路径，将'//'修改为'/'
DECLARE@XML xml
SET@XML=N'
<Publishxmlns="http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService">
<UserData>
<Node>
<UserData>TestMsg</UserData>
<SessionID>0xABde12345</SessionID>
<OrderNumber>102365</OrderNumber>
<CustomerNumber>236598</CustomerNumber>
</Node>
</UserData>
</Publish>'
DECLARE@SessionID CHAR(15)
,@OrderNumberINT
,@CustomerNumberINT
--使用namspace加相对路径
;WITHXMLNAMESPACES (DEFAULT'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECT@SessionID=@XML.value('(//SessionID/text())[1]','CHAR(15)')
,@OrderNumber=@XML.value('(//OrderNumber/text())[1]','int')
,@CustomerNumber=@XML.value('(//CustomerNumber/text())[1]','int')
SELECT@SessionID,@OrderNumber,@CustomerNumber;
--解析所有namespace下的节点
SELECT@SessionID=@XML.value('(/*:Publish/*:UserData/*:Node/*:SessionID/text())[1]','CHAR(15)')
,@OrderNumber=@XML.value('(/*:Publish/*:UserData/*:Node/*:OrderNumber/text())[1]','int')
,@CustomerNumber=@XML.value('(/*:Publish/*:UserData/*:Node/*:CustomerNumber/text())[1]','int')
SELECT@SessionID,@OrderNumber,@CustomerNumber
--BYDBA 修改后的写法。
;WITHXMLNAMESPACES (DEFAULT'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECT@SessionID=@XML.value('(/Publish/UserData/Node/SessionID/text())[1]','CHAR(15)')
,@OrderNumber=@XML.value('(/Publish/UserData/Node/OrderNumber/text())[1]','int')
,@CustomerNumber=@XML.value('(/Publish/UserData/Node/CustomerNumber/text())[1]','int')
SELECT@SessionID,@OrderNumber,@CustomerNumber
---------------------
DECLARE@XML xml
SET@XML=N'
<Publishxmlns="http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService">
<UserDataxmlns="">
<Node>
<UserData>TestMsg</UserData>
<SessionID>0xABde12345</SessionID>
<OrderNumber>102365</OrderNumber>
<CustomerNumber>236598</CustomerNumber>
</Node>
</UserData>
</Publish>'
DECLARE@SessionID CHAR(15)
,@OrderNumberINT
,@CustomerNumberINT
--使用namspace加相对路径
--;WITHXMLNAMESPACES (DEFAULT'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECT@SessionID=@XML.value('(//SessionID/text())[1]','CHAR(15)')
,@OrderNumber=@XML.value('(//OrderNumber/text())[1]','int')
,@CustomerNumber=@XML.value('(//CustomerNumber/text())[1]','int')
SELECT@SessionID,@OrderNumber,@CustomerNumber
--推荐的写法
SELECT@SessionID=@XML.value('(/*/UserData/Node/SessionID/text())[1]','CHAR(15)')
,@OrderNumber=@XML.value('(/*/UserData/Node//OrderNumber/text())[1]','int')
,@CustomerNumber=@XML.value('(/*/UserData/Node//CustomerNumber/text())[1]','int')
SELECT@SessionID,@OrderNumber,@CustomerNumber
-----------======================================================================
--使用exist()与value()时对性能的影响
DECLARE@XML xml
SET@XML=N'
<Publishxmlns="http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService">
<UserData>
<Node>
<UserData>TestMsg</UserData>
<SessionID>0xABde12345</SessionID>
<OrderNumber>102365</OrderNumber>
<CustomerNumber>236598</CustomerNumber>
</Node>
</UserData>
</Publish>'
--使用exist
;WITHXMLNAMESPACES(DEFAULT'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECT@XML.value('(/Publish/UserData/Node/UserData/text())[1]','VARCHAR(10)')
,@XML.value('(/Publish/UserData/Node/SessionID/text())[1]','VARCHAR(10)')
,@XML.value('(/Publish/UserData/Node/OrderNumber/text())[1]','INT')
,@XML.value('(/Publish/UserData/Node/CustomerNumber/text())[1]','INT')
WHERE@XML.exist('/Publish/UserData/Node/OrderNumber[text()="102365"]')=1
--使用普通方法(value)
;WITHXMLNAMESPACES(DEFAULT 'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECT@XML.value('(/Publish/UserData/Node/UserData/text())[1]','VARCHAR(10)')
,@XML.value('(/Publish/UserData/Node/SessionID/text())[1]','VARCHAR(10)')
,@XML.value('(/Publish/UserData/Node/OrderNumber/text())[1]','INT')
,@XML.value('(/Publish/UserData/Node/CustomerNumber/text())[1]','INT')
WHERE@XML.value('(/Publish/UserData/Node/OrderNumber/text())[1]','INT')=102365
-----------======================================================================
--使用local-name()对性能产生的影响
DECLARE@XML xml
SET@XML=N'
<Publishxmlns="http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService">
<UserData>
<Node>
<UserData>TestMsg</UserData>
<SessionID>0xABde12345</SessionID>
<OrderNumber>102365</OrderNumber>
<CustomerNumber>236598</CustomerNumber>
</Node>
</UserData>
</Publish>'
--解析任何命名空间对应节点值
SELECT@XML.value('(/*[local-name()="Publish"]/*[local-name()="UserData"]/*[local-name()="Node"]/*[local-name()
="UserData"]/text())[1]','VARCHAR(10)')
,@XML.value('(/*[local-name()="Publish"]/*[local-name()="UserData"]/*[local-name()="Node"]/*[local-name()
="SessionID"])[1]','VARCHAR(10)')
,@XML.value('(/*[local-name()="Publish"]/*[local-name()="UserData"]/*[local-name()="Node"]/*[local-name()
="OrderNumber"])[1]','INT')
,@XML.value('(/*[local-name()="Publish"]/*[local-name()="UserData"]/*[local-name()="Node"]/*[local-name()
="CustomerNumber"])[1]','INT')
--解析特定的命名空间
;WITHXMLNAMESPACES(DEFAULT 'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECT@XML.value('(/Publish/UserData/Node/UserData/text())[1]','VARCHAR(10)')
,@XML.value('(/Publish/UserData/Node/SessionID/text())[1]','VARCHAR(10)')
,@XML.value('(/Publish/UserData/Node/OrderNumber/text())[1]','INT')
,@XML.value('(/Publish/UserData/Node/CustomerNumber/text())[1]','INT')
-----------======================================================================
--修改XML的节点
DECLARE@POData xml
,@InvoiceNumber int
set@InvoiceNumber=45386473
--下面这段代码是把原来的XML里面的VendorInvoiceNumber修改为：<VendorInvoiceNumberxmlns="">45386473</VendorInvoiceNumber>
SET@POData=N'<Publish xmlns="http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService">
<Subject>POASNInternalPOInfo</Subject>
<FromService>http://soa.Test.com/SOA/USA/InfrastructureService/V10/NLB/PubSubService</FromService>
<ToService>http://soa.Test.com/SOA/USA/POASNManagement/V10/OVS02/POASNInternalPOInfoService</ToService>
<MessageType>POV10</MessageType>
<Node>
<POV10>
<Body>
<PO>
<VendorInvoiceNumber />
</PO>
</Body>
</POV10>
</Node>
</Publish>'
--先删除
SET@POData.modify('
delete/*:Publish/*:Node/*:POV10/*:Body/*:PO/*:VendorInvoiceNumber
')
--NameSpace错误的添加
--SET@POData.modify('
-- insert<VendorInvoiceNumber>{sql:variable("@InvoiceNumber")}</VendorInvoiceNumber>
-- into (/*:Publish/*:Node/*:POV10/*:Body/*:PO)[1]
--')
--
--select@POData
--正确的添加
SET@POData.modify('declare default element namespace
"http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService";
insert<VendorInvoiceNumber>{sql:variable("@InvoiceNumber")}</VendorInvoiceNumber>
into(/Publish/Node/POV10/Body/PO)[1]
')
select@POData
-----------======================================================================
--XML操作用于合并字符串
USE TEST
go
IFOBJECT_ID('tempdb.dbo.#test ') IS NOT NULL
DROPTABLE dbo.#test
CREATETABLE #test (id int
,value VARCHAR(100)
)
INSERTINTO #test
SELECT43,N'aa'
UNIONALL
SELECT43,N'bb'
UNIONALL
SELECT43,N'cc'
UNIONALL
SELECT66,N'kk'
UNIONALL
SELECT66,N'zz'
select *from #test
--方法一：
;WITH data
AS (
SELECT DISTINCT id FROM #test
)
SELECT id,
col2= STUFF(REPLACE(REPLACE(CAST((SELECT value
FROM #test
WHERE id=a.id
FOR XML PATH(''),TYPE ) ASNVARCHAR(max)),'</value>',''),'<value>',','),1,1,'')
FROMdata a
--方法二
DECLARE@groupid INT
,@CatalogVARCHAR(MAX)
DECLARE@T TABLE
(
groupidINT
,CatalogVARCHAR(MAX)
)
DECLAREcur_groupid CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECTDISTINCT id
FROM#test
OPENcur_groupid
FETCHNEXT FROM cur_groupid INTO @groupid
WHILE@@FETCH_STATUS=0
BEGIN
SET@Catalog=''
SELECT@Catalog=@Catalog+CAST(value AS VARCHAR(8))+','
FROM#test
WHEREID=@groupid
INSERTINTO @T
SELECT@groupid,@Catalog
FETCHNEXT FROM cur_groupid INTO @groupid
END
CLOSEcur_groupid
DEALLOCATEcur_groupid
SELECTgroupid,LEFT(Catalog,LEN(Catalog)-1) FROM @T
-----XML操作用于分割字符串
USE TEST
go
IFOBJECT_ID('tempdb.dbo.#test ') IS NOT NULL
DROPTABLE dbo.#test
CREATETABLE #test (id int
,value VARCHAR(100)
)
INSERTINTO #test
SELECT32,N'a,b,c,dd,ff'
UNIONALL
SELECT23,N'a,kk,ll,dd,ff'
select *from #test
;WITHdata
AS (
SELECTid
,CAST(REPLACE('<value>'+value+'</value>',',','</value><value>')
AS XML)as c
FROM#test
)
SELECTa.id
,T.C.value('(.)[1]','VARCHAR(10)')
FROMdata as a
CROSSAPPLY C.nodes('./value') AS T(C)
-----------======================================================================
--XML解析中文为乱码的问题
--INS7DBM01
DECLARE@xml xml
SET @xml=
--BYDBA1.XML变量赋值是，必须是加N
N'
<Publishxmlns="http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService">
<Node>
<ComReasonTreeMessage>
<Body>
<ReasonDescription>测试XML解析中文为乱码的问题</ReasonDescription>
</Body>
</ComReasonTreeMessage>
</Node>
</Publish>'
DECLARE@ReasonDescription1 VARCHAR(100)
;WITHXMLNAMESPACES (DEFAULT'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECT@ReasonDescription1=@xml.value('(Publish/Node/ComReasonTreeMessage/Body/ReasonDescription/text())
[1]','VARCHAR(100)')
DECLARE@ReasonDescription NVARCHAR(100)--BYDBA 1.此处必须定义为UNICODE编码的数据类型
;WITHXMLNAMESPACES (DEFAULT 'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECT@ReasonDescription=@xml.value('(Publish/Node/ComReasonTreeMessage/Body/ReasonDescription/text())
[1]','NVARCHAR(100)')--BYDBA1.此处必须为UNICODE编码的数据类型
select@ReasonDescription1,@ReasonDescription
---统计XML节点个数
DECLARE@xml xml
SET@xml=N'
<Publishxmlns="http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService">
<Node>
<SubNode>Test1</SubNode>
</Node>
<Node>
<SubNode>Test2</SubNode>
</Node>
<Node>
<SubNode>Test3</SubNode>
</Node>
</Publish>
'
--不推荐的写法
DECLARE@T TABLE
(ID INTIDENTITY(1,1)
,NodeXML
)
;WITHXMLNAMESPACES (DEFAULT'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
INSERTINTO @T(Node)
SELECT T.C.query('.')
FROM@xml.nodes('/Publish/Node') AS T(C)
selectCOUNT(*)
from @T
WHERENode IS NOT NULL
--不推荐的做法
;WITHXMLNAMESPACES (DEFAULT'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECTCOUNT(*)
FROM (
SELECT T.C.query('.') AS Node
FROM@xml.nodes('/Publish/Node') AS T(C)
) AS A
--推荐的写法
;WITHXMLNAMESPACES (DEFAULT'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECT@xml.value('count(/Publish/Node)[1]','INT')
---------------------------------------===========================构造XML
DECLARE@xml xml
,@mess NVARCHAR(max)
Ifobject_id('tempdb.dbo.#','u') is not null
droptable #
createtable #
(
id intidentity(1,1)
,itemvarchar(10)
,WarehouseCHAR(10)
,Qty int
)
INSERTINTO #
SELECT'100-02-001','01',1
unionall
select'102-02-002','02',2
unionall
select'103-02-003','03',3
unionall
select'103-02-003','03',4
unionall
select'103-02-003','03',5
unionall
select'104-02-004','04',4
-------------------------不推荐的写法
Declare@Node NVARCHAR(max)
,@bodyNVARCHAR(max)
SET@Node =CAST((SELECT 'pdateInventory' AS "Action"
,NULL AS "Comment"
,'Newegg.EC.USA.InventoryManagement.Deduct.V10' AS "Namespace"
,'Inventory,EDI' AS "Tag"
,'NESO' AS "Sender"
,'EN' AS "Languag"
,'1003' AS "CompanyCode"
,'1.0' AS "Version"
FOR XMLPATH('MessageHead'),TYPE ) AS NVARCHAR(max))
SET@body = CAST( (SELECT TOP 3 a.item AS "@ItemNumber"
,a.WarehouseAS "@WarehouseNumber"
,a.QtyAS "@Quantity"
FROM # AS A
FORXML
PATH('ItemInfo'),ROOT('Items'),TYPE
)
ASNVARCHAR(max))
SET@mess = N'
<Publishxmlns="http://soa.Test.com/SOA/USA/InfrastructureService/V10/EcommercePubSubService">
<Subject>SSL11InventoryDeduction</Subject>
<FromService>http://soa.Test.com/SOA/USA/InfrastructureService/V10/Ecommerce/PubSubService</FromService>
<ToService>http://soa.Test.com/SOA/USA/OrderManagement/V10/SSL11/InventoryDeductionSSLService</ToService>
<Node>'+ @Node +
'<Body>
<InventoryDeductionInfo>'+ @body +
'</InventoryDeductionInfo>
</Body>
</Node>
</Publish>'
SET @xml= CAST(@mess as xml)
SELECT@xml
-------------------------建议的写法
;WITHXMLNAMESPACES(DEFAULT'http://soa.Test.com/SOA/USA/InfrastructureService/V10/EcommercePubSubService')
SELECT
'SSL11InventoryDeduction' AS "Subject"
,'http://soa.Test.com/SOA/USA/InfrastructureService/V10/Ecommerce/PubSubService'AS "FromService"
,'http://soa.Test.com/SOA/USA/OrderManagement/V10/SSL11/InventoryDeductionSSLService'AS "ToService"
,(SELECT (SELECT 'pdateInventory' AS "Action"
,NULL AS "Comment"
,'Newegg.EC.USA.InventoryManagement.Deduct.V10' AS "Namespace"
,'Inventory,EDI' AS "Tag"
,'NESO' AS "Sender"
,'EN' AS "Languag"
,'1003' AS "CompanyCode"
,'1.0' AS "Version"
FOR XML PATH('MessageHead'),TYPE
)
,(SELECT(
SELECT TOP 3 a.item AS "@ItemNumber"
,a.WarehouseAS "@WarehouseNumber"
,a.QtyAS "@Quantity"
FROM # AS A
FORXML PATH('ItemInfo'),ROOT('Items'),TYPE
)
FORXML PATH('InventoryDeductionInfo'),ROOT('Body'),TYPE
)
FOR XMLPATH('Node'),TYPE
)
FOR XMLPATH ('Publish'),TYPE
--------------------------循环处理XML的性能问题
declare@Message xml
set@Message=N'
<Publishxmlns="http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService">
<Node>
<MessageHead>
<Sequence>1000</Sequence>
<OriginalGUID>10000</OriginalGUID>
</MessageHead>
<MessageHead>
<Sequence>1001</Sequence>
<OriginalGUID>10001</OriginalGUID>
</MessageHead>
<MessageHead>
<Sequence>1002</Sequence>
<OriginalGUID>10002</OriginalGUID>
</MessageHead>
<MessageHead>
<Sequence>1003</Sequence>
<OriginalGUID>10003</OriginalGUID>
</MessageHead>
<MessageHead>
<Sequence>1004</Sequence>
<OriginalGUID>10004</OriginalGUID>
</MessageHead>
</Node>
</Publish>
'
declare@NodeCount INT
,@SequenceINT
,@OriginalGUIDINT
;WITHXMLNAMESPACES(DEFAULT 'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECT@NodeCount = @Message.value(N'count(Publish/Node/MessageHead)','INT')
IFOBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROPTABLE #temp
CREATETABLE #temp
(
SequenceINT
,OriginalGUIDINT
)
INSERTINTO #temp
SELECT1003,10003
UNIONALL
SELECT1004,10004
UNIONALL
SELECT1008,10008
SELECT *FROM #temp
WHILE(@NodeCount > 0)
BEGIN
;withxmlnamespaces(default'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
SELECT
@Sequence=
@Message.value('(/Publish/Node/MessageHead[sql:variable("@NodeCount")]/Sequence/text())[1]','INT')
,@OriginalGUID =
@Message.value('(/Publish/Node/MessageHead[sql:variable("@NodeCount")]/OriginalGUID/text())[1]','INT')
DELETE
FROM#temp
WHERESequence = @Sequence
ANDOriginalGUID = @OriginalGUID
SET@NodeCount = @NodeCount - 1
END
SELECT *FROM #temp
-----------------------------------建议的处理方式
IFOBJECT_ID('tempdb.dbo.#temp1') IS NOT NULL
DROPTABLE #temp1
CREATETABLE #temp1
(
SequenceINT
,OriginalGUIDINT
)
INSERTINTO #temp1
SELECT1003,10003
UNIONALL
SELECT1004,10004
UNIONALL
SELECT1008,10008
SELECT *FROM #temp1
DECLARE@T TABLE
(
SequenceINT
,OriginalGUIDINT
)
;WITHXMLNAMESPACES(DEFAULT'http://soa.Test.com/SOA/USA/InfrastructureService/V10/PubSubService')
INSERTINTO @T
SELECTT.C.value('(./Sequence/text())[1]','INT')
,T.C.value('(./OriginalGUID/text())[1]','INT')
FROM@Message.nodes('Publish/Node/MessageHead') AS T(C)
DELETE A
FROM#temp1 AS A
INNERJOIN @T AS B
ONA.Sequence = B.Sequence
ANDA.OriginalGUID = B.OriginalGUID
SELECT *FROM #temp1
---------------------------------------处理xml节点解析的问题
declare@messageBody xml
set@messageBody = N'
<Publishxmlns="http://soa.Test.com/SOA/CN/InfrastructureService/V10/NeweggCNPubSubService">
<Subject>WH49TMSFeedback</Subject>
<FromService>http://soa.Test.com/SOA/CN/OrderManagement/V10/Warehouse49/TMSFeedback</FromService>
<ToService>http://soa.Test.com/SOA/CN/InfrastructureService/V10/NeweggCN/PubSubService</ToService>
<Node>
<Root>TestMsg</Root>
</Node>
</Publish>'
selectCAST(REPLACE(CAST(@messageBody AS nvarchar(MAX))
,'xmlns="http://soa.Test.com/SOA/CN/InfrastructureService/V10/NeweggCNPubSubService"','')as
xml).query('/Publish/Node/Root') --SSBRouter方式
--正确的写法
select@messageBody.query('declare default element namespace
"http://soa.Test.com/SOA/CN/InfrastructureService/V10/NeweggCNPubSubService"
;/Publish/Node/Root')