一些经常需要复制的sql（SQLServer）

1.该sql用于查询员工出勤天数、日计划总完成量，总核查量，以及个人、核查量按参数比例计算月均完成率

select  e.name,a.*, 
		(a.overall_progress * (select config_value FROM sys_config where config_key = 'emp_workPlan_progress')/100 
		+ a.check_overall_progress *(100- (select config_value FROM sys_config where config_key = 'emp_workPlan_progress'))/100 ) / a.summary_days as average_progress ,
		b.attendance_days,
		b.attendance_days - a.summary_days as missing_days 
from (select emp_no,sum(progress) as overall_progress,sum(check_by) as check_overall_progress,count(1) as summary_days  ,( DateName(year,plan_start_time)+'-'+
	      DateName(month,plan_start_time) ) as summary_time from emp_work_plan where status = 0 GROUP BY emp_no,
				( DateName(year,plan_start_time)+'-'+DateName(month,plan_start_time) )) a 
left join (select emp_no,count(1) as attendance_days , ( DateName(year,time)+'-'+DateName(month,time) ) as attendance_time  
          from emp_check WHERE status_code IN ('Z001','Z006','Z004','Z005') GROUP BY emp_no, 
					( DateName(year,time)+'-'+DateName(month,time) )   )b 
on a.emp_no = b.emp_no and a.summary_time = b.attendance_time 
LEFT JOIN bas_employee e on e.emp_no = a.emp_no

2.获取int型ID

select case when (select max(id)+1 from proj_plan where ancestor = '0'  ) is null
                        then 2
                    else (select max(id)+1 from proj_plan where ancestor = '0')
                   end

3.统计类型

SELECT case 
 when grouping(w.emp_no)='0'and grouping(w.name)=0 and grouping(d.dept_name)=0 and grouping(e.dept_id)=0 and grouping(DateName(year,w.actual_in_time)+'-'+DateName(month,w.actual_in_time))=1  then '个人小计'
 when grouping(w.emp_no)='1'and grouping(w.name)=1 and grouping(e.dept_id)=0  then '部门合计'
 when grouping(w.emp_no)='1' and grouping(w.name)=1 and grouping(e.dept_id)=1 then '总计'
           
else d.dept_name end dept_name,w.emp_no,( DateName(year,w.actual_in_time)+'-'+DateName(month,w.actual_in_time) ) actual_in_time,w.name,e.dept_id,SUM(CONVERT(int,sum_day)) AMOUNT,COUNT(*) TRAYCOUNT 
          from emp_evection_record  w 
					LEFT JOIN bas_employee e on w.emp_no = e.emp_no 
					LEFT JOIN bas_dept d on e.dept_id = d.dept_id 

          GROUP BY e.dept_id,w.name, d.dept_name,w.emp_no,( DateName(year,w.actual_in_time)+'-'+DateName(month,w.actual_in_time) )
          WITH ROLLUP
					HAVING ( grouping(d.dept_name)='0' and grouping(w.name)='0' and grouping(e.dept_id)='0' and grouping(w.emp_no)='0')
					or ( grouping(d.dept_name)='1' and grouping(w.name)='1' and grouping(e.dept_id)='0' and grouping(w.emp_no)='1')
					or ( grouping(d.dept_name)='1' and grouping(w.name)='1' and grouping(e.dept_id)='1' and grouping(w.emp_no)='1')

4.sqlserver计算两个日期差值

select datediff(year, 开始日期,结束日期); --两日期间隔年
select datediff(quarter, 开始日期,结束日期); --两日期间隔季
select datediff(month, 开始日期,结束日期); --两日期间隔月
select datediff(day, 开始日期,结束日期); --两日期间隔天
select datediff(week, 开始日期,结束日期); --两日期间隔周
select datediff(hour, 开始日期,结束日期); --两日期间隔小时
select datediff(minute, 开始日期,结束日期); --两日期间隔分
select datediff(second, 开始日期,结束日期); --两日期间隔秒

5.判断字段值为空时返回0或特定值

ISNULL([字段], '为空时的返回值') AS data

6.查一个月中每一天的数据，当天无记录时赋0

SELECT
 a.dt AS date,
 ISNULL(b.count, '0') AS data
FROM
 (
  SELECT
    CONVERT(VARCHAR(10), dateadd(d, number, dateadd(month, datediff(month, 0, getdate()), 0)),120)  dt
  FROM
   master..spt_values
  WHERE
   type = 'p'
  AND dateadd(d, number, dateadd(month, datediff(month, 0, getdate()), 0)) <=  dateadd(month, datediff(month, 0, dateadd(month, 1, getdate())), -1)
 ) a
LEFT JOIN (select CONVERT(VARCHAR(10),start_time,120) as start_time,count(1) as count from mydo_job_finish WHERE in_or_out >10 GROUP BY CONVERT(VARCHAR(10),start_time,120)) b ON a.dt =b.start_time

7.修改字符串中的部分字符

（例如将0100234只修改前两位，变成0200234）

UPDATE bas_cargo_space SET group_coding = STUFF(group_coding, 1, 2, '02') FROM bas_cargo_space WHERE LEFT(group_coding, 2) = '02' AND the_platoon = 2```