1.从1开始编号,赋予其连续的编号
select ROW_NUMBER() OVER (ORDER BY 字段 asc/desc) AS RowNumber from Table
2.分区函数Partition By //注:可以用在表数据在显示时根据某个字段分区
select row_number()over(partition by 字段 order by 字段 asc/desc) as mm from Table
相比row_number() rank()数据会存在并列顺序
select rank()over(partition by 字段 order by 字段 asc/desc) as mm from Table
3.条件判断语句
select (case when SNo = 1 then Province else '' end) Province from Table
4. For Xml Path('')将多列数据拼接成一个字段,STUFF(a.name,1,1,'') 字符串截断
select STUFF(a.name,1,1,'') from (
SELECT ';'+ '默认运费'+Str(e.DefaultUnitNumber) +'内,'+Str(e.DefaultFee)+'元,每增加' +Str(e.EachUnitNumber)+',增加运费'+Str(e.EachUnitFee)+'元'
FROM LogisticsAreaShippingExpenseItems e
where e.IsLock=1 and e.FeeCategory=1
FOR XML PATH('')
) a(name)
5 CROSS APPLY 将2张表数据合成到同一张表中(没有主外键关系也可以)