加入子查询 [英] JOIN on Subquery

问题描述

我想在其中一个表上使用 id 连接两列后，对两个表执行简单连接.

I want to execute a simple join on two tables after connecting two columns with ids on one of these tables.

第一步:

SELECT cars.hhid & cars.vmid
FROM cars

现在我想将此结果与另一个表(table2)进行比较.

Now I want to compare this result with another table (table2).

新结果应该是table1(汽车)中与table2中的id匹配的每一行.

The new result should be every row in table1 (cars) that match with id in table2.

怎么了?

我的代码:

SELECT Cars.* 
FROM (SELECT Cars.hhid & Cars.vmid AS zid
FROM cars) x
JOIN table2 ON table2.id = x.zid;

推荐答案

从你在问题中所说的，我不明白你为什么需要一个子查询.

From what you've said in the question, I don't see why you need a subquery at all.

试试这个:

select cars.*
from cars 
inner join table2 on cstr(table2.id) = cars.hhid & cars.vmid

(使用 double 作为所有三个字段的数据类型进行测试)

您不需要子查询，因为您可以将连接的列"直接放入 JOIN 子句中(当然您可以将它们放入 SELECT 子句中好吧，如果你在那里需要它们).

You don't need the subquery because you can put your "connected columns" directly into the JOIN clause (of course you can put them into the SELECT clause as well if you need them there).

但是，一旦你连接列，Access 似乎将它们视为 string，所以你不能直接在 double 列中加入它们代码>table2.
这就是为什么您需要使用 CStr() 将列从 table2 转换为字符串.

However, as soon as you concatenate the columns, Access seems to treat them as a string, so you can't join them directly on the double column in table2.
That's why you need to convert the column from table2 to a string with CStr().

这篇关于加入子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！