解决方案 »
- 通过PL/SQL Developer中的ODBC Importer导数据时发生如下错误,各位高手帮忙看看什么原因?
- 一个触发器的编译错误
- oracle developer连接oracle数据库出现the network adapter cannot establish the connection
- 触发器 能否更新某个字段
- 怎样给已建oracle表创建分区
- 这个SQL语句怎么写???
- 如何把sqlsever 数据库移植到 oracle当中去
- dbms_job.submit问题
- 有关ORACLE数据库环境不同的数据记录比较的问题
- 如何在sql*plus下创建用户,表空间和表!再线等待!分不够可以在加!
- oracle如何实现让一条数据的每个列的值和每个列的名字变成一行行的输出!!!详细请看内容
- 请教一个行转列的问题,期待高手
(SELECT 'yuwen' KEMU, 98 ZHANGSAN, 80 LISI, 70 WANGWU
FROM DUAL
UNION ALL
SELECT 'shuxue' KEMU, 99 ZHANGSAN, 70 LISI, 60 WANGWU
FROM DUAL
UNION ALL
SELECT 'yingyu' KEMU, 70 ZHANGSAN, 88 LISI, 77 WANGWU
FROM DUAL)
SELECT TOTAL_SCO, NM
FROM (SELECT SUM(ZHANGSAN) TOTAL_SCO, 'zhansan' NM
FROM T
UNION ALL
SELECT SUM(LISI), 'lisi' NM
FROM T
UNION ALL
SELECT SUM(WANGWU), 'wangwu' NM
FROM T)
WHERE ROWNUM = 1
ORDER BY TOTAL_SCO DESC;
SELECT '语文' 科目, 98 张三, 80 李四, 70 王五 FROM DUAL UNION ALL
SELECT '数学', 99, 70, 60 FROM DUAL UNION ALL
SELECT '英语', 70, 88, 77 FROM DUAL)
SELECT (CASE
WHEN SIGN(SUM(张三) - SUM(李四)) + SIGN(SUM(张三) - SUM(王五)) = 2 THEN
'张三'
WHEN SIGN(SUM(李四) - SUM(张三)) + SIGN(SUM(李四) - SUM(王五)) = 2 THEN
'李四'
ELSE
'王五'
END) 姓名
FROM TEST;
WITH test AS(
SELECT '语文' 科目, 98 张三, 98 李四, 70 王五 FROM DUAL UNION ALL
SELECT '数学', 99, 99, 60 FROM DUAL UNION ALL
SELECT '英语', 70, 70, 77 FROM DUAL)
SELECT DECODE(GREATEST(SUM(张三) ,SUM(李四), SUM(王五)),
SUM(张三),
'张三',
SUM(李四),
'李四',
SUM(王五),
'王五') name
FROM TEST
这个也很好,不过所有的方法都有一个毛病,就是如果有平分,没法查出两条记录。。
select greatest(T.张三,T.李四,T.王五) maxscore from (select sum(张三) 张三,sum(李四) 李四,sum(王五) 王五 from t_score) T
WITH T AS
(SELECT 'yuwen' KEMU, 98 ZHANGSAN, 80 LISI, 70 WANGWU
FROM DUAL
UNION ALL
SELECT 'shuxue' KEMU, 99 ZHANGSAN, 70 LISI, 60 WANGWU
FROM DUAL
UNION ALL
SELECT 'yingyu' KEMU, 70 ZHANGSAN, 88 LISI, 77 WANGWU
FROM DUAL)
SELECT NM FROM(
SELECT TOTAL_SCO, NM,rank() over(order by TOTAL_SCO desc) S
FROM (SELECT SUM(ZHANGSAN) TOTAL_SCO, 'zhansan' NM
FROM T
UNION ALL
SELECT SUM(LISI), 'lisi' NM
FROM T
UNION ALL
SELECT SUM(WANGWU), 'wangwu' NM
FROM T)) WHERE S=1
用2L改的
select greatest(tt.zs,tt.ls,tt.ww) from (
select sum(zhangsan) zs, sum(lisi) ls, sum(wangwu) ww from socre) tt;