行转列. 要求出每个人，2007-12-01到2007-12-5每一天的成绩， 如果这个这天有两个以上的成绩就求平均， 最后还要对这个五天的成绩再做平均 SQL执行后的结果集如下，请问SQL怎么写？ 人员编号 姓名 2007-12-01 2007-12-02 2007-12-03 2007-12-04 2007-12-05 1 a 35 10 - - - (注：2007-12-1这个人有两个成绩，要求平均,这天没有成绩的空着或者画一个‘-’) 2 b 20 3 c 50 4 d 5 e
create table #score(personID int , date datetime , score int )
insert into #score
select 1 , '2007-12-2 06:00:12', 10
union select 2 , '2007-12-1 00:12:12' , 20
union select 1 , '2007-12-1 12:12:20' , 30
union select 1 , '2007-12-1 12:05:05' , 40
union select 3 , '2007-12-5 04:04:05' , 50
create table #person (PersonID int, pName varchar(30))
insert into #person
select 1 ,'a'
union select 2 ,'b'
union select 3 ,'c'
union select 4 ,'d'
union select 5 ,'e'
/*
要求出每个人，2007-12-01到2007-12-5每一天的成绩，
如果这个这天有两个以上的成绩就求平均，
最后还要对这个五天的成绩再做平均
SQL执行后的结果集如下，请问SQL怎么写？
人员编号 姓名 2007-12-01 2007-12-02 2007-12-03 2007-12-04 2007-12-05
1 a 35 10 - - -
(注：2007-12-1这个人有两个成绩，要求平均,这天没有成绩的空着或者画一个‘-’)
2 b 20
3 c 50
4 d
5 e
*/
drop table #avgS
create table #avgS (personID int , date varchar(10) , score int )
insert into #avgS
select personID ,convert (varchar(30) ,date,110) ,avg(score) as s
from #score
group by personID ,convert (varchar(30) ,date,110)
select * from #avgS
//用临时表:
select v.personID,
sum(case when cast (v.date as varchar(10) ) = '12-01-2007'
then v.score else '0' end ) as "2007-12-1",
sum(case when cast (v.date as varchar(10) ) = '12-02-2007'
then v.score else '0' end ) as "2007-12-2" ,
sum(case when cast (v.date as varchar(10) ) = '12-03-2007'
then v.score else '0' end ) as "2007-12-3",
sum(case when cast (v.date as varchar(10) ) = '12-04-2007'
then v.score else '0' end ) as "2007-12-4",
sum(case when cast (v.date as varchar(10) ) = '12-05-2007'
then v.score else '0' end ) as "2007-12-5" ,
avg(v.score) as avg_score
from #avgS as v
group by v.personID
//结果:
personID 2007-12-1 2007-12-2 2007-12-3 2007-12-4 2007-12-5 avg_score
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 35 10 0 0 0 22
2 20 0 0 0 0 20
3 0 0 0 0 50 50
(3 行受影响)
//而不用临时表:
select v.personID,
sum(case when cast (v.date as varchar(10) ) = '12-01-2007'
then v.score else '0' end ) as "2007-12-1",
sum(case when cast (v.date as varchar(10) ) = '12-02-2007'
then v.score else '0' end ) as "2007-12-2" ,
sum(case when cast (v.date as varchar(10) ) = '12-03-2007'
then v.score else '0' end ) as "2007-12-3",
sum(case when cast (v.date as varchar(10) ) = '12-04-2007'
then v.score else '0' end ) as "2007-12-4",
sum(case when cast (v.date as varchar(10) ) = '12-05-2007'
then v.score else '0' end ) as "2007-12-5" ,
avg(v.score) as avg_score
from (
select personID ,cast (date as varchar(10) ) as date ,avg(score) as score
from #score
group by personID ,cast (date as varchar(10) )
) as v
group by v.personID
结果
personID 2007-12-1 2007-12-2 2007-12-3 2007-12-4 2007-12-5 avg_score
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 0 0 0 0 0 22
2 0 0 0 0 0 20
3 0 0 0 0 0 50
(3 行受影响)
现在出现的问题是: 用和不用临时表,结果是不一样的.
insert into #score
select 1 , '2007-12-2 06:00:12', 10
union select 2 , '2007-12-1 00:12:12' , 20
union select 1 , '2007-12-1 12:12:20' , 30
union select 1 , '2007-12-1 12:05:05' , 40
union select 3 , '2007-12-5 04:04:05' , 50
create table #person (PersonID int, pName varchar(30))
insert into #person
select 1 ,'a'
union select 2 ,'b'
union select 3 ,'c'
union select 4 ,'d'
union select 5 ,'e'
/*
要求出每个人，2007-12-01到2007-12-5每一天的成绩，
如果这个这天有两个以上的成绩就求平均，
最后还要对这个五天的成绩再做平均
SQL执行后的结果集如下，请问SQL怎么写？
人员编号 姓名 2007-12-01 2007-12-02 2007-12-03 2007-12-04 2007-12-05
1 a 35 10 - - -
(注：2007-12-1这个人有两个成绩，要求平均,这天没有成绩的空着或者画一个‘-’)
2 b 20
3 c 50
4 d
5 e
*/
drop table #avgS
create table #avgS (personID int , date varchar(10) , score int )
insert into #avgS
select personID ,convert (varchar(30) ,date,110) ,avg(score) as s
from #score
group by personID ,convert (varchar(30) ,date,110)
select * from #avgS
//用临时表:
select v.personID,
sum(case when cast (v.date as varchar(10) ) = '12-01-2007'
then v.score else '0' end ) as "2007-12-1",
sum(case when cast (v.date as varchar(10) ) = '12-02-2007'
then v.score else '0' end ) as "2007-12-2" ,
sum(case when cast (v.date as varchar(10) ) = '12-03-2007'
then v.score else '0' end ) as "2007-12-3",
sum(case when cast (v.date as varchar(10) ) = '12-04-2007'
then v.score else '0' end ) as "2007-12-4",
sum(case when cast (v.date as varchar(10) ) = '12-05-2007'
then v.score else '0' end ) as "2007-12-5" ,
avg(v.score) as avg_score
from #avgS as v
group by v.personID
//结果:
personID 2007-12-1 2007-12-2 2007-12-3 2007-12-4 2007-12-5 avg_score
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 35 10 0 0 0 22
2 20 0 0 0 0 20
3 0 0 0 0 50 50
(3 行受影响)
//而不用临时表:
select v.personID,
sum(case when cast (v.date as varchar(10) ) = '12-01-2007'
then v.score else '0' end ) as "2007-12-1",
sum(case when cast (v.date as varchar(10) ) = '12-02-2007'
then v.score else '0' end ) as "2007-12-2" ,
sum(case when cast (v.date as varchar(10) ) = '12-03-2007'
then v.score else '0' end ) as "2007-12-3",
sum(case when cast (v.date as varchar(10) ) = '12-04-2007'
then v.score else '0' end ) as "2007-12-4",
sum(case when cast (v.date as varchar(10) ) = '12-05-2007'
then v.score else '0' end ) as "2007-12-5" ,
avg(v.score) as avg_score
from (
select personID ,cast (date as varchar(10) ) as date ,avg(score) as score
from #score
group by personID ,cast (date as varchar(10) )
) as v
group by v.personID
结果
personID 2007-12-1 2007-12-2 2007-12-3 2007-12-4 2007-12-5 avg_score
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 0 0 0 0 0 22
2 0 0 0 0 0 20
3 0 0 0 0 0 50
(3 行受影响)
现在出现的问题是: 用和不用临时表,结果是不一样的.
 |
作者：NewSea 如无特别标记说明，均为NewSea原创，版权私有，翻载必纠。欢迎交流，转载，但要在页面明显位置给出原文连接。谢谢。 |
