如何合并来自不同表的记录？ [英] How to combine records from different tables?

问题描述

同一工作簿中有两个工作表，它们的结构相同，字段名称相同。

There are two worksheets in same workbook that have the same structure-same field names.

例如：

表1

- Officer  name   mkt 
 - s15     peter  15
 - s17     mary   18
 - S32     tom    42
 - S32     tom    89

表2

- Officer  name   mkt 
 - s56     jason  55
 - s31     alex   34
 - S54     gee    45

现在我正在使用ADO在excel-vba中运行sql。

Now I'm using ADO to run the sql in excel-vba.

select officer ,name ,sum(mkt) from [$table1]

现在我想合并这两个表记录并选择

Now I want to combine these 2 tables records and select later.

这意味着：

表3

- Officer  name   mkt 
 - s15     peter  15
 - s17     mary   18
 - S32     tom    42
 - S32     tom    89
 - s56     jason  55
 - s31     alex   34
 - S54     gee    45

然后稍后进行选择（SQL）。

Then later make the selection(SQL) .

  select officer ,name ,sum(mkt) from [$table3]

它是否可以在SQL或VBA中执行（我更喜欢在SQL语句中执行）？

*我更喜欢使用sth SQL技术来执行它。联接表之类的东西？但是联接表只联接不同表中的列。现在我想联接行*

*I prefer to use sth SQL technique to perform it . Something like join table ? But join table only join the columns in different tables.Now I want to join rows *

推荐答案

您可以使用公用表表达式将表合并为一个，然后执行总计。我正在使用 SET NOCOUNT ON； ，因为如果我省略了它，我以前在excel中有问题。

You can use a common table expression to union the tables into one and then perform the aggregate sum. I'm using SET NOCOUNT ON; because I had issues before in excel if I omitted this. A full outer join between the two tables would also work.

SET NOCOUNT ON;

WITH CTE AS
(
    SELECT *
    FROM [$table1]
    UNION ALL
    SELECT *
    FROM [$table2]
)

SELECT office, name, sum(mkt)
FROM CTE
GROUP BY office, name

您也可以尝试不使用CTE：

You can also try without the CTE:

SELECT office, name, sum(mkt)
FROM(
    SELECT *
    FROM [$table1]
    UNION ALL
    SELECT *
    FROM [$table2]
)
GROUP BY office, name

这篇关于如何合并来自不同表的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！