KADB行列转换

发布于:2025-07-06 ⋅ 阅读:(19) ⋅ 点赞:(0)

create table row2col(name varchar(20),subject varchar(20),grade varchar(10));

insert into row2col SELECT 'Tim' name, '数学' subject, 'A' grade

UNION

SELECT 'Tim' name, '英语' subject, 'B' grade

UNION

SELECT 'Tim' name, '语文' subject, null grade

UNION

SELECT 'Tom' name, '数学' subject, 'B' grade

UNION

SELECT 'Tom' name, '英语' subject, 'D' grade

UNION

SELECT 'Tom' name, '语文' subject, 'B' grade;

行转列:

select t.name,t.grades[1] 数学,t.grades[2] 英语,t.grades[3] 语文 from(

SELECT

    t.name,

    array_agg(t.grade order by t.subject) grades

FROM row2col t group by t.name)t;

name | 数学 | 英语 | 语文

------+------+------+------

 Tim  | A    | B    |

 Tom  | B    | D    | B

列转行:

select name,unnest(array['数学','英语','语文']),unnest(array[数学,英语,语文]) from (select t.name,t.grades[1] 数学,t.grades[2] 英语,t.grades[3] 语文 from(

SELECT

    t.name,

    array_agg(t.grade order by t.subject) grades

FROM row2col t group by t.name)t

)a;

name | unnest | unnest

------+--------+--------

 Tim  | 数学   | A

 Tim  | 英语   | B

 Tim  | 语文   |

 Tom  | 数学   | B

 Tom  | 英语   | D

 Tom  | 语文   | B

2、递归的实现方式

CREATE TABLE employees (

   employee_id serial PRIMARY KEY,

   full_name VARCHAR NOT NULL,

   manager_id INT

);

INSERT INTO employees (

   employee_id,

   full_name,

   manager_id

)

VALUES

   (1, 'Michael North', NULL),

   (2, 'Megan Berry', 1),

   (3, 'Sarah Berry', 1),

   (4, 'Zoe Black', 1),

   (5, 'Tim James', 1),

   (6, 'Bella Tucker', 2),

   (7, 'Ryan Metcalfe', 2),

   (8, 'Max Mills', 2),

   (9, 'Benjamin Glover', 2),

   (10, 'Carolyn Henderson', 3),

   (11, 'Nicola Kelly', 3),

   (12, 'Alexandra Climo', 3),

   (13, 'Dominic King', 3),

   (14, 'Leonard Gray', 4),

   (15, 'Eric Rampling', 4),

   (16, 'Piers Paige', 7),

   (17, 'Ryan Henderson', 7),

   (18, 'Frank Tucker', 8),

   (19, 'Nathan Ferguson', 8),

   (20, 'Kevin Rampling', 8);

返回id2的经理的所有下属

WITH RECURSIVE subordinates AS (

   SELECT

      employee_id,

      manager_id,

      full_name

   FROM

      employees

   WHERE

      employee_id = 2

   UNION

      SELECT

         e.employee_id,

         e.manager_id,

         e.full_name

      FROM

         employees e

      INNER JOIN subordinates s ON s.employee_id = e.manager_id

) SELECT

   *

FROM

   subordinates;

employee_id | manager_id |    full_name   

-------------+------------+-----------------

           2 |          1 | Megan Berry

           9 |          2 | Benjamin Glover

           8 |          2 | Max Mills

           7 |          2 | Ryan Metcalfe

           6 |          2 | Bella Tucker

          19 |          8 | Nathan Ferguson

          18 |          8 | Frank Tucker

          20 |          8 | Kevin Rampling

          16 |          7 | Piers Paige

          17 |          7 | Ryan Henderson