Помогите с sql-запросом

[Newbee]

Всем привет!

Давно не брал SQL в руки, но все циклично.
Есть вот такая табличка:

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

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');

Нужен запрос, чтобы получить сумму цветов для каждой фамилии:
Name/Color|RED|YELLOW|GREEN|PURPLE
Ivanov | 3 | 1 | 2 | 0
Petrov | 2 | 3 | 1 | 1
Sidorov| 2 | 1 | 2 | 2

[zdn]

Возможно так:

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

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
;

[fraks]

В первичных данных нет цвета PURPLE, вместо него написан PERFECT.

Запрос может выглядеть так (синтаксис Firebird, для другого сервера может потребоваться заменить IIF на CASE или чего там еще есть):

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

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

[fraks]

Вот вариант для простгресса.

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

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      |
+---------+-----+--------+-------+---------+--------+

[Newbee]

Земной поклон за помощь!

[Newbee]

А если предположить, что помимо Иванова, Петрова и Сидорова есть и другие фамилии, то можно их как-то объединить, например под "Остальные" и суммарно посчитать цвета и для них?
Конструкция "case when u_name in ('Ivanov', 'Petrov', 'Sidorov') then u_name else 'Other' end ) as u_name" явно не прокатит, так как все фамилии не из списка будут заменены на Other, но группировки и суммирования не произойдет.

[fraks]

Конструкция "case when u_name in ('Ivanov', 'Petrov', 'Sidorov') then u_name else 'Other' end ) as u_name" явно не прокатит, так как все фамилии не из списка будут заменены на Other, но группировки и суммирования не произойдет.

По какому полю сгруппируешь - по тому и будет. Группировать можно не только по полю но и по выражению.

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

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      |
+--------+-----+--------+-------+---------+--------+

[Newbee]

Спасибо! Забыл что можно группировать по порядковому номеру, а не названию поля.

[Newbee]

Всем доброго времени суток!

Продолжаю быть начинающим в PostgreSQL. Случилась крайне странная ситуация - не срабатывает команда update.
Для теста создал схему, в ней две таблицы - tbl и tbl_h, на обновление tbl повесил триггер - он перекидывает предыдущие значения в таблицу tbl_h (историческая). Запускаю update и commit (на всякий случай) - update "проходит", но в основной таблице ничего не меняется, а триггер срабатывает и добавляет строку в таблицу с историей.
Пытаюсь разобраться в чем проблема. Подскажите новичку куда копать.

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

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;

P.S. Все тесты гоняю в DBeaver 22.2.4.

[Newbee]

Нашел сам.
В функции надо возвращать значение NEW, а не NULL.