作
--
UPDATE t1 SET tclo=1 FROM t1,t2 WHERE t1.id=t2.id
-------------------14=========================日期函数
--
--datediff(ex,begin,end)
--时差--ex:表达式，begin:开始时间，end:结束时间，ex=end-begin
SELECT DATEDIFF(hh,'2012-03-03 10:11','2012-03-04 09:11')
--加时--ex:表达式，加时，时间
SELECT DATEADD(hh,24,'2012-03-03 10:11')
-------------------15=========================行列互转（两种方式）
--1 定义变量
DECLARE @result VARCHAR(255)
SET @result = ''
SELECT @result = @result + cast(tdd.[name] as varchar(255)) +','
FROM T tdd(NOLOCK) WHERE 1=1
IF(len(@result) - 1<0) SELECT '' AS [name]
ELSE SELECT LEFT(@result,len(@result) - 1) AS [name]
--2 使用 for xml path(''),使用stuff函数将第一个,号替换掉
SELECT
[name]=stuff((select ','+cast(tt.[name] AS VARCHAR(10))
from T tt where 1=1 for xml path('')), 1, 1, '')
FROM @temp
-------------------16=========================查看/删除/创建表约束
--查看表约束
sp_helpconstraint 表名
--删除表约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名
--创建表约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFAULT ('') FOR 字段
-------------------17===========================With ** AS（）使用
--- 临时表
WITH tt AS (
SELECT * FROM tab
)
-- 递归
WITH dept AS(
SELECT cd.Code,cd.CodeName
FROM CostDept cd(NOLOCK) WHERE cd.Code='0123'
UNION ALL
SELECT cd1.Code,cd1.CodeName
FROM dept JOIN CostDept cd1(NOLOCK) ON dept.Code=cd1.ParentCode
)
SELECT * FROM dept
-------------------------------------------------------------------------------
SELECT tt.DeliveryOrderNO,tt.SysOrderType FROM (
SELECT T.DeliveryOrderNO,
SysOrderType=stuff((select ','+cast(tt.SysOrderType AS VARCHAR(10))
from TMS_DeliveryOrderDetail tt where tt.DeliveryOrderNO=T.DeliveryOrderNO for xml path('')), 1, 1, '')
FROM (
SELECT tdo.DeliveryOrderNO
FROM TMS_DeliveryOrder tdo
WHERE 1=1
and tdo.ReceivedDate>='2012-03-01 01:01' AND tdo.ReceivedDate<=GETDATE()
AND tdo.OrderType=2
--AND tdo.DeliveryOrderNO='0103Y1203010001'
) T ) TT WHERE 1=1
-- and (charindex('13',SysOrderType)>0 OR charindex('13',SysOrderType)>0 OR charindex('16',SysOrderType)>0 OR charindex('17',SysOrderType)>0 OR charindex('18',SysOrderType)>0)
--AND charindex('13',SysOrderType)=0 AND charindex('16',SysOrderType)=0 AND charindex('18',SysOrderType)=0
AND (charindex('10',SysOrderType)>0 OR charindex('17',SysOrderType)>0)
AND (charindex('13',SysOrderType)=0 AND charindex('16',SysOrderType)=0 AND charindex('18',SysOrderType)=0)
SELECT * FROM TMS_DeliveryOrderDetail tdod WHERE tdod.FactoryID IS NOT NULL
-------------------18================Update 时增加 @ERROR<>0 OR @ROWCOUNT<>cnt(影响行数) 判断
IF(@@ERROR<>0 OR @@ROWCOUNT<>1)
BEGIN
ROLLBACK
PRINT 'RollBack'
RETURN
END
PRINT 'Commit Start'
COMMIT
PRINT 'Commit Over'
SELECT * FROM TMS_DeliveryOrderDetail tdod
WHERE EXISTS(
SELECT * FROM TMS_SYN_Redeploy tsr WHERE
tsr.RedeployCode IN
(
)
--------------------19=====================大数据表循环删除脚本
DECLARE @icount INTEGER;
DECLARE @StartDate date;
DECLARE @EndDate date;
SET @StartDate = CONVERT(date,'2012-08-01');
SET @EndDate = CONVERT(date,'2012-08-31');
;
WHILE 1=1
BEGIN
DELETE TOP (20000)
FROM [dbo].[ForecastBaseData]
WHERE CreateDate>=@StartDate
and CreateDate<=@EndDate
;
SET @icount = @@ROWCOUNT
;
IF @icount<>20000
BREAK
;
WAITFOR DELAY '00:00:05'
END
--------------------19 END=====================
|