|
employees表
问题:去除重复记录（工种相同的记录即为重复记录）,重复记录只留雇佣日期最早的员工的信息(如果工种相同，雇佣日期相同就都留下)。
select t.*,row_number() over(partition by job_id order by hire_date desc) rn from employees t
select * from(select t.*,row_number() over(partition by job_id order by hire_date desc) rn from employees t) where rn=1;
取得工种重复的记录中雇佣日期最早的员工的信息
keep(dense_rank first order by commission_pct) over(partition by department_id )
keep(dense_rank last order by commisssion_pct) over(partition by department_id)
SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id IN (30, 60)
ORDER BY department_id, salary;
需求:随机数据抽样调查前10条记录
oracle：select * from(select * from employees order by dbms_random.value()) where rownum<10
db2:select * from employees order by rand() fetch first 10 rows only
mysql:select * from employees order by rand() limit 10
sqlserver：select top 10 from(select * from employees order by newid())
空值转换函数以及空值排序:
select first_name,coalesce(manager_id,0) from employees where salary =24000 (通用)
select first_name,nvl(manager_id,0) from employees where salary=24000(oracle特有)
select * from(
select first_name,manager_id,decode(manager_id,null,0,1) as isnull from employees) order by isnull asc
通过控制isnull字段升序，降序来排序
截取字符串函数（常用一定要记牢）
select first_name,substr(first_name,length(first_name)-1) newname from employees order by newname从倒数第二个字符串截取到最后其中length(coal)返回列长度
select first_name,substr(first_name,2,5) newname from employees 从第2个字符串开始截取5个字符串（从1开始）
对于sql server substring(first_name,len(first_name)-2,2)
| 
转播
