Ecology9 常用SQL大全

SELECT * FROM HrmSubCompany WHERE canceled IS NULL

SELECT * FROM HrmDepartment WHERE canceled is NULL or  canceled = 0

SELECT count(*) FROM HrmDepartment WHERE canceled is NULL or  canceled = 0

WITH dept_h AS (
  SELECT id,departmentname,supdepid, CAST(departmentname AS VARCHAR(MAX)) AS fullName FROM HrmDepartment WHERE (canceled IS NULL OR  canceled = 0 ) AND supdepid = 0
  UNION ALL
  SELECT t1.id,t1.departmentname,t1.supdepid,CONCAT(t2.fullName,'>', t1.departmentname) FROM HrmDepartment AS t1
  INNER JOIN dept_h AS t2 ON t1.supdepid  = t2.id
  WHERE t1.supdepid != 0 AND (canceled IS NULL OR canceled = 0)
)
SELECT * FROM dept_h

-- MySQl代码
SELECT
  t1.ID AS ID,
  t1.WORKCODE AS WORKCODE,
  t1.SUBCOMPANYID1 AS SUBCOMPANYID1,
  t1.DEPARTMENTID AS DEPARTMENTID,
  t1.DSPORDER AS dsporder,
  t2.kqdate AS kqdate,
  t2.attendancemins AS attendancemins,
  t3.duration_min AS duration_min,
  round( ( ( COALESCE ( t2.attendancemins, 0 ) + COALESCE ( t3.duration_min, 0 ) ) / 60 ), 2 ) AS total_hours,
  dayofmonth ( CAST ( t2.kqdate AS DATE ) ) AS dd
FROM
(
  (
    hrmresource AS t1
    INNER JOIN ( SELECT kq_format_total.kqdate AS kqdate, kq_format_total.resourceid AS resourceid, kq_format_total.attendancemins AS attendancemins FROM kq_format_total ) AS t2 ON ( ( t2.resourceid = t1.ID ) )
  )
  LEFT JOIN ( SELECT kq_flow_overtime.belongdate AS belongdate, kq_flow_overtime.resourceid AS resourceid, kq_flow_overtime.duration_min AS duration_min FROM kq_flow_overtime ) AS t3 ON ( ( ( t3.belongdate = t2.kqdate ) AND ( t3.resourceid = t2.resourceid ) ) )
)
ORDER BY
t1.DEPARTMENTID ASC,
t1.DSPORDER ASC,
t2.kqdate ASC

SELECT
hr.workcode AS 工作编号 ,
hr.lastname AS 员工姓名 ,
hr.loginid AS 登录账号 ,
hr.password AS 密码,
hr.accounttype AS 账号类型,
hr.belongto AS 所属主账号,
CASE
hr.sex
WHEN 0 THEN
'男'
WHEN 1 THEN
'女'
END AS 性别,
hr.seclevel AS 安全等级,
jt.jobtitlename AS 岗位,
ja.jobactivityname AS 职务,
jg.jobgroupname AS 职务类型,
jc.name AS 职称,
hr.JOBLEVEL AS 职级,
hr2.lastname AS 直接上级,
jt.jobresponsibility AS 岗位职责,
hr3.lastname AS 助理,
CASE
hr.status
WHEN 0 THEN
'试用'
WHEN 1 THEN
'正式'
WHEN 2 THEN
'临时'
WHEN 3 THEN
'试用延期'
WHEN 4 THEN
'解聘'
WHEN 5 THEN
'离职'
WHEN 6 THEN
'退休'
WHEN 7 THEN
'无效'
END AS 状态,
hr.workroom AS 办公室,
hl.locationname AS 办公地点,
hr.extphone AS 办公电话,
hr.mobile AS 移动电话,
hr.telephone AS 其它电话,
hr.fax AS 传真,
hr.email AS 电子邮件,
'简体中文' AS 系统语言,
hr.birthday AS 出生日期,
hr.folk AS 民族,
hr.nativeplace AS 籍贯,
hr.regresidentplace AS  户口,
hr.certificatenum AS 身份证号码,
CASE hr.maritalstatus
WHEN 1 THEN '已婚'
WHEN 0 THEN '未婚'
END AS 婚姻状况,
hr.policy AS 政治面貌,
hr.bememberdate AS 入团时间,
hr.bepartydate AS  入党时间,
CASE hr.islabouunion
WHEN 0 THEN '否'
WHEN 1 THEN '是'
END AS 工会会员,
hdl.name AS 学历,
hr.degree AS 学位,
CASE hr.healthinfo
WHEN 0 THEN '优秀'
WHEN 1 THEN '良好'
WHEN 2 THEN '一般'
WHEN 3 THEN '较差'
END AS 健康状况,
hr.height AS 身高,
hr.weight AS 体重,
huk.name AS 用工性质,
hr.startdate AS 合同开始日期,
hr.enddate AS  合同结束日期,
hr.probationenddate AS 试用期结束日期,
hr.companystartdate AS 入职日期,
hr.workstartdate AS 参加工作日期,
hr.residentplace AS  现居住地,
hr.homeaddress AS 家庭联系方式,
hr.tempresidentnumber AS 暂住证号码,
hr.accountname AS 工资账号户名,
hr.bankid1 AS 工资银行,
hr.accountid1 AS 工资账号,
hr.accumfundaccount AS 公积金账户,
'' AS 显示顺序
FROM
HrmResource AS hr
LEFT JOIN HrmJobTitles AS jt ON jt.id = hr.jobtitle
LEFT JOIN HrmJobActivities AS ja ON ja.id = jt.jobactivityid
LEFT JOIN HrmJobGroups AS jg ON ja.jobgroupid = jg.id
LEFT JOIN HrmJobCall AS jc ON hr.jobcall = jc.id
LEFT JOIN HrmResource AS hr2 ON hr.managerid = hr2.id
LEFT JOIN HrmResource AS hr3 ON hr.assistantid = hr3.id
LEFT JOIN HrmLocations AS hl ON hl.id = hr.locationid
LEFT JOIN hrmeducationlevel AS hdl ON hdl.id = hr.educationlevel
LEFT JOIN HrmUseKind AS  huk  ON huk.id = hr.usekind