SQL常用语句

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