我有一个表 :
有4个字段
姓名 t1 t2 t3 a 1 2 3
b 2 1 9
c 4 6 8以t1字段排名 权重40%
排名 姓名 t1 t2 t3 占比1 a 1 2 3 0.4
2 b 2 1 9 0.8
3 c 4 6 8 1.2以t2字段排名 权重40% 排名 姓名 t1 t2 t3 占比1 b 2 1 9 0.4
2 a 1 2 3 0.8
3 c 4 6 8 1.2以t3字段排名 权重20% 排名 姓名 t1 t2 t3 占比
1 a 1 2 3 0.4
2 c 4 6 8 0.8
3 b 2 1 9 1.2根据这3种排名,算出总排名
总排名=占比之和,最小的为第一,
请问我应该怎么写sql语句呢
有4个字段
姓名 t1 t2 t3 a 1 2 3
b 2 1 9
c 4 6 8以t1字段排名 权重40%
排名 姓名 t1 t2 t3 占比1 a 1 2 3 0.4
2 b 2 1 9 0.8
3 c 4 6 8 1.2以t2字段排名 权重40% 排名 姓名 t1 t2 t3 占比1 b 2 1 9 0.4
2 a 1 2 3 0.8
3 c 4 6 8 1.2以t3字段排名 权重20% 排名 姓名 t1 t2 t3 占比
1 a 1 2 3 0.4
2 c 4 6 8 0.8
3 b 2 1 9 1.2根据这3种排名,算出总排名
总排名=占比之和,最小的为第一,
请问我应该怎么写sql语句呢
解决方案 »
- oracle 自动执行job
- oracle监听的困惑
- 提高dbms_job.submit的执行效率(跪求高手指教)
- PLSQL在表空间中点右键DBMS_METADATA DDL修改的结果怎么保存
- 触发器 Oracle 多表级联删除
- 请问用< 或 > 号会用到索引吗?
- Oracle Datalink+XMLType问题,请高手帮忙
- 请教——创建存储过程的错误
- 【同一表重分组信息,取每组第一条记录,这样的select语句如何写?先谢谢各位!】
- 存储过程中使用EXECUTE IMMEDIATE提示权限不足,可是单独执行sql没有问题!
- 安装ORCLE11G R2 后 , 关于登陆企业管理的问题
- PL/SQL有自动备份数据库数据的功能吗
table e,
(select 权重*40% from table order by t1 ) a,
(select 权重*40% from table order by t2) b,
(select 权重*40% from table order by t3) c
order by 权重合
with tmp as
(
select 'a' as name, 1 t1, 2 t2, 3 t3 from dual
union all
select 'b' as name, 2 t1, 1 t2, 9 t3 from dual
union all
select 'c' as name, 4 t1, 6 t2, 8 t3 from dual
)
select name,
(rank() over (order by t1)) * 0.4 * t1 +
(rank() over (order by t2)) * 0.4 * t2 +
(rank() over (order by t3)) * 0.2 * t3 as qzpm
from tmp
order by qzpm
select 'a' name,1 t1, 2 t2, 3 t3 from dual union all
select 'b' name,2 t1, 1 t2, 9 t3 from dual union all
select 'c' name,4 t1, 6 t2, 8 t3 from dual)
SELECT rownum rn, NAME, t1, t2, t3 FROM (SELECT * FROM tt ORDER BY t1 * 0.4 + t2 * 0.4 + t3 * 0.2);
t1,
t2,
t3
FROM
(SELECT table1.*,
t1/SUM(t1)OVER()*0.4 +t2/SUM(t2)OVER()*0.4 +t3/SUM(t3)OVER()*0.2 AS flag
FROM table1
)
ORDER BY flag
with tt as (
select 'a' name,1 t1, 2 t2, 3 t3 from dual union all
select 'b' name,2 t1, 1 t2, 9 t3 from dual union all
select 'c' name,4 t1, 6 t2, 8 t3 from dual)
SELECT rownum, NAME, t1, t2, t3
FROM (SELECT a.NAME, a.t1, a.t2, a.t3
FROM (SELECT rownum * 0.4 q, NAME, t1, t2, t3 FROM (SELECT * FROM tt ORDER BY t1)) a,
(SELECT rownum * 0.4 q, NAME, t1, t2, t3 FROM (SELECT * FROM tt ORDER BY t2)) b,
(SELECT rownum * 0.2 q, NAME, t1, t2, t3 FROM (SELECT * FROM tt ORDER BY t3)) c
WHERE a.name = b.name AND
a.name = c.name
ORDER BY a.q + b.q + c.q)