合并两个没有公共字段的表 [英] Combine two tables that have no common fields
问题描述
我想学习如何组合两个没有共同字段的数据库表.我检查过 UNION 但 MSDN 说:
I want to learn how to combine two db tables which have no fields in common. I've checked UNION but MSDN says :
以下是使用 UNION 组合两个查询的结果集的基本规则:
The following are basic rules for combining the result sets of two queries by using UNION:
- 在所有查询中，列的数量和顺序必须相同.
- 数据类型必须兼容.
但我根本没有共同的领域.我想要的只是将它们像视图一样组合在一张表中.
But I have no fields in common at all. All I want is to combine them in one table like a view.
那我该怎么办?
推荐答案
有多种方法可以做到这一点，具体取决于您真正想要什么.没有常用栏目，你需要决定是要引入一个常用栏目还是获取产品.
There are a number of ways to do this, depending on what you really want. With no common columns, you need to decide whether you want to introduce a common column or get the product.
假设您有两个表:
parts: custs:
+----+----------+ +-----+------+
| id | desc | | id | name |
+----+----------+ +-----+------+
| 1 | Sprocket | | 100 | Bob |
| 2 | Flange | | 101 | Paul |
+----+----------+ +-----+------+
忘记实际的列，因为在这种情况下您很可能有客户/订单/零件关系；我只是用这些列来说明如何做到这一点.
Forget the actual columns since you'd most likely have a customer/order/part relationship in this case; I've just used those columns to illustrate the ways to do it.
笛卡尔积将匹配第一个表中的每一行与第二个表中的每一行:
A cartesian product will match every row in the first table with every row in the second:
> select * from parts, custs;
id desc id name
-- ---- --- ----
1 Sprocket 101 Bob
1 Sprocket 102 Paul
2 Flange 101 Bob
2 Flange 102 Paul
这可能不是您想要的，因为 1000 个零件和 100 个客户会导致 100,000 行包含大量重复信息.
That's probably not what you want since 1000 parts and 100 customers would result in 100,000 rows with lots of duplicated information.
或者，您可以使用联合来仅输出数据，但不是并排(您需要确保两个选择之间的列类型兼容，通过使表列兼容或强制它们在选择中):
Alternatively, you can use a union to just output the data, though not side-by-side (you'll need to make sure column types are compatible between the two selects, either by making the table columns compatible or coercing them in the select):
> select id as pid, desc, null as cid, null as name from parts
union
select null as pid, null as desc, id as cid, name from custs;
pid desc cid name
--- ---- --- ----
101 Bob
102 Paul
1 Sprocket
2 Flange
在某些数据库中，您可以使用 rowid/rownum 列或伪列来并排匹配记录，例如:
In some databases, you can use a rowid/rownum column or pseudo-column to match records side-by-side, such as:
id desc id name
-- ---- --- ----
1 Sprocket 101 Bob
2 Flange 101 Bob
代码类似于:
select a.id, a.desc, b.id, b.name
from parts a, custs b
where a.rownum = b.rownum;
它仍然像一个笛卡尔积，但 where 子句限制了如何组合行以形成结果(所以根本不是笛卡尔积，真的).
It's still like a cartesian product but the where clause limits how the rows are combined to form the results (so not a cartesian product at all, really).
我没有为此测试过该 SQL，因为它是我选择的 DBMS 的限制之一，这是正确的，我认为在经过深思熟虑的模式中不需要它.由于 SQL 不保证它生成数据的顺序，除非您有特定关系或order by子句，否则每次执行查询时匹配都会改变.
I haven't tested that SQL for this since it's one of the limitations of my DBMS of choice, and rightly so, I don't believe it's ever needed in a properly thought-out schema. Since SQL doesn't guarantee the order in which it produces data, the matching can change every time you do the query unless you have a specific relationship or order by clause.
我认为理想的做法是在两个表中添加一列，指定关系是什么.如果没有真正的关系，那么您可能无法尝试将它们与 SQL 并排放置.
I think the ideal thing to do would be to add a column to both tables specifying what the relationship is. If there's no real relationship, then you probably have no business in trying to put them side-by-side with SQL.
如果您只想将它们并排显示在报告或网页上(两个示例)，那么正确的工具是生成您的报告或网页的任何工具，再加上两个独立的 SQL 查询以获取两个不相关的表.例如，BIRT(或 Crystal 或 Jasper)中的两列网格各有一个单独的数据表，或者 HTML 两列表(或 CSS)各有一个单独的数据表.
If you just want them displayed side-by-side in a report or on a web page (two examples), the right tool to do that is whatever generates your report or web page, coupled with two independent SQL queries to get the two unrelated tables. For example, a two-column grid in BIRT (or Crystal or Jasper) each with a separate data table, or a HTML two column table (or CSS) each with a separate data table.
这篇关于合并两个没有公共字段的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
