oracle实现行列转换的2种方法

2013-12-29

1、通用方法,通过max,case组合


select group_name 班别,
STAFF_ID 工号,
max(case
when (sm_theme = '副卡2终端') then
scount
else
0
end) 副卡2终端,
max(case
when (sm_theme = '子卡1终端') then
scount
else
0
end) 子卡1终端,
max(case
when (sm_theme = '子卡2终端') then
scount
else
0
end) 子卡2终端,
max(case
when (sm_theme = '天翼终端') then
scount
else
0
end) 天翼终端,
max(case
when (sm_theme = '副卡1终端') then
scount
else
0
end) 副卡1终端

from (select count(v.sm_theme) as scount,
sm_theme,
h.group_name,
h.STAFF_ID
from v_order_detail v, v_hrm_staff h

where v.STAFF_ID = h.STAFF_ID and v.sm_srtype=1
group by v.sm_theme, h.group_name, h.STAFF_ID) T
group by STAFF_ID, group_name

 

2、通过特有的decode实现行列转换


select group_name 班别,
STAFF_ID 工号,
SUM(DECODE(sm_theme, '副卡2终端', scount, 0)) 副卡2终端,
SUM(DECODE(sm_theme, '子卡1终端', scount, 0)) 子卡1终端,
SUM(DECODE(sm_theme, '子卡2终端', scount, 0)) 子卡2终端,
SUM(DECODE(sm_theme, '天翼终端', scount, 0)) 天翼终端,
SUM(DECODE(sm_theme, '副卡1终端', scount, 0)) 副卡1终端

from (select count(v.sm_theme) as scount,
sm_theme,
h.group_name,
h.STAFF_ID
from v_order_detail v, v_hrm_staff h

where v.STAFF_ID = h.STAFF_ID
and v.sm_srtype = 1
group by v.sm_theme, h.group_name, h.STAFF_ID) T
group by STAFF_ID, group_name

总结:sql技巧太多

作者:robotbird, 分类:关于代码 标签: oracle , 浏览(2100), 评论(0)
上一篇: oracle 性能优化示例
下一篇: 罗马的故事-苏拉

相关文章

(0)条评论 订阅

发表评论

电子邮件用于回复通知和avatar全球唯一头像 *

*