3.
(1).select case when score<60 then '0<score<60'
when score>=60 and score<80 then '60<score<80'
when score>=80 then '80<score<100' end 成绩,
count(score) 人数
from chengji
group by case when score<60 then '0<score<60'
when score>=60 and score<80 then '60<score<80'
when score>=80 then '80<score<100' end;
--如果数据库没有<60的记录,就没有'0<score<60'这一行?
(2).select t.name,t.语文 语文,t.数学 数学,t.英语 英语,t.语文+t.数学+t.英语 总分
from (select name ,sum(decode(course,'语文',score,0)) as 语文,
sum(decode(course,'数学',score,0)) as 数学,
sum(decode(course,'英语',score,0)) as 英语
from chengji
group by name) t
--'总分'这一行怎么加上呢
(1).select case when score<60 then '0<score<60'
when score>=60 and score<80 then '60<score<80'
when score>=80 then '80<score<100' end 成绩,
count(score) 人数
from chengji
group by case when score<60 then '0<score<60'
when score>=60 and score<80 then '60<score<80'
when score>=80 then '80<score<100' end;
--如果数据库没有<60的记录,就没有'0<score<60'这一行?
(2).select t.name,t.语文 语文,t.数学 数学,t.英语 英语,t.语文+t.数学+t.英语 总分
from (select name ,sum(decode(course,'语文',score,0)) as 语文,
sum(decode(course,'数学',score,0)) as 数学,
sum(decode(course,'英语',score,0)) as 英语
from chengji
group by name) t
--'总分'这一行怎么加上呢
解决方案 »
- 有500G的文件入数据库库,表空间要多大才能确保完成入库?
- 求一个sql写法???
- 开发区,贴 了一天了,一个回复的都没有:c# 调用过程,报 {"ORA-01036: 非法的变量名/编号\n"}
- Oracle Database Congiguration 安装不成功
- linux下安装oracle,被占用16G空间!!!晕!
- 如何知道我的oracle数据库所能接受的最大连接数session?
- 进来说话!!!
- help!!!very easy!!在线等
- oracel develop 6i安装问题
- 救命呀。。。。。。。关于Oracle HTTP Server
- 11个,为什么我建立的JOB不自动运行?
- 想导出数据库用户所有对象的ddl语句,有啥好办法
2.
create or replace function GetCurrBal(qrp_rq IN VARCHAR2,qrp_code IN VARCHAR2)
return number
is
Result number;
begin
if qrp_code='RMB' then
begin
select RMB_YTD_BALANCE INTO Result FROM CCB_GYB WHERE ACCOUNTING_DATE=TO_DATE(qrp_rq,'yyyy-mm-dd');
end;
elsif qrp_code='CNY' then
begin
select CNY_YTD_BALANCE INTO Result FROM CCB_GYB WHERE ACCOUNTING_DATE=TO_DATE(qrp_rq,'yyyy-mm-dd');
end;
elsif qrp_code='USD' then
begin
select USD_YTD_BALANCE INTO Result FROM CCB_GYB WHERE ACCOUNTING_DATE=TO_DATE(qrp_rq,'yyyy-mm-dd');
end;
end if;
return(Result);
end GetCurrBal;
SELECT 成绩,count(case when 成绩>0 and 成绩<60 THEN 1 end) as good,
count(case when 成绩>60 and 成绩<80 THEN 1 end) fuck1,
count(case when 成绩>80 and 成绩<100 THEN 1 end) fuck1 from student group by 成绩;
SELECT * FROM student s;
搞了一个钟没搞出.睡觉;
sum(decode(学科,'语文', 成绩,0)) "语文",
sum(decode(学科,'数学', 成绩,0)) "数学",
sum(decode(学科,'英语', 成绩,0)) "英语",
SUM(成绩),
cast(avg(成绩*1.0) as decimal(18,2)) 平均分
from student
group by 姓名;
SELECT * FROM student s;
with aa as(
select '张三' name, '语文' class, 40 score from dual
union
select '张三' name, '数学' class, 86 score from dual
union
select '张三' name, '英语' class, 75 score from dual
union
select '李四' name, '语文' class, 78 score from dual
union
select '李四' name, '数学' class, 85 score from dual
union
select '李四' name, '英语' class, 78 score from dual
)
select count(1),cc from (
select (case
when (score>0 and score<60) then '成绩不及格'
when (score>60 and score<80) then '成绩及格'
when (score>80 and score<100) then '成绩优秀'
end )cc,name,class,score
from aa
) group by cc
2)应该是行转列,现在写的这个有点笨
with aa as(
select '张三' name, '语文' class, 40 score from dual
union
select '张三' name, '数学' class, 86 score from dual
union
select '张三' name, '英语' class, 75 score from dual
union
select '李四' name, '语文' class, 78 score from dual
union
select '李四' name, '数学' class, 85 score from dual
union
select '李四' name, '英语' class, 78 score from dual
)
select aa.name as 姓名,
sum(case class when '语文' then aa.score end)语文,
sum(case class when '数学' then aa.score end)数学,
sum(case class when '英语' then aa.score end)英语,
bb.totalscore
from aa,(
select sum(score) totalscore,name
from aa
group by name
)bb
where aa.name = bb.name
group by aa.name,bb.totalscore
qrp_code IN VARCHAR2 --币种
) return number IS
v_ye NUMBER;begin if qrp_rq is null OR UPPER(qrp_code) NOT IN ('RMB', 'CNY', 'USD') THEN
return null;
end if; execute immediate 'select ' || upper(qrp_code) ||
'_YTD_BALANCE from CCB_GYB t WHERE TO_CHAR(T.ACCOUNTING_DATE,''YYYYMMDDHH24MISS'') =' || '''' ||
qrp_rq || ''''
INTO V_YE;
return(v_ye);
exception when no_data_found then return null;end GetCurrBal;