У меня есть 5 таблиц:
идентификатор клиента - имя
p_orders id - id_customer - код - дата
p_items id - id_order - описание - цена
и h_orders и h_items, которые являются точной копией p_orders и p_items.
Когда таблицы p_ достигают большого количества строк, я перемещаю самые старые в таблицы h_.. они должны быть в истории.
Итак, моя проблема: как получить данные как из таблиц p_, так и из таблиц h_, считая их одной уникальной таблицей?
Например, я хочу получить количество заказов для каждого клиента и общую стоимость (всех заказов клиента), и я использую этот запрос:
SELECT
customer.id,
customer.name,
count(DISTINCT p_orders.id) AS num_orders,
sum(p_items.price) AS total_money
FROM
customer
INNER JOIN p_orders ON p_orders.id_customer = customer.id
INNER JOIN p_items ON p_items.id_order = p_orders.id
GROUP BY
customer.id,
customer.name,
p_orders.id_customer
ORDER BY
customer.id
он работает только для одного «набора» таблиц (p_ или h_).. но я хочу их обоих.
Я пытался использовать UNION:
(
SELECT
customer.id,
customer.name,
count(DISTINCT p_orders.id) AS num_orders,
sum(p_items.price) AS total_money
FROM
customer
INNER JOIN p_orders ON p_orders.id_customer = customer.id
INNER JOIN p_items ON p_items.id_order = p_orders.id
GROUP BY
customer.id,
customer.name,
p_orders.id_customer
)
UNION
(
SELECT
customer.id,
customer.name,
count(DISTINCT h_orders.id) AS num_orders,
sum(h_items.price) AS total_money
FROM
customer
INNER JOIN h_orders ON h_orders.id_customer = customer.id
INNER JOIN h_items ON h_items.id_order = h_orders.id
GROUP BY
customer.id,
customer.name,
h_orders.id_customer
)
ORDER BY id ASC
Это работает, но если у клиента есть заказы как в таблицах p_, так и в таблицах h_, у меня будет 2 строки для этого клиента с двумя разными num_orders и total_money (соответственно из таблиц p_ и h_).
Я попытался добавить идентификатор GROUP BY вне союза:
(
--SELECT 2
)
UNION
(
--SELECT 1
)
GROUP BY id
ORDER BY id ASC
но запрос терпит неудачу с ОШИБКОЙ: синтаксическая ошибка в или рядом с "GROUP" в символе 948, кажется, что GROUP BY не может использоваться таким образом.
Любое предложение?
ИЗМЕНИТЬ:
Для uriDium да, все таблицы имеют столбец id в качестве первичного ключа, а упомянутые поля (он же p_orders.id_customer) также являются внешними ключами. Вот тестовый дамп структуры базы данных (я добавил некоторые индексы и внешние ключи после создания таблицы, но я не думаю, что это что-то значит):
CREATE TABLE customer (
id serial NOT NULL,
name character(50)
);
CREATE TABLE p_orders (
id serial NOT NULL,
id_customer integer NOT NULL,
date date DEFAULT now(),
code character(5)
);
CREATE TABLE p_items (
id serial NOT NULL,
id_order integer NOT NULL,
descr character(250),
price money
);
CREATE TABLE h_orders (
id integer NOT NULL,
id_customer integer NOT NULL,
date date,
code character(5)
);
CREATE TABLE h_items (
id integer NOT NULL,
id_order integer NOT NULL,
descr character(250),
price money
);
CREATE UNIQUE INDEX id_h_orders ON h_orders USING btree (id);
CREATE INDEX id_h_o_c ON h_orders USING btree (id_customer);
CREATE UNIQUE INDEX id_items_h ON h_items USING btree (id);
CREATE INDEX id_ordinr_dsve ON h_items USING btree (id_order);
ALTER TABLE ONLY customer
ADD CONSTRAINT customer_pkey (id);
ALTER TABLE ONLY p_orders
ADD CONSTRAINT p_orders_pkey PRIMARY KEY (id);
ALTER TABLE ONLY p_items
ADD CONSTRAINT p_items_pkey PRIMARY KEY (id);
ALTER TABLE ONLY stats
ADD CONSTRAINT stats_pkey PRIMARY KEY (id);
ALTER TABLE ONLY p_orders
ADD CONSTRAINT "$1" FOREIGN KEY (id_customer) REFERENCES customer(id) ON DELETE CASCADE;
ALTER TABLE ONLY p_items
ADD CONSTRAINT "$1" FOREIGN KEY (id_order) REFERENCES p_orders(id) ON DELETE CASCADE;
ALTER TABLE ONLY h_orders
ADD CONSTRAINT "$1" FOREIGN KEY (id_customer) REFERENCES customer(id) ON DELETE CASCADE;
ALTER TABLE ONLY h_items
ADD CONSTRAINT "$1" FOREIGN KEY (id_order) REFERENCES h_orders(id) ON DELETE CASCADE;
