SQL联接和MS Access-如何将多个表组合为一个表? [英] SQL Joins and MS Access - How to combine multiple tables into one?

问题描述

我在Access 2010中有四个表，每个表具有相同的主键.我想将所有四个表中的所有数据连接到一个表中，每个值的列都绑定到所有表的主键上.因此，例如:

I have four tables in Access 2010, each with the same primary key. I'd like to join all the data in all four tables into one table with columns for each value tied to the primary key over all the tables. So, for example:

Table1

ID   Value1

1    10       
2    7
3    4
4    12

Table 2

ID   Value2

1    33
2    8
6    19
7    4

Table 3

ID   Value3

1    99
2    99
5    99
7    99

我想创建:

Table 4

ID  Value1  Value2  Value3

1   10      33      99
2   7       8       99
3   4
4   12
5           99
6           19      
7           4       99

我正在使用MS Access，我知道我必须基本上使用3个联接(左，右，内部)来获得完全联接，但是我不确定如何构造查询.

I'm using MS Access and I know I have to basically use 3 joins (left, right, inner) to get a full join, but I'm not exactly sure about how to structure the query.

有人可以给我一些示例SQL代码，向我指出如何产生此结果的正确方向吗?

Could someone please give me some example SQL code to point me in the right direction as to how to produce this result?

这是我到目前为止所拥有的.这将合并所有表，但看起来我仍然缺少一些数据.我做错了什么吗?

Here is what I have so far. This combines all the tables, but it looks like I'm still missing some data. Have I done something wrong:

SELECT Coventry.cptcode, Coventry.[Fee Schedule], CT6002.[Fee Schedule], Medicare.[Fee Schedule], OFSP.[Fee Schedule]
FROM ((Coventry LEFT JOIN CT6002 ON Coventry.cptcode = CT6002.cptcode) LEFT JOIN Medicare ON CT6002.cptcode = Medicare.cptcode) LEFT JOIN OFSP ON Medicare.cptcode = OFSP.cptcode
UNION
SELECT Coventry.cptcode, Coventry.[Fee Schedule], CT6002.[Fee Schedule], Medicare.[Fee Schedule], OFSP.[Fee Schedule]
FROM ((Coventry RIGHT JOIN CT6002 ON Coventry.cptcode = CT6002.cptcode) RIGHT JOIN Medicare ON CT6002.cptcode = Medicare.cptcode) RIGHT JOIN OFSP ON Medicare.cptcode = OFSP.cptcode
UNION
SELECT Coventry.cptcode, Coventry.[Fee Schedule], CT6002.[Fee Schedule], Medicare.[Fee Schedule], OFSP.[Fee Schedule]
FROM ((Coventry INNER JOIN CT6002 ON Coventry.cptcode = CT6002.cptcode) INNER JOIN Medicare ON CT6002.cptcode = Medicare.cptcode) INNER JOIN OFSP ON Medicare.cptcode = OFSP.cptcode;

推荐答案

如何?

SELECT id
, max(v1) as value1
, max(v2) as value2
, max(v3) as value3
FROM 
(
    select id
    , value1 as v1
    , iif(true,null,value1) as v2
    , iif(true,null,value1) as v3 
    from Table1

    union 

    select id, null , value2 , null  from Table2

    union 

    select id, null , null , value3 as v3 from Table3
)
group by id
order by id

工作原理:

• 我没有进行连接，而是将所有结果放入一个表"(我的子查询)中，但在其自己的列中使用了value1，value2和value3，对于不使用表的表则设置为null没有这些列.

• Rather than doing a join, I put all of the results into one "table" (my sub query), but with value1, value2 and value3 in their own columns, and set to null for the tables that don't have those columns.

第一个查询中的iif语句表示我希望v2和v3与v1具有相同的数据类型.这是一个狡猾的骇客，但似乎行得通(不幸的是，access从查询的第一条语句中得出了该类型，而使用clng(null)进行强制转换则行不通).之所以起作用，是因为iif的结果必须与最后两个参数具有相同的类型，并且只有最后一个参数具有类型，因此才可以从中推论得出.而第一个参数为true表示返回的值将永远是第二个参数.

The iif statements in the first query are to say that I want v2 and v3 to be the same data type as v1. It's a dodgy hack but seems to work (sadly access works out the type from the first statement in the query, and casting withclng(null) didn't work). They work because the result of the iif must be of the same type as the last two parameters, and only the last parameter has a type, so that gets inferred from this; whilst the first parameter being true means that the value returned will only ever be the second parameter.

然后，外部查询将这些结果压缩为每个id一行；由于具有值的字段大于null，并且我们最多只有一个具有每个id值的字段，因此我们将获得该列的值.

The outer query then squashes these results down to one line per id; since fields with a value are greater than null and we have at most one field with a value per id, we get that value for that column.

我不确定性能与MS文章的执行方式相比如何，但是如果您使用的是访问权限，我怀疑您还有其他事情要担心;).

I'm not sure how performance compares with the MS article's way of doing this, but if you're using access I suspect you have other things to worry about ;).

SQL Fiddle: http://sqlfiddle.com/#!6/6f93b/2 (对于SQL Server，因为Access不可用，但是我尝试使其尽可能相似)

SQL Fiddle: http://sqlfiddle.com/#!6/6f93b/2 (For SQL Server since Access not available, but I've tried to make it as similar as possible)

这篇关于SQL联接和MS Access-如何将多个表组合为一个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！