17,140
社区成员




select greatest(tt.zs,tt.ls,tt.ww) from (
select sum(zhangsan) zs, sum(lisi) ls, sum(wangwu) ww from socre) tt;
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(T.张三,T.李四,T.王五) maxscore from (select sum(张三) 张三,sum(李四) 李四,sum(王五) 王五 from t_score) T
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
这个也很好,不过所有的方法都有一个毛病,就是如果有平分,没法查出两条记录。。WITH test AS(
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 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 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;