SQL：将2个表分组为1，使用join，union，然后？(SQL: grouping 2 tables as 1 with join, union, and then?)

我有5张桌子：

客户ID - 名称

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行，其中包含2个不同的num_orders和total_money（分别来自p_ tables和h_ tables）

我试图在联盟之外添加GROUP BY id：

( --SELECT 2 ) UNION ( --SELECT 1 ) GROUP BY id ORDER BY id ASC

但查询在字符948处出现“GROUP”处或附近的ERROR：语法错误 ，看起来像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;

I have 5 tables:

customers id - name

p_orders id - id_customer - code - date

p_items id - id_order - description - price

and h_orders and h_items, that are exactly the copy of p_orders and p_items.

When the p_ tables reach a big amount of rows, i move the oldest to the h_ tables.. they due as history.

So, my problem is: how to retrieve the data from both the p_ tables and h_ considering them as one unique table?

For example, i want to retrieve the number of orders for each customer, and the total price (of all the customer's orders), and i use that query:

it works just for one 'set' of tables (p_ or h_)..but i want them both.

I've tryed to use an UNION:

This one works, but if a customer have orders both in the p_ tables and in the h_ tables, i'll have 2 rows for that customer with 2 different num_orders and total_money (respectively coming from p_ tables and h_ tables)

I've tryed to add a GROUP BY id outside the union:

( --SELECT 2 ) UNION ( --SELECT 1 ) GROUP BY id ORDER BY id ASC

but the query fail with ERROR: syntax error at or near "GROUP" at character 948, seem like GROUP BY cannot be used in that way.

Any suggestion?

EDIT:

For uriDium, yes, all the tables have the id column as primary key, and the referred fields (aka p_orders.id_customer) are foreign keys too. Here the test db structure dump (i added some indexes and foreign keys after the table creation, but i dont think that this mean something):

最满意答案

您应该创建两个表的视图：

CREATE VIEW All_Orders AS SELECT id, id_customer, code, date, 'H' AS order_type FROM h_orders UNION ALL SELECT id, id_customer, code, date, 'P' AS order_type FROM p_orders CREATE VIEW All_Order_Items -- A table name of "items" is pretty bad in my opinion AS SELECT id, id_order, description, price, 'H' AS order_item_type FROM h_items UNION ALL SELECT id, id_order, description, price, 'P' AS order_item_type FROM p_items

现在你可以加入这些观点。我包括了类型（P＆H），以便您知道“id”列现在指的是什么。如果你的两个表中的id（“h”和“p”可以有重复项，那么你必须在All_Order_Items视图中加入Orders表。否则你将在两个视图之间加入很多麻烦。希望你的id列智能设计，而不仅仅是自动添加或标识列。

You should probably create views over the two tables:

Now you can just join to those views. I included the types (P & H) so that you know what the "id" column now refers to. If the ids in your two tables ("h" and "p" can have duplicates then you will have to join the Orders table right in the All_Order_Items view. Otherwise you will have a lot of trouble joining between the two views. Hopefully your id columns are intelligently designed and not just auto-incrmenting or identity columns.

更多推荐