SQL2.ru

Код: Выделить всё

CREATE TABLE test_tbl (
	u_name varchar NOT NULL,
	u_color varchar NULL
);

INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'RED');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'YELLOW');
INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'RED');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'YELLOW');
INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'GREEN');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'PERFECT');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'RED');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'GREEN');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'GREEN');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'YELLOW');
INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'RED');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'PERFECT');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'PERFECT');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'YELLOW');
INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'GREEN');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'RED');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'RED');
INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'GREEN');
INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'YELLOW');
INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'RED');

Код: Выделить всё

with
 RED as (select u_name, count(u_color) as color from test_tbl where u_color = 'RED' group by u_name),
 YELLOW as (select u_name, count(u_color) as color from test_tbl where u_color = 'YELLOW' group by u_name),
 GREEN as (select u_name, count(u_color) as color from test_tbl where u_color = 'GREEN' group by u_name),
 PURPLE as (select u_name, count(u_color) as color from test_tbl where u_color = 'PURPLE' group by u_name)
select RED.u_name, 
  coalesce(RED.color,0) as RED, 
  coalesce(YELLOW.color,0) as YELLOW, 
  coalesce(GREEN.color,0) as GREEN, 
  coalesce(PURPLE.color,0) as PURPLE
 from RED
  left join YELLOW on RED.u_name = YELLOW.u_name
  left join GREEN on YELLOW.u_name = GREEN.u_name
  left join PURPLE on GREEN.u_name = PURPLE.u_name
;

Код: Выделить всё

select
  u_name,
  --
  sum( iif(u_color = 'RED'     , 1, 0) ) as RED,
  sum( iif(u_color = 'YELLOW'  , 1, 0) ) as YELLOW,
  sum( iif(u_color = 'GREEN'   , 1, 0) ) as GREEN,
  sum( iif(u_color = 'PERFECT' , 1, 0) ) as PERFECT,
  sum( iif(u_color = 'PURPLE'  , 1, 0) ) as PURPLE

from
  test_tbl

group by
  u_name

Код: Выделить всё

select
  u_name,
  --
  sum ( case when u_color = 'RED'     then 1 else 0 end ) as RED,
  sum ( case when u_color = 'YELLOW'  then 1 else 0 end ) as YELLOW,
  sum ( case when u_color = 'GREEN'   then 1 else 0 end ) as GREEN,
  sum ( case when u_color = 'PERFECT' then 1 else 0 end ) as PERFECT,
  sum ( case when u_color = 'PURPLE'  then 1 else 0 end ) as PURPLE

from
  test_tbl

group by
  u_name
;  

Код: Выделить всё

+=========+=====+========+=======+=========+========+
| u_name  | red | yellow | green | perfect | purple |
+=========+=====+========+=======+=========+========+
| Petrov  | 2   | 3      | 1     | 1       | 0      |
| Sidorov | 2   | 1      | 2     | 2       | 0      |
| Ivanov  | 3   | 1      | 2     | 0       | 0      |
+---------+-----+--------+-------+---------+--------+

Код: Выделить всё

select
  case when u_name in ('Ivanov', 'Petrov') then u_name else 'Other' end  as u_name,
  --
  sum ( case when u_color = 'RED'     then 1 else 0 end ) as RED,
  sum ( case when u_color = 'YELLOW'  then 1 else 0 end ) as YELLOW,
  sum ( case when u_color = 'GREEN'   then 1 else 0 end ) as GREEN,
  sum ( case when u_color = 'PERFECT' then 1 else 0 end ) as PERFECT,
  sum ( case when u_color = 'PURPLE'  then 1 else 0 end ) as PURPLE

from test_tbl
group by 1
;

Код: Выделить всё

+========+=====+========+=======+=========+========+
| u_name | red | yellow | green | perfect | purple |
+========+=====+========+=======+=========+========+
| Other  | 2   | 1      | 2     | 2       | 0      |
| Petrov | 2   | 3      | 1     | 1       | 0      |
| Ivanov | 3   | 1      | 2     | 0       | 0      |
+--------+-----+--------+-------+---------+--------+

Код: Выделить всё

DROP SCHEMA IF EXISTS test CASCADE;
DROP ROLE IF EXISTS test;
commit;

CREATE USER test WITH 
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    LOGIN
    NOREPLICATION
    NOBYPASSRLS
    CONNECTION LIMIT -1
    PASSWORD 'qwerty';
CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION test;

CREATE TABLE test.tbl (
    tbl_id integer NOT NULL,
    tbl_name varchar(255) NOT NULL,
    CONSTRAINT tbl_pk PRIMARY KEY (tbl_id)
) WITH (
  OIDS=FALSE
);

CREATE TABLE test.tbl_h (
    tbl_id integer NOT NULL,
    tbl_name varchar(255) NOT NULL,
    tbl_h_dt TIMESTAMP
) WITH (
  OIDS=FALSE
);

CREATE OR REPLACE FUNCTION tbl_h_fnc ()
RETURNS trigger AS
$$
DECLARE

BEGIN
insert into test.tbl_h select t.tbl_id, t.tbl_name, current_timestamp from test.tbl t;
RETURN NULL;
END;

$$ LANGUAGE 'plpgsql';

CREATE TRIGGER tbl_h_trg
     BEFORE UPDATE ON test.tbl
     FOR EACH ROW EXECUTE PROCEDURE tbl_h_fnc();
     

ALTER TABLE test.tbl_h ADD CONSTRAINT tbl_h_fk FOREIGN KEY (tbl_id) REFERENCES test.tbl(tbl_id);


select * from test.tbl t;
select * from test.tbl_h th;

insert into test.tbl values (111, 'Test');

update test.tbl set tbl_name = 'BLABLABLA' where tbl_id = 111;
commit;
select * from test.tbl t;
select * from test.tbl_h th;