现在的问题是这样的,有一个视图
a:
SCORE SID QID
5 1 1
6 1 1
7 1 2
8 1 2
9 2 1
10 2 1
11 2 2
12 2 2我想得到这样一个视图,当SID 和 QID 都相等的时候的总得分,也就是
b:
SCORE SID QID
11 1 1
15 1 2
19 2 1
23 2 2请问应该怎么做?是需要分几步来实现呢,还是可以直接得到这样一个视图?
a:
SCORE SID QID
5 1 1
6 1 1
7 1 2
8 1 2
9 2 1
10 2 1
11 2 2
12 2 2我想得到这样一个视图,当SID 和 QID 都相等的时候的总得分,也就是
b:
SCORE SID QID
11 1 1
15 1 2
19 2 1
23 2 2请问应该怎么做?是需要分几步来实现呢,还是可以直接得到这样一个视图?
select sum(SCORE) SCORE , SID , QID from a group by SID , QID--视图
create view
as
select sum(SCORE) SCORE , SID , QID from a group by SID , QID
go
from view_a group by SID ,QID
insert into tb select 5,1,1
insert into tb select 6,1,1
insert into tb select 7,1,2
insert into tb select 8,1,2
insert into tb select 9,2,1
insert into tb select 10,2,1
insert into tb select 11,2,2
insert into tb select 12,2,2
go
select SUM(score)score,sid,qid from tb group by SID,qid
/*
score sid qid
----------- ----------- -----------
11 1 1
19 2 1
15 1 2
23 2 2(4 行受影响)*/
go
drop table tb
insert into a values(5 ,1 ,1)
insert into a values(6 ,1 ,1)
insert into a values(7 ,1 ,2)
insert into a values(8 ,1 ,2)
insert into a values(9 ,2 ,1)
insert into a values(10 ,2 ,1)
insert into a values(11 ,2 ,2)
insert into a values(12 ,2 ,2)
go--直接查询
select sum(SCORE) SCORE , SID , QID from a group by SID , QID order by SID , QID
/*
SCORE SID QID
----------- ----------- -----------
11 1 1
15 1 2
19 2 1
23 2 2(所影响的行数为 4 行)
*/
--创建视图
go
create view my_view
as
select sum(SCORE) SCORE , SID , QID from a group by SID , QID
go--通过视图查询数据
select * from my_view order by SID , QID
/*
SCORE SID QID
----------- ----------- -----------
11 1 1
15 1 2
19 2 1
23 2 2(所影响的行数为 4 行)
*/--删除视图
drop view my_viewdrop table a