SQL：使用连接、联合将 2 个表分组为 1，然后？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/898019/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: grouping 2 tables as 1 with join, union, and then?
提问by Strae
I have 5 tables:
我有5张桌子：
customers id - name
客户 ID - 姓名
p_orders id - id_customer - code - date
p_orders id - id_customer - 代码 - 日期
p_items id - id_order - description - price
p_items id - id_order - 描述 - 价格
and h_orders and h_items, that are exactly the copy of p_orders and p_items.
以及 h_orders 和 h_items，它们正是 p_orders 和 p_items 的副本。
When the p_ tables reach a big amount of rows, i move the oldest to the h_ tables.. they due as history.
当 p_ 表达到大量行时，我将最旧的移到 h_ 表......它们作为历史记录。
So, my problem is: how to retrieve the data from both the p_ tables and h_ considering them as one unique table?
所以，我的问题是：如何从 p_ 表和 h_ 中检索数据，将它们视为一个唯一的表？
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:
例如，我想检索每个客户的订单数量和总价（所有客户的订单），我使用该查询：
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
it works just for one 'set' of tables (p_ or h_)..but i want them both.
它仅适用于一组“一组”表（p_ 或 h_）..但我想要它们。
I've tryed to use an UNION:
我尝试使用 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
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)
这个有效，但如果客户在 p_ 表和 h_ 表中都有订单，我将为该客户有 2 行，其中包含 2 个不同的 num_orders 和 total_money（分别来自 p_ 表和 h_ 表）
I've tryed to add a GROUP BY id outside the union:
我试图在联合之外添加一个 GROUP BY id：
(
--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.
但是查询失败并显示ERROR: syntax error at or near "GROUP" at character 948，似乎 GROUP BY 不能以这种方式使用。
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):
对于 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 "" FOREIGN KEY (id_customer) REFERENCES customer(id) ON DELETE CASCADE;
ALTER TABLE ONLY p_items
ADD CONSTRAINT "" FOREIGN KEY (id_order) REFERENCES p_orders(id) ON DELETE CASCADE;
ALTER TABLE ONLY h_orders
ADD CONSTRAINT "" FOREIGN KEY (id_customer) REFERENCES customer(id) ON DELETE CASCADE;
ALTER TABLE ONLY h_items
ADD CONSTRAINT "" FOREIGN KEY (id_order) REFERENCES h_orders(id) ON DELETE CASCADE;
回答by Tom H
You should probably create views over the two tables:
您可能应该在两个表上创建视图：
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
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.
现在您可以加入这些视图。我包括了类型 (P & H)，以便您知道“id”列现在指的是什么。如果您的两个表中的 id（“h”和“p”可以有重复，那么您必须在 All_Order_Items 视图中加入 Orders 表。否则您将在两个视图之间加入时遇到很多麻烦。希望您的 id列是智能设计的，而不仅仅是自动递增或标识列。
回答by Jimmie R. Houts
You could try this:
你可以试试这个：
SELECT tbl.ID,
tbl.Name,
sum(tbl.num_orders) num_orders,
sum(tbl.total_money) total_money
FROM (
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
) tbl
GROUB BY tbl.id, tbl.name
ORDER BY tbl.id ASC
回答by HLGEM
Create a view with the union of the two queries but without the aggregate functions. USe Union All as the same record is not in both tables and you don't need the server to waste time looking to see that.You will probaly have other times you want to access both tables in a query.
使用两个查询的联合创建一个视图，但没有聚合函数。使用 Union All 因为相同的记录不在两个表中，您不需要服务器浪费时间查看它。您可能会有其他时间想要在查询中访问两个表。
Then write your query using the view.
然后使用视图编写查询。
view code would be something like (you may want other fields for other purposes as well:
查看代码将类似于（您可能还需要其他字段用于其他目的：
Create view customerOrders
AS
SELECT customer.id as CustomerID, customer.name, p_orders.id as OrderID, p_items.price as price
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
union all
SELECT customer.id, customer.name, h_orders.id as id, H_items.price
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
then the call for your query would be something like (none of this is tested may need adjustment)
那么对您的查询的调用将类似于（这些都没有经过测试，可能需要调整）
SELECT CustomerID, customer.name, count(DISTINCT OrderID) AS num_orders,
sum(price) AS total_money
FROM customerOrders
GROUP BY CustomerID, customer.name
ORDER BY CustomerID
回答by Strae
Thanks for all the replies, guys..
谢谢大家的回复，各位。。
Both the 'views way' and the 'subquery way' by Jimmie R. Houts works perfectly, maybe the views are just more convenient to use.. and them both should take the same time (or not?)
Jimmie R. Houts 的“视图方式”和“子查询方式”都可以完美运行，也许视图使用起来更方便......而且它们都应该花费相同的时间（或不？）
So i'll mark as best answer the first one about the views.
因此，我会将有关观点的第一个答案标记为最佳答案。
Anyway, if i can, may i ask you if the structure and indexes i used are a good or could be optimized?
无论如何，如果可以的话，请问我使用的结构和索引是否良好或可以优化？
回答by uriDium
As far as I know SQL Server should automatically eliminating duplicates. Using UNION ALL will include duplicates. I would imagine that SQL Server would use the primary keys as a means of working out what is a duplicate. Are the primary keys on these tables made up of the same datatype and is ID 1 in your p table also ID 1 in your h table?
据我所知 SQL Server 应该自动消除重复项。使用 UNION ALL 将包含重复项。我想 SQL Server 会使用主键作为计算重复项的一种方式。这些表上的主键是否由相同的数据类型组成，并且 p 表中的 ID 1 也是 h 表中的 ID 1？
回答by Eric Petroelje
The easiest way to do what you are looking at would be to create views (say "a_orders" and "a_items"). The views would just be defined like:
做你所看到的最简单的方法是创建视图（比如“a_orders”和“a_items”）。视图将被定义为：
SELECT * FROM p_orders
UNION
SELECT * FROM h_orders
If you delete rows from a_orders as you insert them to h_orders (so a given order would not be in both tables) it would be quite a bit more efficient to use UNION ALL instead of UNION.
如果在将行插入到 h_orders 时从 a_orders 中删除行（因此给定的顺序不会在两个表中），使用 UNION ALL 而不是 UNION 会更有效率。
