1、查询重复 select id，name from users where id in (select id from users group by id having count(id)>=2)
2、去除重复 select distinct * from users
3、子查询（sql增列）统计
select id=row_number()over(order by Le_NameOfAssociate),
Le_NameOfAssociate as name,Le_ApplierID as userultcode,Year,
(isnull((select SUM(CONVERT(int, LegitimateVaction))+ SUM(CONVERT(int, WelfareVaction)) from dbo.VactionInfo where 'BPM/'+GUID= a.Le_ApplierID and Year=a.Year),0)- isnull( (select SUM(Le_Days)+ SUM(le_days1) from Rs_LeaveInfo where Le_LeaveType='年假' and Le_ApplierID=a.Le_ApplierID and Year= a.Year),0)) as WeiXiu,
(select SUM(Le_Days)+ SUM(le_days1) from Rs_LeaveInfo where Le_LeaveType='年假' and Le_ApplierID=a.Le_ApplierID and Year=a.Year)as NJleave, (select SUM(Le_Days)+ SUM(le_days1) from Rs_LeaveInfo where Le_LeaveType='产前检查' and Le_ApplierID=a.Le_ApplierID and Year=a.Year)as CQJCleave,
(select SUM(Le_Days)+ SUM(le_days1) from Rs_LeaveInfo where Le_LeaveType='流产休假' and Le_ApplierID=a.Le_ApplierID and Year=a.Year)as LCXJleave,
(select SUM(Le_Days)+ SUM(le_days1) from Rs_LeaveInfo where Le_LeaveType='病假' and Le_ApplierID=a.Le_ApplierID and Year=a.Year)as BJleave, (select SUM(Le_Days)+ SUM(le_days1) from Rs_LeaveInfo where Le_LeaveType='丧假' and Le_ApplierID=a.Le_ApplierID and Year=a.Year)as SJleave, (select SUM(Le_Days)+ SUM(le_days1) from Rs_LeaveInfo where Le_LeaveType='产假' and Le_ApplierID=a.Le_ApplierID and Year=a.Year)as CJleave, (select SUM(Le_Days)+ SUM(le_days1) from Rs_LeaveInfo where Le_LeaveType='婚假' and Le_ApplierID=a.Le_ApplierID and Year=a.Year)as HJleave, (select SUM(Le_Days)+ SUM(le_days1) from Rs_LeaveInfo where Le_LeaveType='陪产假' and Le_ApplierID=a.Le_ApplierID and Year=a.Year)as PCJleave, (select SUM(Le_Days)+ SUM(le_days1) from Rs_LeaveInfo where Le_LeaveType='无薪事假' and Le_ApplierID=a.Le_ApplierID and Year=a.Year)as WXSJleave,
(select SUM(Le_Days)+ SUM(le_days1) from Rs_LeaveInfo where Le_LeaveType='护理假' and Le_ApplierID=a.Le_ApplierID and Year=a.Year)as HLleave, ((select SUM(CONVERT(int, LegitimateVaction))+ SUM(CONVERT(int, WelfareVaction)) from dbo.VactionInfo where 'BPM/'+GUID= a.Le_ApplierID and Year=CONVERT(int, a.Year)-1)- isnull( (select SUM(Le_Days)+ SUM(le_days1) from Rs_LeaveInfo where Le_LeaveType='年假' and Le_ApplierID=a.Le_ApplierID and Year=CONVERT(int, a.Year)-1),0)) as LastNJ,
(select SUM(CONVERT(int, LegitimateVaction))+ SUM(CONVERT(int, WelfareVaction)) from dbo.VactionInfo where 'BPM/'+GUID= a.Le_ApplierID and Year=a.Year)as leavesum,
(select top 1 Initials from EmployeeManagement where 'BPM/'+EECode= a.Le_ApplierID )as userid,
(select top 1 EnglishName from EmployeeManagement where 'BPM/'+EECode= a.Le_ApplierID )as enname
from Rs_LeaveInfo a
--where a.Year='' and a.Le_NameOfAssociate=''
group by a.Le_ApplierID,Le_NameOfAssociate,Year
3、sql中使用替换：REPLACE(REPLACE( StepNote,'font-family:','font-family:Times New Roman;'),'font-size:','font-size: 16px;') as StepNote
4、ROW_NUMBER() OVER 分页:select * from ( select ca_id,ROW_NUMBER() over (order by ca_id) as Noc from dbo.Cw_Payment ) as tb where Noc between 1 and 2
5、分组取每组最大的数据行：（子查询）
select StepCount,MaterialOrder,CalcdAV, CalcdUnit from dbo.Pe_StepsMaterial as a where MaterialOrder = (select top 1 MaterialOrder from Pe_StepsMaterial as b where b.Pe_PerVer='PE002305.1.1' and a.StepCount=b.StepCount order by MaterialOrder desc) and a.Pe_PerVer='PE002305.1.1' group by StepCount,MaterialOrder,CalcdAV, CalcdUnit order by MaterialOrder desc