PostgreSQL-按兩列分組并使用一列作為結果列

我有兩個表：Subject和Journal如下：

Subject
 id | name
----------
  1 | fruit
  2 | drink
  3 | vege
  4 | fish

和

Journal
 id | subj | reference | value
------------------------------
  1 |    1 |       foo |    30
  2 |    2 |       bar |    20
  3 |    1 |       bar |    35
  4 |    1 |       bar |    10
  5 |    2 |       baz |    25
  6 |    4 |       foo |    30
  7 |    4 |       bar |    40
  8 |    1 |       baz |    20
  9 |    2 |       bar |     5

我想對Journal.valuegroupsubj和reference.

我知道該group by條款是為此目的，但我希望輸出如下：

reference | subj_1 | subj_2 | subj_3 | subj_4
          |  fruit |  drink |   vege |   fish (even better)
---------------------------------------------
      foo |     30 |        |        |     30
      bar |     45 |     25 |        |     40
      baz |     20 |     25 |        |

這可能嗎？

uj5u.com熱心網友回復：

這會產生您想要的結果：

SELECT *
FROM   crosstab(
   'SELECT reference, subj, sum(value)
    FROM   journal
    GROUP  BY 1, 2
    ORDER  BY 1, 2'

  , $$VALUES (1), (2), (3), (4)$$
   ) AS ct (reference text, fruit int, drink int, vege int, fish int);

db<>在這里擺弄

除了排序順序，哪個看起來很隨意？

詳細說明和說明：

• PostgreSQL 交叉表查詢

uj5u.com熱心網友回復：

可以根據當前資料生成Sql Statement。

然后使用生成的 Sql 陳述句

樣本資料：

create table Subject (
 id serial primary key, 
 name varchar(30) not null
 );

insert into Subject (id, name) values
 (1 ,'fruit')
,(2 ,'drink')
,(3 ,'vege')
,(4 ,'fish');
 
create table Journal (
 id int, 
 subj int, 
 reference varchar(30), 
 value int
);

insert into Journal   
(id, subj, reference, value) values
 (1, 1, 'foo', 30)
,(2, 2, 'bar', 20)
,(3, 1, 'bar', 35)
,(4, 1, 'bar' ,10)
,(5, 2, 'baz', 25)
,(6, 4, 'foo', 30)
,(7, 4, 'bar', 40)
,(8, 1, 'baz', 20)
,(9, 2, 'bar', 5);

生成陳述句：

SELECT $f$SELECT * FROM crosstab(
     $$SELECT DISTINCT ON (1, 2)
       j.reference, 'subj_'||j.subj||'_'||s.name AS data_type, SUM(j.value) AS val
       FROM Journal j
       JOIN Subject s ON s.id = j.subj
       GROUP BY j.reference, j.subj, s.name
       ORDER BY j.reference$$

    ,$$VALUES ($f$     || string_agg(quote_literal(data_type), '), (') || $f$)$$)
AS x (reference text, $f$ || string_agg(quote_ident(data_type), ' int, ') || ' int)'
AS Stmt
FROM  (SELECT concat('subj_', id, '_', name) AS data_type FROM Subject) x

| stmt |
| :------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------ |
| SELECT * FROM crosstab(<br> $$SELECT DISTINCT ON (1, 2)<br> j.reference, 'subj_'||j.subj||'_'||s.name AS data_type, SUM(j. value) AS val<br> FROM Journal j<br> JOIN Subject s ON s.id = j.subj<br> GROUP BY j.reference, j.subj, s.name<br> ORDER BY j.reference$$ <br><br> ,$$VALUES ('subj_1_fruit'), ('subj_2_drink'), ('subj_3_vege'), ('subj_4_fish')$$)<br>AS x (參考文本, subj_1_fruit int, subj_2_drink int , subj_3_vege int, subj_4_fish int) |

運行

SELECT * FROM crosstab(
     $$SELECT DISTINCT ON (1, 2)
       j.reference, 'subj_'||j.subj||'_'||s.name AS data_type, SUM(j.value) AS val
       FROM Journal j
       JOIN Subject s ON s.id = j.subj
       GROUP BY j.reference, j.subj, s.name
       ORDER BY j.reference$$

    ,$$VALUES ('subj_1_fruit'), ('subj_2_drink'), ('subj_3_vege'), ('subj_4_fish')$$)
AS x (reference text, subj_1_fruit int, subj_2_drink int, subj_3_vege int, subj_4_fish int)

參考 | subj_1_fruit | subj_2_drink | subj_3_vege | subj_4_fish
:-------- | -----------: | -----------: | ----------: | ----------：
酒吧| 45 | 25 |        空| 40
巴茲| 20 | 25 |        空|        空
foo | 30 |         空|        空| 30

db<>在這里擺弄

標籤：sql PostgreSQL的 枢

上一篇：如何使一行在一對多關系中唯一

下一篇：如何在列上設定約束和外鍵