select ID as '部门编号', Department as '部门名称',
[正式] = Sum ( case when EmployeeType = ' 正式 ' then Cnt else 0 end ),
[临时] = Sum ( case when EmployeeType = ' 临时 ' then Cnt else 0 end ),
[辞退] = Sum ( case when EmployeeType = ' 辞退 ' then Cnt else 0 end ),
[合计] = Sum ( case when EmployeeType <> '' then Cnt else 0 end )
from VDepartmentEmployeeType group by ID, Department上面的sql是sqlserver的,是个表的行列转换
[正式] = Sum (...),改成oracle应该怎么写呢?
[正式] = Sum ( case when EmployeeType = ' 正式 ' then Cnt else 0 end ),
[临时] = Sum ( case when EmployeeType = ' 临时 ' then Cnt else 0 end ),
[辞退] = Sum ( case when EmployeeType = ' 辞退 ' then Cnt else 0 end ),
[合计] = Sum ( case when EmployeeType <> '' then Cnt else 0 end )
from VDepartmentEmployeeType group by ID, Department上面的sql是sqlserver的,是个表的行列转换
[正式] = Sum (...),改成oracle应该怎么写呢?
解决方案 »
- oracle10g 怎么修改警告日志的记录时间间隔,默认是5分钟
- 通过代码循环往数据库中插入数据时报错,求大神指教
- 求一个Oracle操作批处理程序 在两个用户间导入导出某几个表
- 一对多 SQL 语句求教
- 急!oracle中用什么语句修改数据库表名!
- ORACLE不可用!!!01034 oracle not available
- 如何取得服务器的时间?
- 有关Oracle安装的问题?
- 特急求救,关于DBA如何知道每种操作系统I/O缓冲区大小的问题,包括windows2000、windows NT、各种Linux版本、Unix等
- 两列合并统计
- 请教oracle 10g 关于日期格式的问题
- 急,请问如何定义一个rowid 类型的变量
Sum ( case when EmployeeType = ' 正式 ' then Cnt else 0 end ) 正式,
Sum ( case when EmployeeType = ' 临时 ' then Cnt else 0 end ) 临时 ,
Sum ( case when EmployeeType = ' 辞退 ' then Cnt else 0 end ) 辞退,
Sum ( case when EmployeeType <> '' then Cnt else 0 end ) 合计
from VDepartmentEmployeeType group by ID, Department
select ID 部门编号, Department 部门名称,
Sum ( case when EmployeeType = ' 正式 ' then Cnt else 0 end ) 正式,
Sum ( case when EmployeeType = ' 临时 ' then Cnt else 0 end ) 临时 ,
Sum ( case when EmployeeType = ' 辞退 ' then Cnt else 0 end ) 辞退,
Sum ( case when EmployeeType <> '' then Cnt else 0 end ) 合计
from VDepartmentEmployeeType group by ID, Department
select ID '部门编号', Department '部门名称',
Sum ( case when EmployeeType = ' 正式 ' then Cnt else 0 end ) 正式,
Sum ( case when EmployeeType = ' 临时 ' then Cnt else 0 end ) 临时,
Sum ( case when EmployeeType = ' 辞退 ' then Cnt else 0 end ) 辞退,
Sum ( case when EmployeeType <> '' then Cnt else 0 end ) 合计
from VDepartmentEmployeeType group by ID, Department
Sum( case when EmployeeType = ' 正式 ' then Cnt else 0 end ) as "正式",
Sum( case when EmployeeType = ' 临时 ' then Cnt else 0 end ) as "临时",
Sum( case when EmployeeType = ' 辞退 ' then Cnt else 0 end ) as "辞退",
Sum( case when nvl(EmployeeType,'')<>'' then Cnt else 0 end ) as "合计"
from VDepartmentEmployeeType
group by ID, Department;
---------- 方法二:decode函数 -----------------------
select ID as '部门编号', Department as '部门名称',
Sum(decode(EmployeeType,' 正式 ',Cnt,0) as "正式",
Sum(decode(EmployeeType,' 临时 ',Cnt,0) as "临时",
Sum(decode(EmployeeType,' 辞退 ',Cnt,0) as "辞退",
Sum(decode(EmployeeType,null,0,'',0,Cnt) as "合计"
from VDepartmentEmployeeType
group by ID, Department;
select ID as '部门编号', Department as '部门名称',
[正式] = Sum ( decode(EmployeeType,'正式',1,0),
[临时] = Sum (decode(EmployeeType,'临时',1,0 ),
[辞退] = Sum ( decode(EmployeeType,'辞退',1,0 ),
[合计] = Sum ( decode(EmployeeType,'合计',1,0) )
from VDepartmentEmployeeType group by ID, Department