虚拟表和联接 [英] Virtual Table and Join

问题描述

如何在单个查询中创建虚拟表并将其内部联接
原始表格:
table1栏位:A，B，C，D，E
表2栏位:F，G，H，B，K


虚拟表1字段:B，sum(A)
虚拟表2个字段:B，sum(K)

所需的联接结果:B，sum(A)，sum(K)

How create virtual table and inner join them in a single query
Original Table:
table1 fields:A,B,C,D,E
Table2 Fields:F,G,H,B,K


Virtual table1 fields:B,sum(A)
Virtual table2 fields:B,sum(K)

Join results needed:B,sum(A),sum(K)

推荐答案

Okey，由于您尚未指定确切的表结构和数据，因此下面考虑了

Okey , Since you have not specified exact table structure and data so I have considered below

Table1
 (A BIGINT ,
  B NVARCHAR(50) ,
  C NVARCHAR(50) ,
  D NVARCHAR(50) ,
  E NVARCHAR(50)
 )

Table2
 (F NVARCHAR(50) ,
  G NVARCHAR(50) ,
  H NVARCHAR(50) ,
  B NVARCHAR(50) ,
  K BIGINT
 )

表1
A	B	C	D	E
10	B1	C1	D1	E1
20	B2	C2	D2	E2

表2
F	G	H	B	K
F1	G1	H1	B1	10
F1	G2	H2	B2	20

这是根据您的需要的sql proc

Table1
A	B	C	D	E
10	B1	C1	D1	E1
20	B2	C2	D2	E2

Table2
F	G	H	B	K
F1	G1	H1	B1	10
F1	G2	H2	B2	20

And here is the sql proc as per your need

CREATE TABLE #table1 (B NVARCHAR(10),SumOfA BIGINT,C NVARCHAR(50),D NVARCHAR(50),E NVARCHAR(50))
CREATE TABLE #table2 (B NVARCHAR(10),SumOfK BIGINT,F NVARCHAR(50),G NVARCHAR(50),H NVARCHAR(50))

INSERT INTO #table1
SELECT B,SUM(A),C,D,E FROM Table1 GROUP BY B,C,D,E

INSERT INTO #table2
SELECT B,SUM(K),F,G,H FROM Table2 GROUP BY B,F,G,H

SELECT B,SumOfA,SumOfK,C,D,E,F,G,H FROM #table1 T1 Inner Join #table2 T2 On T1.A=T2.A 

希望这会有所帮助，如果是，那么请接受并投票答复.任何对此的任何疑问/问题都将受到欢迎.

谢谢&问候
RDBurmon.Sr.Software Engineer

Hope this helps , If yes then plz accept and vote the answer. Any queries / questions on this are always welcome.

Thanks & Regards
RDBurmon.Sr.Software Engineer

Manas Bhardwaj的回答很好，但我更喜欢使用 ^ ]，就像这样:

The answer of Manas Bhardwaj is good, but i prefer to use aliases[^], like this:

SELECT t1.B, SUM(t1.A), SUM(t2.K)
FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.B = t2.B
GROUP BY t1.A, t1.B, t2.K

我认为它看起来更可读；)

如果您想创建临时表，请将#与创建表一起使用 [ ^ ]命令

In my opinion it looks more readible ;)

If youwould like to create temporary table, use # with CREATE TABLE[^] command, for example

CREATE TABLE #MyTempTable (
    ID INT NOT NULL,
    Name VARCHAR(30) NULL)

--usage:
INSERT INTO #MyTampTable (ID, Name)
    VALUES(1,'SomeText')

SELECT *
FROM #MyTempTable

DROP TABLE #MyTempTable

SELECT
    Table1.B,
    SUM(Table1.A),
    SUM(Table2.K)
FROM
    Table1 INNER JOIN Table2 ON
        Table1.B = Table2.B
GROUP BY Table1.A, Table1.B, Table2.K

这篇关于虚拟表和联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！