问题一：避免报表中的重复行显示。
select
case when job_ctr = 1 then job_id else null end "JOB_ID",
case when jobman_ctr = 1 then manager_id else null end "MANAGER_ID",
employee_id
from (
select job_id,
row_number() over (partition by job_id order by job_id)
as job_ctr,
manager_id,
row_number() over (partition by job_id, manager_id order by job_id, manager_id)
as jobman_ctr,
employee_id
from hr.employees
);
注：巧用oracle分析函数row_number() over()，上面的语句展示了这个效果，他把重复的字段合并成一条显示。
问题二：同时操作分组中的最小行和最大行。
select department_id, employee_id, first_name, last_name,
case
when hire_date = first_hire and hire_date = last_hire
then 'Shortest and Longest Serving'
when hire_date = first_hire then 'Longest Serving'
when hire_date = last_hire then 'Shortest Serving'
end Department_Tenure
from (
select department_id, employee_id, first_name, last_name, hire_date,
max(hire_date) over(partition by department_id) first_hire,
min(hire_date) over(partition by department_id) last_hire
from hr.employees
) Hire_Dates
where hire_date in (first_hire, last_hire)
order by department_id, department_tenure;
注：巧用oracle分析函数 max() over()和min() over()，上面的语句展示了这个效果，他能同时处理最小值和最大值。
问题三：显示分组统计中的处理第几位的值。
select department_id, last_name, salary as third_salary
from (
select department_id, last_name, salary,
row_number()
over (partition by department_id order by salary desc) sal_position
from hr.employees )
sal_by_dept
where sal_by_dept.sal_position = 3;
注：巧用oracle分析函数row_number() over()，上面的语句展示了这个效果，他可以查看分组后排在第二位或者第三位的值，很强大。
问题四：把一个结果集根据一个字段平均分成几部分，可以使2部分，3部分，4部分等等。
select aac001,
aic142,
ntile(4) over(order by aic142 desc) as aic142_quartile
from ic13
where aac001 = '1302000314319'
order by aic142
1 1302000314319 40.00 4
2 1302000314319 48.00 4
3 1302000314319 84.50 3
4 1302000314319 230.00 3
5 1302000314319 360.00 3
6 1302000314319 600.00 2
7 1302000314319 1086.00 2
8 1302000314319 1150.00 2
9 1302000314319 1400.00 1
10 1302000314319 4222.50 1
11 1302000314319 5601.00 1
问题五：柱状图统计效果问题。
select aab001,
sum(aic142),
width_bucket(sum(aic142), 0, 500000, 10) as total
from ic13
group by aab001
order by sum(aic142)
注：巧用WIDTH_BUCKET(expression, minval expression, maxval expression, num buckets)函数。其中expression指的是要统计的值，minval指的是统计开始的最小值，maxval指的是统计结束的最大值，bucket指的是分几个区间段统计。比如1..100之间有100个数，如果要分成10个区间统计，最小值是0，最大值是100，则0-10属于第一个区间，11-20属于第二个区间段，以次类推。
问题六：分组统计并显示各个分组的合计值，相当于ms-sqlserver里面的compute函数。
select aab001, aic230, sum(aic142)
from ic13
group by rollup(aab001, aic230)
order by aab001, aic230
问题七：用字符表示数据的分布情况：
select employee_id,
last_name,
salary,
rpad('X', floor(salary / 1000), 'X') salary_graph
from hr.employees
order by salary desc;
1 100 King 24000.00 XXXXXXXXXXXXXXXXXXXXXXXX
2 101 Kochhar 17000.00 XXXXXXXXXXXXXXXXX
3 102 De Haan 17000.00 XXXXXXXXXXXXXXXXX
4 145 Russell 14000.00 XXXXXXXXXXXXXX
问题八：执行输出html代码。加入类似需要输入html代码时或者xml代码时，可以通过写一个sql语句生成。
select case rownum
when 1 then
'' || chr(10) || '' || chr(10) ||
'' || chr(10) ||
' body {background-color: Lavender;}' || chr(10) ||
' table.outer {width: 80%; border: Black solid 1px;}' || chr(10) ||
' table.inner {width: 100%; border: none;}' || chr(10) ||
' th {background-color: LightSteelBlue; color: WhiteSmoke;}' ||
chr(10) || ' .bar {background-color: LightSlateGray;}' || chr(10) ||
' .empty {background-color: Lavender;}' || chr(10) ||
' .left {text-align: left; padding-left: 5px;}' || chr(10) ||
' .right {text-align: right; padding-right: 5px;}' || chr(10) ||
'' || chr(10) || '' || chr(10) || '' ||
chr(10) || '' || chr(10) || '' ||
chr(10) || '' || chr(10) || 'Employee ID' ||
'
chr(10) || 'Last Name' || chr(10) ||
'Salary Graph ' || chr(10) || '
else
''
end || '' || to_char(employee_id) || '' ||
chr(10) || '' || last_name || '' || chr(10) ||
'' || '' || chr(10) ||
'
to_char(round(100 * salary / maxsalary)) || '%">' || chr(38) ||
'nbsp;' || chr(10) || '
to_char(100 - round(100 * salary / maxsalary)) || '%">' || chr(38) ||
'nbsp;
case rownum
when row_count then
'' || chr(10) || '' || chr(10) || '' ||
chr(10) || ''
else
''
end
from (select employee_id,
last_name,
salary,
count(*) over() as row_count,
max(salary) over() as maxsalary
from hr.employees
order by salary desc);
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7204674/viewspace-621612/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
dbms_logmnr日志分析常用语句
请登录后发表评论
登录
全部评论
