在PostgreSQL中
SQL语句:
select courseNo,score from student
where courseNo between '2001' and '2006'
and score between 93 and 99
查询结果:
courseNo score
2001 99
2002 98
2003 97
2004 95
2005 94
2006 93
其中studentNo是从前台穿过来的两个日期参数(开始日期和结束日期),这两个参数是不固定的,score从前台传的,同样不固定。怎样才能将
查询结果变成如下这样:
studentNo 2001 2002 2003 2004 2005 2006
score 99 98 97 95 94 93
SQL语句:
select courseNo,score from student
where courseNo between '2001' and '2006'
and score between 93 and 99
查询结果:
courseNo score
2001 99
2002 98
2003 97
2004 95
2005 94
2006 93
其中studentNo是从前台穿过来的两个日期参数(开始日期和结束日期),这两个参数是不固定的,score从前台传的,同样不固定。怎样才能将
查询结果变成如下这样:
studentNo 2001 2002 2003 2004 2005 2006
score 99 98 97 95 94 93
我MySQL之前写过一个存储过程,你可以照着写写看。
http://blog.csdn.net/yueliangdao0608/archive/2008/04/19/2306766.aspx
你可以通过 select count(*) 得到总行数吧?
declare @sql varchar(8000)
set @sql = 'select courseNo,'
select @sql = @sql + 'sum(case courseNo when '''+courseNo+'''
then score else 0 end) as '''+Name+''','
from (select distinct courseNo from student) as a
select @sql = left(@sql,len(@sql)-1) + ' from student group by courseNo'
Select distinct courseNo from student
打开游标,循环,累加字符串
select @sql = @sql + 'sum(case courseNo when '''+courseNo+'''
then score else 0 end) 最后执行,PostgreSQL没有用过
create table student(courseNo int,scroe int);--插入测试数据
insert into student values(2001,99);
insert into student values(2002,98);
insert into student values(2003,97);
insert into student values(2004,95);
insert into student values(2005,94);
insert into student values(2006,93);--调用crosstab函数进行行列转换
select *
from crosstab('select ''abc''::text as rowid,courseNo,scroe from student') as
ct(no_txt text,y_1 int,y_2 int,y_3 int,y_4 int,y_5 int,y_6 int);--结果-------------------------------------- no_txt | y_1 | y_2 | y_3 | y_4 | y_5 | y_6
--------+-----+-----+-----+-----+-----+-----
abc | 99 | 98 | 97 | 95 | 94 | 93
能分别说下详细的安装方法么?
#make
#make install 然后将 tablefunc.sql文件到入到数据库里边运行。
2、存储过程或程序肯定能够实现。
3、最终需要执行的 sql语句可以类似如下:select courseNo,max(case when courseNo = 2001 then score else 0 end) as score2001,max(case when courseNo = 2002 then score else 0 end) as score2002,max(case when courseNo = 2003 then score else 0 end) as score2003,..... from .... where .... group by courseNo,...;关键就是 “max(case when courseNo = 2001 then score else 0 end) as score2001,.... ” 这部分要动态生成。然后这条sql语句要能够动态执行。
postgre不太清楚有没有动态执行sql的功能(类似exec的语句)。其他:
可以用普通的sql语句执行,然后用程序实现横向化的过程。