哦,我明白你的意思了。我就算省一场得三分,负一场不得分吧。用sql会很复杂,用存储过程就简单多了。不过有一点,你的表设计的不合理。我认为应该这样设计
球队表(id,球队名,球队id) //添加的id为自增一类型,很好实现,只要从1开始排列就行了,为了下面简单,免得用游标那么复杂。
赛程表(赛程id,主场球队id,客场球队id)
比赛成绩表(赛程id,球队id,得分)然后再建立一张专门放你要的结果的表
结果表(球队名,胜场数,负场数,主场负,客场负)然后我们的存储过程 stp定义为
as
v_i number;
v_id number; //假设你的球队id是number型
v_胜场数 number;
v_负场数 number;
v_主场负 number;
v_客场负 number;
v_球队名 number;
BEGIN
select count(*) into v_i from 球队表; for i in i..v_i loop//执行一个循环,让每个队都统计到
select 球队id into v_id from 球队表;
select count(*) into v_胜场数 from 比赛成绩表 where 球队id=v_id and 得分=3;
select count(*) into v_负场数 from 比赛成绩表 where 球队id=v_id and 得分=0;
select count(*) into v_主场负 from 比赛成绩表,赛程表 where 球队id=v_id and 得分=0 and 主场球队id = v_id;
select count(*) into v_客场负 from 比赛成绩表,赛程表 where 球队id=v_id and 得分=0 and 客场球队id = v_id;
select 球队名 into v_球队名 from 球队表;
insert into 结果表 values (v_球队名,v_胜场数,v_负场数,v_主场负,v_客场负)
end loop
commit;
exception
when others then
rollback;
raise;
end stp;
球队表(id,球队名,球队id) //添加的id为自增一类型,很好实现,只要从1开始排列就行了,为了下面简单,免得用游标那么复杂。
赛程表(赛程id,主场球队id,客场球队id)
比赛成绩表(赛程id,球队id,得分)然后再建立一张专门放你要的结果的表
结果表(球队名,胜场数,负场数,主场负,客场负)然后我们的存储过程 stp定义为
as
v_i number;
v_id number; //假设你的球队id是number型
v_胜场数 number;
v_负场数 number;
v_主场负 number;
v_客场负 number;
v_球队名 number;
BEGIN
select count(*) into v_i from 球队表; for i in i..v_i loop//执行一个循环,让每个队都统计到
select 球队id into v_id from 球队表;
select count(*) into v_胜场数 from 比赛成绩表 where 球队id=v_id and 得分=3;
select count(*) into v_负场数 from 比赛成绩表 where 球队id=v_id and 得分=0;
select count(*) into v_主场负 from 比赛成绩表,赛程表 where 球队id=v_id and 得分=0 and 主场球队id = v_id;
select count(*) into v_客场负 from 比赛成绩表,赛程表 where 球队id=v_id and 得分=0 and 客场球队id = v_id;
select 球队名 into v_球队名 from 球队表;
insert into 结果表 values (v_球队名,v_胜场数,v_负场数,v_主场负,v_客场负)
end loop
commit;
exception
when others then
rollback;
raise;
end stp;
解决方案 »
- 有mysql,mssql基础如何快速入门oracle?
- 有一道题a.time是时间字段 取表a的最近一周的最新的一条记录和表a的最近一周的最老的一条记录 各位 怎么算呀
- 插入多行数据的问题
- v sqlarea中的executions是什么意思
- 创建包、包体,为什么会报错啊!
- 问题1:请问把SESSION KILL 掉之后,而CPU的利用并没有被释放,怎么做能把CPU给释放掉? 问题2;这里有个SQL文很慢谁能帮我找找原因?很急,在线
- 想请教一下,关于一个JOB的问题
- 求一SQL,怎么算出库中所有表记录不为空的表
- 请问连表查询的sql语句怎么写!
- windows2000下的Oracle8.1.7的安装问题。
- 编译出错,请帮忙查看!
- 一个简单的sql语句
表:recode bout_id, team_name,team_id,scoreselect team_name,
sum(deocde(sign(score),1,1,0)),
sum(deocde(sign(score),-1,1,0)),
sum(deocde(sign(score),1,decode(hv,'H',1,0),0)),
sum(deocde(sign(score),-1,decode(hv,'H',1,0),0)),
sum(deocde(sign(score),1,decode(hv,'V',1,0),0)),
sum(deocde(sign(score),-1,decode(hv,'V',1,0),0))
from (
select a.bout_id, team_name, score -
lead(a.score,1,0) over(partition by a.bout_id order by a.team_id) -
lag(a.score,1,0) over(partition by a.bout_id order by a.team_id) score,
decode(host_team,a.team_id,'H','V') hv
FROM recode a, bout b
WHERE a.bout_id = b.bout_id
)
group by team_name
c.球队名,
sum(decode(c.得分,2,1,1,0)) as 胜场数,
sum(decode(c.得分,2,0,1,1)) as 负场数,
sum(decode(c.得分,2,1,1,0)*decode(c.球队id,c.主场球队id,1,0)) as 主场胜场数,
sum(decode(c.得分,2,0,1,1)*decode(c.球队id,c.主场球队id,1,0)) as 主场负场数,
sum(decode(c.得分,2,1,1,0)*decode(c.球队id,c.客场球队id,1,0)) as 客场胜场数,
sum(decode(c.得分,2,0,1,1)*decode(c.球队id,c.客场球队id,1,0)) as 客场负场数
from
(select
a.球队名,
a.球队id,
b.主场球队id,
b.客场球队id,
a.得分
from
比赛成绩表 a,
赛程表 b
where
a.赛程id = b.赛程id) c
group by
c.球队名
0, 球队名, NULL)) AS 负, COUNT(DECODE("主1/客0", 1, DECODE(得分, 3,
球队名, NULL), NULL)) AS 主场胜, COUNT(DECODE("主1/客0", 1, DECODE(得分, 0,
球队名, NULL), NULL)) AS 主场负, COUNT(DECODE("主1/客0", 0, DECODE(得分, 3,
球队名, NULL), NULL)) AS 客场胜, COUNT(DECODE("主1/客0", 0, DECODE(得分, 0,
球队名, NULL), NULL)) AS 客场负
FROM (SELECT 赛程ID, 球队名, 球队id, 得分,
(SELECT COUNT(主场球队id)
FROM 赛程表
WHERE 赛程表.主场球队id = 比赛成绩表.球队id AND 赛程表.赛程id = 比赛成绩表.赛程ID)
AS "主1/客0"
FROM 比赛成绩表)
GROUP BY 球队名
SELECT 球队名,
COUNT(DECODE(得分, 3, 球队名, NULL)) AS 胜,
COUNT(DECODE(得分, 0, 球队名, NULL)) AS 负,
COUNT(DECODE("主1/客0", 1, DECODE(得分, 3,球队名, NULL), NULL)) AS 主场胜,
COUNT(DECODE("主1/客0", 1, DECODE(得分, 0,球队名, NULL), NULL)) AS 主场负,
COUNT(DECODE("主1/客0", 0, DECODE(得分, 3,球队名, NULL), NULL)) AS 客场胜,
COUNT(DECODE("主1/客0", 0, DECODE(得分, 0,球队名, NULL), NULL)) AS 客场负
FROM (SELECT 赛程ID, 球队名, 球队id, 得分,
(SELECT COUNT(主场球队id)
FROM 赛程表
WHERE 赛程表.主场球队id = 比赛成绩表.球队id AND
赛程表.赛程id = 比赛成绩表.赛程ID) AS "主1/客0"
FROM 比赛成绩表)
GROUP BY 球队名
----------------------------------------
估计楼主所说的得分是指球场上的比分。
create table 表a(aid int,zid int,kid int);
insert into 表a values(1,1,2);
insert into 表a values(2,1,3);
insert into 表a values(3,2,1);
insert into 表a values(4,2,3);
insert into 表a values(5,3,1);
insert into 表a values(6,3,2);
create table 表b(aid int,did int,score int);
insert into 表b values(1,1,100);
insert into 表b values(1,2,88);
insert into 表b values(2,1,100);
insert into 表b values(2,3,90);
insert into 表b values(3,2,96);
insert into 表b values(3,1,102);
insert into 表b values(4,2,100);
insert into 表b values(4,3,112);
insert into 表b values(5,3,100);
insert into 表b values(5,1,101);
insert into 表b values(6,3,79);
insert into 表b values(6,2,80);--球队名 胜场 负场数 主场胜-负 客场胜-负
create table 表c as
select a.aid,a.zid,b.score,a.kid,(select score from 表b where aid = a.aid and did = a.kid) kscore
from 表a a,表b b
where a.aid = b.aid and a.zid = b.did ;select * from 表c;
/* AID ZID SCORE KID KSCORE
---------- ---------- ---------- ---------- ----------
1 1 100 2 88
2 1 100 3 90
3 2 96 1 102
4 2 100 3 112
5 3 100 1 101
6 3 79 2 80已选择6行。
*/
select zid 队名,(select count(*) from 表c where (zid = c.zid and score>kscore) or (kid =c.zid and score<kscore)) 胜场
,(select count(*) from 表c where (zid = c.zid and score<kscore) or (kid =c.zid and score>kscore)) 负场
,(select count(*) from 表c where (zid = c.zid and score>kscore)) 主场胜,(select count(*) from 表c where (zid = c.zid and score<kscore)) 主场负
,(select count(*) from 表c where (kid = c.zid and kscore>score)) 客场胜,(select count(*) from 表c where (kid = c.zid and kscore<score)) 客场负
from 表c c group by zid;/*
队名 胜场 负场 主场胜 主场负 客场胜 客场负
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 4 0 2 0 2 0
2 1 3 0 2 1 1
3 1 3 0 2 1 1已选择3行。
*/
drop table 表a;
drop table 表b;
drop table 表c;
insert into 表a values(1,1,2);
insert into 表a values(2,1,3);
insert into 表a values(3,2,1);
insert into 表a values(4,2,3);
insert into 表a values(5,3,1);
insert into 表a values(6,3,2);
create table 表b(赛程id int,球队id int,得分 int);
insert into 表b values(1,1,100);
insert into 表b values(1,2,88);
insert into 表b values(2,1,100);
insert into 表b values(2,3,90);
insert into 表b values(3,2,96);
insert into 表b values(3,1,102);
insert into 表b values(4,2,100);
insert into 表b values(4,3,112);
insert into 表b values(5,3,100);
insert into 表b values(5,1,101);
insert into 表b values(6,3,79);
insert into 表b values(6,2,80);--球队名 胜场 负场数 主场胜-负 客场胜-负
create table 表c as
select a.赛程id,a.主场球队id,b.得分 主队得分,a.客场球队id,(select 得分 from 表b where 赛程id = a.赛程id and 球队id = a.客场球队id) 客队得分
from 表a a,表b b
where a.赛程id = b.赛程id and a.主场球队id = b.球队id ;select * from 表c;
/* 赛程ID 主场球队ID 主队得分 客场球队ID 客队得分
---------- ---------- ---------- ---------- ----------
1 1 100 2 88
2 1 100 3 90
3 2 96 1 102
4 2 100 3 112
5 3 100 1 101
6 3 79 2 80已选择6行。
*/
select 主场球队id 队名,(select count(*) from 表c where (主场球队id = c.主场球队id and 主队得分>客队得分) or (客场球队id =c.主场球队id and 主队得分<客队得分)) 胜场
,(select count(*) from 表c where (主场球队id = c.主场球队id and 主队得分<客队得分) or (客场球队id =c.主场球队id and 主队得分>客队得分)) 负场
,(select count(*) from 表c where (主场球队id = c.主场球队id and 主队得分>客队得分)) 主场胜,(select count(*) from 表c where (主场球队id = c.主场球队id and 主队得分<客队得分)) 主场负
,(select count(*) from 表c where (客场球队id = c.主场球队id and 客队得分>主队得分)) 客场胜,(select count(*) from 表c where (客场球队id = c.主场球队id and 客队得分<主队得分)) 客场负
from 表c c group by 主场球队id;/* 队名 胜场 负场 主场胜 主场负 客场胜 客场负
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 4 0 2 0 2 0
2 1 3 0 2 1 1
3 1 3 0 2 1 1已选择3行。
*/
drop table 表a;
drop table 表b;
drop table 表c;