1.EXISTS 运算符
EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。
2.Where条件也可以使用 case ..when ..THEN..ELSE..END
WHERE OrderDate >= CASE ( ISNULL(@OrderDateBegin, '') ) WHEN '' THEN OrderDate ELSE CONVERT(DATETIME, @OrderDateBegin) END AND OrderDate < ‘2020-10-1‘ END
3.where条件表达式嵌套子语句
WHERE (
c.departmentid = @departmentid
OR c.departmentid IN (
SELECT id
FROM department(NOLOCK)
WHERE tid = @departmentid )
) and 1=1
4.存储过程拼接sql，执行sql
DECLARE @SQL varchar(8000)
SET @SQL = 'Select * from ....'
SET @SQL = @SQL + '' + ' where 1=1 ' + ' ' ' + @abc + ' ' '
--print(@sql)
EXEC (@SQL)
5. 转换变量日期类型调用日期函数
DATEADD(mm,-1,CONVERT(datetime, @OrderDateBegin))
6. select 字段处可以使用返回一个值的select子语句
SELECT a.UserName,
(select count(1) from ship (NOLOCK) where
CONVERT(varchar(10), Disdate, 20) BETWEEN
CASE(ISNULL(@OrderDateBegin, '')) WHEN '' THEN CONVERT(varchar(10), Disdate, 20) ELSE @OrderDateBegin END ) as Amount, a.Text From ABC a
7.select 中存在子查询
SELECT NEWID(), 'H' + CONVERT(VARCHAR(8),r.Date,112) + b.OrderNo
+ '-' + CONVERT(VARCHAR(8), ( ( SELECT COUNT(1) FROM RepShip WHERE OrderNo= r.OrderNo) + ROW_NUMBER()OVER(PARTITION BY b.OrderNo ORDER BY r.Date ) ) )
,....
FROM ship r
