Re: Pet Peeves?
От | Octavio Alvarez |
---|---|
Тема | Re: Pet Peeves? |
Дата | |
Msg-id | 1233517285.5647.83.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: Pet Peeves? (Octavio Alvarez <alvarezp@alvarezp.ods.org>) |
Список | pgsql-general |
On Sat, 2009-01-31 at 15:54 -0800, Octavio Alvarez wrote: > On Sat, 2009-01-31 at 23:36 +0000, Gregory Stark wrote: > > Octavio Alvarez <alvarezp@alvarezp.ods.org> writes: > > > > What about a WHERE clause like > > > > WHERE P1 > P2 > > You could either: > > (1) do "FROM grades AS g1 INNER JOIN grades AS g2 ON g1.P1 > g2.P2", > generating the record set before applying the crosstab transformation. Just to remove all the stupid things I said about the first solution to the WHERE P1 > P2 problem: Your grades table would be defined as: test=# \d grades Table "public.grades" Column | Type | Modifiers --------+-------------------+----------- st | character varying | su | character varying | p | bigint | gr | bigint | Indexes: "grades_st_key" UNIQUE, btree (st, p, su) st = student; su = subject; p = period; gr = grade The non-crosstab query that gives you the recordset for the crosstab, would be: SELECT p2_gt_p1.st, p2_gt_p1.su, grades.p, grades.gr FROM ( SELECT g1.st, g1.su, g1.p, g1.gr, g2.p, g2.gr FROM grades g1 INNER JOIN grades g2 ON g1.st = g2.st AND g1.su = g2.su AND g2.p = 2 AND g1.p = 1 AND g2.gr > g1.gr ) AS p2_gt_p1 LEFT JOIN grades USING (st, su);
В списке pgsql-general по дате отправления: