当前位置： 文江博客话题详情

合并两个没有公共字段的表

发布于 2024-07-29 07:12:55 字数 249 浏览 3 评论 0原文

我想学习如何组合两个没有共同字段的数据库表。 我查过 UNION 但 MSDN 说：

以下是使用 UNION 组合两个查询的结果集的基本规则：

  1. 所有查询中的列数和顺序必须相同。
  2. 数据类型必须兼容。

但我完全没有共同领域。 我想要的只是将它们像视图一样组合在一张表中。

所以我该怎么做？

原文

I want to learn how to combine two db tables which have no fields in common. I've checked UNION but MSDN says :

The following are basic rules for combining the result sets of two queries by using UNION:

  1. The number and the order of the columns must be the same in all queries.
  2. The data types must be compatible.

But I have no fields in common at all. All I want is to combine them in one table like a view.

So what should I do?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（15）

樱娆 2024-08-05 07:12:55

有多种方法可以做到这一点，具体取决于您的真正需求。 由于没有通用色谱柱，您需要决定是要引入通用色谱柱还是购买该产品。

假设您有两个表：

parts:              custs:
+----+----------+   +-----+------+
| id | desc     |   |  id | name |
+----+----------+   +-----+------+
|  1 | Sprocket |   | 100 | Bob  |
|  2 | Flange   |   | 101 | Paul |
+----+----------+   +-----+------+

忘记实际的列，因为在这种情况下您很可能拥有客户/订单/零件关系； 我只是用这些专栏来说明如何做到这一点。

笛卡尔积会将第一个表中的每一行与第二个表中的每一行进行匹配：

> 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 行包含大量重复信息。

或者，您可以使用联合来仅输出数据，尽管不是并排（您需要确保两个选择之间的列类型兼容，可以通过使表列兼容或在选择中强制它们） ):

> 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 列或伪列来并排匹配记录，例如：

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 子句限制了如何组合行以形成结果（因此根本不是笛卡尔积，真的）。

我还没有为此测试该 SQL，因为它是我选择的 DBMS 的限制之一，因此，我不认为在经过深思熟虑的模式中需要它。 由于 SQL 不保证其生成数据的顺序，因此每次执行查询时匹配都可能会发生变化，除非您有特定关系或order by子句。

我认为理想的做法是向两个表添加一列，指定关系是什么。 如果没有真正的关系，那么您可能没有必要尝试将它们与 SQL 并列放置。

如果您只想将它​​们并排显示在报告或网页上（两个示例），那么正确的工具就是生成您的报告或网页的任何工具，再加上两个独立 SQL查询来获取两个不相关的表。 例如，BIRT（或 Crystal 或 Jasper）中的两列网格每个都有一个单独的数据表，或者 HTML 两列表（或 CSS）每个都有一个单独的数据表。

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.

Let's say you have the two tables:

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

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

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

The code would be something like:

select a.id, a.desc, b.id, b.name
from parts a, custs b
where a.rownum = b.rownum;

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).

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.

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.

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.

回复 原文
勿忘初心 2024-08-05 07:12:55

这是一个非常奇怪的请求，几乎可以肯定这是您在现实应用程序中永远不想做的事情，但从纯粹的学术角度来看，这是一个有趣的挑战。 在 SQL Server 2005 中，您可以使用通用表表达式和 row_number() 函数并进行连接：

with OrderedFoos as (
    select row_number() over (order by FooName) RowNum, *
    from Foos (nolock)
),
OrderedBars as (
    select row_number() over (order by BarName) RowNum, *
    from Bars (nolock)
)
select * 
from OrderedFoos f
    full outer join OrderedBars u on u.RowNum = f.RowNum

这可行，但非常愚蠢，我仅将其作为“社区 wiki”答案提供，因为我真的不会推荐它。

This is a very strange request, and almost certainly something you'd never want to do in a real-world application, but from a purely academic standpoint it's an interesting challenge. With SQL Server 2005 you could use common table expressions and the row_number() functions and join on that:

with OrderedFoos as (
    select row_number() over (order by FooName) RowNum, *
    from Foos (nolock)
),
OrderedBars as (
    select row_number() over (order by BarName) RowNum, *
    from Bars (nolock)
)
select * 
from OrderedFoos f
    full outer join OrderedBars u on u.RowNum = f.RowNum

This works, but it's supremely silly and I offer it only as a "community wiki" answer because I really wouldn't recommend it.

回复 原文
前事休说 2024-08-05 07:12:55
SELECT *
FROM table1, table2

这会将 table1 中的每一行与 table2 （笛卡尔积）连接起来，返回所有列。

SELECT *
FROM table1, table2

This will join every row in table1 with table2 (the Cartesian product) returning all columns.

回复 原文
宁愿没拥抱 2024-08-05 07:12:55
select 
    status_id, 
    status, 
    null as path, 
    null as Description 
from 
    zmw_t_status

union
select 
    null, 
    null, 
    path as cid, 
    Description from zmw_t_path;

select 
    status_id, 
    status, 
    null as path, 
    null as Description 
from 
    zmw_t_status

union
select 
    null, 
    null, 
    path as cid, 
    Description from zmw_t_path;
回复 原文
极度宠爱 2024-08-05 07:12:55

尝试：

select * from table 1 left join table2 as t on 1 = 1;

这将从两个表中获取所有列。

try:

select * from table 1 left join table2 as t on 1 = 1;

This will bring all the columns from both the table.

回复 原文
幻想少年梦 2024-08-05 07:12:55

如果表没有公共字段，则无法在任何有意义的视图中组合数据。 您最终更有可能得到一个包含两个表中重复数据的视图。

If the tables have no common fields then there is no way to combine the data in any meaningful view. You would more likely end up with a view that contains duplicated data from both tables.

回复 原文
七分※倦醒 2024-08-05 07:12:55

为了获得两个表的有意义/有用的视图，您通常需要从每个表中确定一个标识字段，然后可以在 JOIN 的 ON 子句中使用该字段。

那么在您看来：

SELECT T1.*, T2.* FROM T1 JOIN T2 ON T1.IDFIELD1 = T2.IDFIELD2

您提到没有字段是“通用的”，但是尽管标识字段可能不具有相同的名称，甚至不是相同的数据类型，但您可以使用转换/转换函数以某种方式连接它们。

To get a meaningful/useful view of the two tables, you normally need to determine an identifying field from each table that can then be used in the ON clause in a JOIN.

THen in your view:

SELECT T1.*, T2.* FROM T1 JOIN T2 ON T1.IDFIELD1 = T2.IDFIELD2

You mention no fields are "common", but although the identifying fields may not have the same name or even be the same data type, you could use the convert / cast functions to join them in some way.

回复 原文
傲性难收 2024-08-05 07:12:55

为什么不使用简单的方法

    SELECT distinct *
    FROM 
    SUPPLIER full join 
    CUSTOMER on (
        CUSTOMER.OID = SUPPLIER.OID
    )

它为您提供两个表中的所有列，并返回来自客户和供应商的所有记录，如果客户有 3 条记录，供应商有 2 条记录，那么供应商将在所有列中显示 NULL

why don't you use simple approach

    SELECT distinct *
    FROM 
    SUPPLIER full join 
    CUSTOMER on (
        CUSTOMER.OID = SUPPLIER.OID
    )

It gives you all columns from both tables and returns all records from customer and supplier if Customer has 3 records and supplier has 2 then supplier'll show NULL in all columns

回复 原文
红衣飘飘貌似仙 2024-08-05 07:12:55
Select 
DISTINCT  t1.col,t2col
From table1 t1, table2 t2

OR

Select 
DISTINCT  t1.col,t2col
From table1 t1 
cross JOIN  table2 t2

如果它拥抱数据，则需要很长时间..

Select 
DISTINCT  t1.col,t2col
From table1 t1, table2 t2

OR

Select 
DISTINCT  t1.col,t2col
From table1 t1 
cross JOIN  table2 t2

if its hug data , its take long time ..

回复 原文
巴黎盛开的樱花 2024-08-05 07:12:55
SELECT t1.col table1col, t2.col table2col
FROM table1 t1 
JOIN table2 t2 on t1.table1Id = x and t2.table2Id = y

SELECT t1.col table1col, t2.col table2col
FROM table1 t1 
JOIN table2 t2 on t1.table1Id = x and t2.table2Id = y
回复 原文
不弃不离 2024-08-05 07:12:55

连接不相关的表

在此处输入图像描述

演示 SQL 脚本

IF OBJECT_ID('Tempdb..#T1') IS NOT NULL  DROP TABLE #T1;

CREATE TABLE #T1 (T1_Name VARCHAR(75));

INSERT INTO #T1 (T1_Name) VALUES ('Animal'),('Bat'),('Cat'),('Duet');

SELECT * FROM #T1;

IF OBJECT_ID('Tempdb..#T2') IS NOT NULL  DROP TABLE #T2;

CREATE TABLE #T2 (T2_Class VARCHAR(10));

INSERT INTO #T2 (T2_Class) VALUES ('Z'),('T'),('H'); 

SELECT * FROM #T2;

要连接非相关表，我们将介绍一个序列号的常见连接列如下。

SQL 脚本

SELECT T1.T1_Name,ISNULL(T2.T2_Class,'') AS T2_Class FROM
( SELECT T1_Name,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS S_NO FROM #T1) T1
LEFT JOIN
( SELECT T2_Class,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS S_NO FROM #T2) T2
ON t1.S_NO=T2.S_NO;

Joining Non-Related Tables

enter image description here

Demo SQL Script

IF OBJECT_ID('Tempdb..#T1') IS NOT NULL  DROP TABLE #T1;

CREATE TABLE #T1 (T1_Name VARCHAR(75));

INSERT INTO #T1 (T1_Name) VALUES ('Animal'),('Bat'),('Cat'),('Duet');

SELECT * FROM #T1;

IF OBJECT_ID('Tempdb..#T2') IS NOT NULL  DROP TABLE #T2;

CREATE TABLE #T2 (T2_Class VARCHAR(10));

INSERT INTO #T2 (T2_Class) VALUES ('Z'),('T'),('H'); 

SELECT * FROM #T2;

To Join Non-Related Tables , we are going to introduce one common joining column of Serial Numbers like below.

SQL Script

SELECT T1.T1_Name,ISNULL(T2.T2_Class,'') AS T2_Class FROM
( SELECT T1_Name,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS S_NO FROM #T1) T1
LEFT JOIN
( SELECT T2_Class,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS S_NO FROM #T2) T2
ON t1.S_NO=T2.S_NO;
回复 原文
宁愿没拥抱 2024-08-05 07:12:55
select * from this_table;

select distinct person from this_table

union select address as location from that_table 

drop wrong_table from this_database;

select * from this_table;

select distinct person from this_table

union select address as location from that_table 

drop wrong_table from this_database;
回复 原文
灰色世界里的红玫瑰 2024-08-05 07:12:55

当您必须使用三个选择语句来执行此操作时非常困难，

我尝试了所有建议的技术，但这是徒劳的

请参阅下面的脚本。 如果您有替代解决方案，请提出建议

   select distinct x.best_Achiver_ever,y.Today_best_Achiver ,z.Most_Violator from 

    (SELECT  Top(4) ROW_NUMBER() over (order by tl.username)  AS conj, tl. 
     [username] + '-->' + str(count(*)) as best_Achiver_ever 
    FROM[TiketFollowup].[dbo].N_FCR_Tikect_Log_Archive tl
     group by tl.username
     order by count(*) desc) x
      left outer join 

(SELECT 
Top(4) ROW_NUMBER() over (order by tl.username)  as conj, tl.[username] + '-->' + str(count(*)) as Today_best_Achiver
FROM[TiketFollowup].[dbo].[N_FCR_Tikect_Log] tl
where convert(date, tl.stamp, 121) = convert(date,GETDATE(),121)
group by tl.username
order by count(*) desc) y 
on x.conj=y.conj

 left outer join 

(
select  ROW_NUMBER() over (order by count(*)) as conj,username+ '--> ' + str( count(dbo.IsViolated(stamp))) as Most_Violator from N_FCR_Ticket
where dbo.IsViolated(stamp) = 'violated' and  convert(date,stamp, 121) < convert(date,GETDATE(),121)
group by username
order by count(*) desc) z
on x.conj = z.conj

Very hard when you have to do this with three select statments

I tried all proposed techniques up there but it's in-vain

Please see below script. please advice if you have alternative solution

   select distinct x.best_Achiver_ever,y.Today_best_Achiver ,z.Most_Violator from 

    (SELECT  Top(4) ROW_NUMBER() over (order by tl.username)  AS conj, tl. 
     [username] + '-->' + str(count(*)) as best_Achiver_ever 
    FROM[TiketFollowup].[dbo].N_FCR_Tikect_Log_Archive tl
     group by tl.username
     order by count(*) desc) x
      left outer join 

(SELECT 
Top(4) ROW_NUMBER() over (order by tl.username)  as conj, tl.[username] + '-->' + str(count(*)) as Today_best_Achiver
FROM[TiketFollowup].[dbo].[N_FCR_Tikect_Log] tl
where convert(date, tl.stamp, 121) = convert(date,GETDATE(),121)
group by tl.username
order by count(*) desc) y 
on x.conj=y.conj

 left outer join 

(
select  ROW_NUMBER() over (order by count(*)) as conj,username+ '--> ' + str( count(dbo.IsViolated(stamp))) as Most_Violator from N_FCR_Ticket
where dbo.IsViolated(stamp) = 'violated' and  convert(date,stamp, 121) < convert(date,GETDATE(),121)
group by username
order by count(*) desc) z
on x.conj = z.conj
回复 原文
皇甫轩 2024-08-05 07:12:55

如果表有唯一的字段标识符，我们只需要匹配它们，忘记逻辑关系，我们可以使用这段代码

SELECT a.*, b.*
FROM dbo.tableA a
FULL JOIN dbo.tableB b ON a.ID = b.ID
ORDER BY a.ID

If the tables have unique field identifiers and we just need to match them, forgetting about the logical relationship, we can use this code

SELECT a.*, b.*
FROM dbo.tableA a
FULL JOIN dbo.tableB b ON a.ID = b.ID
ORDER BY a.ID
回复 原文
隐诗 2024-08-05 07:12:55

请尝试以下查询：

合并两个没有公共列的表：

SELECT * 
FROM table1 

UNION 

SELECT * 
FROM table2  
ORDER BY orderby ASC

Please try this query:

Combine two tables that have no common columns:

SELECT * 
FROM table1 

UNION 

SELECT * 
FROM table2  
ORDER BY orderby ASC
回复 原文
~没有更多了~

关于作者

铁轨上的流浪者

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

1CH1MKgiKxn9p

文章 0 评论 0

ゞ记忆︶ㄣ

文章 0 评论 0

JackDx

文章 0 评论 0

信远

文章 0 评论 0

yaoduoduo1995

文章 0 评论 0

霞映澄塘

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文