交叉表函数演示

时间：2010-04-08 来源：osdba

有一表结构如下：
day             equipment    output
2010/4/1       DAT501          100
2010/4/1       DAT502          120
2010/4/2       DAT501          110
2010/4/2       DAT502          105

查询结果：
day                DAT501    DAT502
2010/4/1       100          120
2010/4/2       110          105

这个查询怎么实现？ SELECT *
FROM crosstab(
'select day, equipment, output
   from t
   where equipment = ''DAT501'' or equipment = ''DAT502''
   order by 1,2')
AS t(day date, DAT501 integer, DAT502 integer); 注意需要安装contrib下的tablefunc模块后才会有crosstab函数。见我的演示：
root@postgres1 /usr/src/postgresql-8.4.3/contrib/tablefunc]#su - postgres
[postgres@postgres1 ~]$ cd /usr/src/postgresql-8.4.3/contrib/tablefunc
[postgres@postgres1 tablefunc]$ ls
Makefile data expected sql tablefunc.c tablefunc.h tablefunc.so tablefunc.sql tablefunc.sql.in uninstall_tablefunc.sql
[postgres@postgres1 tablefunc]$ psql -f tablefunc.sql
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
[postgres@postgres1 tablefunc]$ psql
psql (8.4.3)
Type "help" for help. create table t(day date,equipment varchar(20),output integer);
insert into t values('2010-04-01','DAT501',100);
insert into t values('2010-04-01','DAT502',120);
insert into t values('2010-04-02','DAT501',110);
insert into t values('2010-04-02','DAT502',105);
postgres=# SELECT *
postgres-# FROM crosstab(
postgres(#   'select day, equipment, output
postgres'#    from t
postgres'#    where equipment = ''DAT501'' or equipment = ''DAT502''
postgres'#    order by 1,2')
postgres-#   AS t(day date, DAT501 integer, DAT502 integer);
    day     | dat501 | dat502
------------+--------+--------
2010-04-01 |    100 |    120
2010-04-02 |    110 |    105
(2 rows)

相关阅读更多 +