SQL基础

遇到要百度的就在这里记录，会不断更新

查询重复记录大于2的SQL语句范例：

SELECT *FROM classWHERE name IN (SELECT name FROM class GROUP BY name HAVING COUNT(num) >=2)

使用left join范例:

  SELECT a.effectDate as transactionTime ,--异动时间
   a.name as empName,
   a.employeeNo as empNo,-- 工号
  b.name as transferType,-- 异动类型
  c.note as leavetype,-- 离开类型
  a.remark as remarks,-- 备注
  d.name as transactionAfterDepartment,-- 异动以后部门
  e.name as transactionBeforeDepartment,-- 异动以前部门
  f.wxh_name as transactionAfterAffiliations,-- 异动以后机构
  g.wxh_name as transactionBeforeAffiliations,-- 异动以前机构
  s.name as transactionAfterPost,-- 异动以后职位
  s.name as transactionBeforePost,-- 异动以前职位
  dl.name as transactionAfterJobLevel-- 异动以后职位等级
  FROM  [HRMS_NEW].[dbo].[HRMS_PERSONNEL_TRANSFER] a
  left join [HRMS_NEW].[dbo].[HRMS_TRANSFER_TYPE] b on a.transferType_id = b.id 
  left join [HRMS_NEW].[dbo].[HRMS_LEFT_Type] c on a.leftType_id = c.id
  left join [HRMS_NEW].[dbo].[org_department] d on a.newDepartmentCode = d.code
  left join [HRMS_NEW].[dbo].[org_department] e on a.oldDepartmentCode=e.code
  left join [HRMS_NEW].[dbo].[org_hotel] f on a.newHotelCode=f.wxh_no
  left join [HRMS_NEW].[dbo].[org_hotel] g on a.oldHotelCode=g.wxh_no
  left join [HRMS_NEW].[dbo].[org_station] s on a.newStationCode= s.code
  left join [HRMS_NEW].[dbo].[org_station] s1 on a.oldStationCode= s1.code
  left join [HRMS_NEW].[dbo].[HRMS_DUTY_LEVEL] dl on dl.id = a.newDutyLevel
 where a.employeeNo in(select employeeNo from [HRMS_NEW].[dbo].[HRMS_PERSONNEL_TRANSFER] group by employeeNo having count(employeeNo)>8 )