Упростил всё что бы нагляднее.
три таблицы date price sold sold имет ключи на две остальных. расматриваемый пример кол-во продаваемого всегда 1 для простоты. Вот дамп.
| Код |
CREATE TABLE testing.date
(
id bigint NOT NULL,
date bigint NOT NULL,
CONSTRAINT pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
|
| Код |
CREATE TABLE testing.price
(
id bigint NOT NULL,
price bigint NOT NULL,
CONSTRAINT price_pkey PRIMARY KEY (id)
)
|
| Код |
CREATE TABLE testing.sold
(
id bigint NOT NULL,
date bigint NOT NULL,
quantity bigint NOT NULL,
price bigint NOT NULL,
CONSTRAINT sold_pkey PRIMARY KEY (id),
CONSTRAINT sold_date_fkey FOREIGN KEY (date)
REFERENCES testing.date (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT sold_price_fkey FOREIGN KEY (price)
REFERENCES testing.price (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
|
| Код |
COPY date (id, date) FROM stdin;
1 5
2 10
3 15
\.
|
| Код |
INSERT INTO price (id, price) VALUES (1, 11);
INSERT INTO price (id, price) VALUES (2, 22);
INSERT INTO price (id, price) VALUES (3, 44);
INSERT INTO price (id, price) VALUES (4, 55);
|
| Код |
INSERT INTO sold (id, date, quantity, price) VALUES (1, 1, 1, 1);
INSERT INTO sold (id, date, quantity, price) VALUES (2, 1, 1, 2);
INSERT INTO sold (id, date, quantity, price) VALUES (3, 1, 1, 3);
INSERT INTO sold (id, date, quantity, price) VALUES (4, 1, 1, 1);
INSERT INTO sold (id, date, quantity, price) VALUES (5, 2, 1, 2);
INSERT INTO sold (id, date, quantity, price) VALUES (6, 2, 1, 1);
INSERT INTO sold (id, date, quantity, price) VALUES (7, 2, 1, 4);
INSERT INTO sold (id, date, quantity, price) VALUES (8, 3, 1, 1);
INSERT INTO sold (id, date, quantity, price) VALUES (9, 3, 1, 2);
INSERT INTO sold (id, date, quantity, price) VALUES (10, 3, 1, 2);
|
Итак. первый запрос для даты 1
select sum(t_price.price) as sum from (select price.price from testing.sold LEFT JOIN testing.price ON (price.id=sold.price) where sold.date=1) as t_price;
получаем 88.
делаем in и вставляем дату. получаем буэ
select sold.date,sum(t_price.price) as sum from testing.sold,(select price.price from testing.sold LEFT JOIN testing.price ON (price.id=sold.price) where sold.date IN (select date.id from testing.date)) as t_price GROUP BY sold.date;
date | sum
------+-----
2 | 693
3 | 693
1 | 924
(3 rows)
А счастье было так близко. |
)