如何联接两个表，其中一个表没有主键且字符长度不同 [英] How to join two tables with one of them not having a primary key and not the same character length

问题描述

我正在处理两个表：
dyndomrun.ddid =有主键，而
domainregion.domainid =不任何主键或外键。

dyndomrun.ddid定义为字符变化，具有8个字符，而 domainregion.domainid定义也为字符变化，但具有10个字符。 / p>

问题：domainregion表中的某些字段需要与dyndomrun表中的​​主键结合在一起。我似乎无法使用以下简单的JOIN语句来做到这一点：

  SELECT domainregion.domainid，domainregion.dombegin， domainregion.domend，dyndomrun.ddid 
 FROM domainregion，dyndomrun 
 WHERE domainregion.domainid = dyndomrun.ddid 
 ORDER BY domainregion.domainid，dyndomrun.ddid，domainregion.dombegin，domainregion.domend; 
  

我尝试了JOINS，INNER JOINS，LIKE，它们似乎都不起作用。我正在处理的数据库是完全基于PostgreSQL的基于SQL的数据库。

有没有一种我可以使用的方法，它可以根据长度选择domainregion.domainid字符并与dyndomrun.ddid匹配？

请告知。

解决方案

尝试使用此方法仅比较前8个字符：

  SELECT r.domainid，r.dombegin，r.domend， d.ddid 
来自域区域r 
加入dyndomrun d ON r.domainid :: varchar（8）= d.ddid 
按r.domainid，d.ddid，r.dombegin，r排序.domend; 
  

强制转换会隐式修剪尾随字符。 ddid 开头只有8个字符。也无需进行处理。达到相同的目的：

  JOIN dyndomrun d ON left（r.domainid，8）= d.ddid 
  

但是，请注意，字符串函数 left（）仅PostgreSQL 9.1中引入。在早期版本中，您可以替换为：

  JOIN dyndomrun d ON substr（r.domainid，1，8）= d.ddid 
  

__

初学者的基本说明：

• 查询使用 JOIN 。阅读有关该手册的更多信息。




• FROM域区域r 是 FROM域区域AS r 。在这种情况下，在PostgreSQL中， AS 只是噪音。 表别名使查询更短，更易于阅读，但在此没有其他影响。例如，您还可以使用表别名多次包含同一张表。




• 连接条件 ON r.domainid :: varchar （8）= d.ddid 仅将两个表达式完全匹配的行连接在一起。再次，阅读手册中的基础 （或其他任何来源）。

这是一个简单的查询，在这里无需多解释。

I am dealing with two tables: "dyndomrun.ddid" = with a Primary key and "domainregion.domainid" = without any primary key nor foreign key.

"dyndomrun.ddid" definition is "character varying" and has 8 characters, whereas "domainregion.domainid" definition is also "character varying" but has 10 characters.

Problem : There are some fields in domainregion table that needs to be joined together with the primary key in dyndomrun table. I can't seem to do this using a simple JOIN statement such as below:

SELECT domainregion.domainid, domainregion.dombegin, domainregion.domend, dyndomrun.ddid 
FROM domainregion, dyndomrun 
WHERE domainregion.domainid = dyndomrun.ddid 
ORDER BY domainregion.domainid, dyndomrun.ddid, domainregion.dombegin, domainregion.domend;

I have tried JOINS, INNER JOINS, LIKE, none of them seems to work. The database that I am dealing with is purely SQL based stored using PostgreSQL.

Is there a method that i can use in which it selects the domainregion.domainid based on the length of the character and matching it dyndomrun.ddid?

Please advise.

解决方案

Try this to compare the first 8 characters only:

SELECT r.domainid, r.dombegin, r.domend, d.ddid 
FROM   domainregion r
JOIN   dyndomrun d ON r.domainid::varchar(8) = d.ddid 
ORDER  BY r.domainid, d.ddid, r.dombegin, r.domend;

The cast implicitly trims trailing characters. ddid only has 8 characters to begin with. No need to process it, too. This achieves the same:

JOIN   dyndomrun d ON left(r.domainid, 8) = d.ddid 

However, be advised that the string function left() was only introduced with PostgreSQL 9.1. In earlier versions you can substitute:

JOIN   dyndomrun d ON substr(r.domainid, 1, 8) = d.ddid

__

Basic explanation for beginners:

• The query uses a JOIN. Read more about that in the manual.

• FROM domainregion r is short for FROM domainregion AS r. AS is just noise in this case in PostgreSQL. The table alias makes the query shorter and easier to read but has no other impact in here. You can also use table aliases to include the same table multiple times for instance.

• The join condition ON r.domainid::varchar(8) = d.ddid joins only those rows together where the two expressions match exactly. Again, read about those basics in the manual (or any other source).

It's a simple query, not much to explain here.

这篇关于如何联接两个表，其中一个表没有主键且字符长度不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！