某公司的人事系统数据库中有三张表： 员工表（EMP）,部门表（DEPT）,工资表（SALARY） EMP表结构如下？

main.dept_name as 部门名称,

count(*) as 在职员工数,

sum(acu_salary) as 实发工资总额

FROM

dept main

INNER JOIN emp a ON main.dept_code = a.dept_code

INNER JOIN salary b ON a.emp_id = b.emp_id

WHERE

-- 1为有效标识

main.alive_flag = '1'

AND a.alive_flag = '1'

AND b.alive_flag = '1'

AND b.`month` = '201101'

GROUP BY

a.dept_code

HAVING

count(*) >5

代码如下

oracle的

select

reverse(

chr(case when ascii('a')*2+10>=128 then round((ascii('a')*2+10)/3) else ascii('a')*2+10 end)||

chr(case when ascii('b')*2+10>=128 then round((ascii('b')*2+10)/3) else ascii('b')*2+10 end)||

chr(case when ascii('c')*2+10>=128 then round((ascii('c')*2+10)/3) else ascii('c')*2+10 end)||

chr(case when ascii('d')*2+10>=128 then round((ascii('d')*2+10)/3) else ascii('d')*2+10 end)||

chr(case when ascii('e')*2+10>=128 then round((ascii('e')*2+10)/3) else ascii('e')*2+10 end))

from dual

sqlserver的

select reverse

(

char(case when ascii('a')*2+10>=128 then (ascii('a')*2+10)/3 else ascii('a')*2+10 end)+

char(case when ascii('b')*2+10>=128 then round((ascii('b')*2+10)/3.0,0) else ascii('b')*2+10 end)+

char(case when ascii('c')*2+10>=128 then round((ascii('c')*2+10)/3.0,0) else ascii('c')*2+10 end)+

char(case when ascii('d')*2+10>=128 then round((ascii('d')*2+10)/3.0,0) else ascii('d')*2+10 end)+

char(case when ascii('e')*2+10>=128 then round((ascii('e')*2+10)/3.0,0) else ascii('e')*2+10 end)

)