Решение для Firebird 2.5.
Скрипт для создания БД, таблиц, первиных и вторичных ключей, а так же заполнения тестовыми данными.
| Код |
SET AUTODDL ON;
CONNECT '/home/Gluttton/Projects/databases/firebird/VingradPashaLostCards.fdb'
USER 'SYSDBA' PASSWORD 'masterkey';
DROP DATABASE;
SET SQL DIALECT 3;
SET NAMES UTF8;
CREATE DATABASE '/home/Gluttton/Projects/databases/firebird/VingradPashaLostCards.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384 DEFAULT CHARACTER SET UTF8;
CREATE DOMAIN ID AS BIGINT NOT NULL;
CREATE DOMAIN INFO AS VARCHAR (32);
CREATE TABLE GAMES
(
ID ID,
INFO INFO
);
CREATE TABLE ROUNDS
(
ID ID,
GAME_ID ID,
INFO INFO
);
CREATE TABLE CARDS
(
ID ID,
ROUND_ID ID,
INFO INFO
);
COMMIT;
ALTER TABLE GAMES ADD CONSTRAINT PK_GAMES PRIMARY KEY (ID);
ALTER TABLE ROUNDS ADD CONSTRAINT PK_ROUNDS PRIMARY KEY (ID);
ALTER TABLE CARDS ADD CONSTRAINT PK_CARDS PRIMARY KEY (ID);
COMMIT;
ALTER TABLE ROUNDS ADD CONSTRAINT FK_GAMES_ROUNDS FOREIGN KEY (GAME_ID) REFERENCES GAMES (ID);
ALTER TABLE CARDS ADD CONSTRAINT FK_ROUNDS_CARDS FOREIGN KEY (ROUND_ID) REFERENCES ROUNDS (ID);
COMMIT;
INSERT INTO GAMES (ID, INFO) VALUES (1, '1');
INSERT INTO GAMES (ID, INFO) VALUES (2, '2');
INSERT INTO GAMES (ID, INFO) VALUES (3, '3');
INSERT INTO GAMES (ID, INFO) VALUES (4, '4');
COMMIT;
INSERT INTO ROUNDS (ID, GAME_ID, INFO) VALUES (1, 1, '1');
INSERT INTO ROUNDS (ID, GAME_ID, INFO) VALUES (2, 1, '2');
INSERT INTO ROUNDS (ID, GAME_ID, INFO) VALUES (3, 2, '3');
COMMIT;
INSERT INTO CARDS (ID, ROUND_ID, INFO) VALUES (1, 1, '1');
INSERT INTO CARDS (ID, ROUND_ID, INFO) VALUES (2, 1, '2');
INSERT INTO CARDS (ID, ROUND_ID, INFO) VALUES (3, 2, '3');
COMMIT;
|
Запрос:
| Код |
select
GAMES.INFO as Game,
R1.INFO as Round_I,
R1.CARDS as Cards_I,
R2.INFO as Round_II,
R2.CARDS as Cards_II,
R3.INFO as Round_III,
R3.CARDS as Cards_III,
R4.INFO as Round_IV,
R4.CARDS as Cards_IV
from GAMES
left join (
select
ROUNDS.GAME_ID,
ROUNDS.INFO,
list (CARDS.INFO) as CARDS
from ROUNDS left join CARDS
on ROUNDS.ID = CARDS.ROUND_ID
where ROUNDS.INFO = '1'
group by ROUNDS.GAME_ID, ROUNDS.INFO
) as R1
on GAMES.ID = R1.GAME_ID
left join (
select
ROUNDS.GAME_ID,
ROUNDS.INFO,
list (CARDS.INFO) as CARDS
from ROUNDS left join CARDS
on ROUNDS.ID = CARDS.ROUND_ID
where ROUNDS.INFO = '2'
group by ROUNDS.GAME_ID, ROUNDS.INFO
) as R2
on GAMES.ID = R2.GAME_ID
left join (
select
ROUNDS.GAME_ID,
ROUNDS.INFO,
list (CARDS.INFO) as CARDS
from ROUNDS left join CARDS
on ROUNDS.ID = CARDS.ROUND_ID
where ROUNDS.INFO = '3'
group by ROUNDS.GAME_ID, ROUNDS.INFO
) as R3
on GAMES.ID = R3.GAME_ID
left join (
select
ROUNDS.GAME_ID,
ROUNDS.INFO,
list (CARDS.INFO) as CARDS
from ROUNDS left join CARDS
on ROUNDS.ID = CARDS.ROUND_ID
where ROUNDS.INFO = '4'
group by ROUNDS.GAME_ID, ROUNDS.INFO
) as R4
on GAMES.ID = R4.GAME_ID
|
Результат.
| Цитата |
GAME ROUND_I CARDS_I ROUND_II CARDS_II ROUND_III CARDS_III ROUND_IV CARDS_IV
1 1 1,2 2 3 NULL NULL NULL NULL
2 NULL NULL NULL NULL 3 NULL NULL NULL
3 NULL NULL NULL NULL NULL NULL NULL NULL
4 NULL NULL NULL NULL NULL NULL NULL NULL
|
Но назвать решение изящным язык не аж никак не поварачивается... |
