在SQL中添加两个表 [英] Add two tables in sql

问题描述

嗨..我有一个名为A的表，它具有字段

Hi.. I have a table named A .it has fields

Id   Name     Mark  Attendence  Total 

1    Aswathi     30        30      50

和名为B
的表

And table named B

Name     Test1  Test2

Aswathi  20      40

我需要将两个表串联为

I need to concatenate two tables as

Id   Name      Test1  Test2 Mark Attendence   Total

1    Aswathi      20     40  30    20          50

表B是动态的.我们可以创建任何测试，而测试的平均值在表A中显示.如何在sql中进行测试?


谢谢，

table B is dynamic..We can create any no of test and the average of that shows in table A.How it is possible in sql??


Thanks ,

推荐答案

可以使用INNER JOIN轻松实现.

This can be simply achieved using an INNER JOIN.

SELECT
   A.ID, A.Name, B.Test1, B.Test2, A.Mark, A.Attendence, A.Total
FROM 
   TableA A
INNER JOIN 
   TableB B 
ON A.Name = B.Name

注意:不过，我想向您指出，这里的联接字段是Name-一个字母数字字段.建议将基于整数的ID字段作为外键，以便于进行比较并避免出现问题.

NOTE: Though, I would like to point you out that the joining field out here is Name - an alphanumeric field. It is suggestible that an integer based ID field be foreign key for easy comparison and avoid issues.

由于表B是动态的，并且动态列可能具有不同的列名，因此可以挑战一下，因为除非应用动态查询，否则将列名分别称为B.Test1，B.Test2，因为可以有Test3，Test4等.

使用动态查询的结果(带有示例记录):

Since Table B is dynamic, and there are possibilities for dynamic column with varying column name, there can be challenge in specifically mention the column name as B.Test1, B.Test2 as there can be Test3, Test4, etc unless dynamic query is applied.

Result using dynamic query (with sample records):

BEGIN TRAN

DECLARE @TestColumns NVARCHAR(MAX)
SET @TestColumns = N'B.TEST1,B.TEST2';

CREATE TABLE A (Id INT,  Name VARCHAR(100),    Mark INT, Attendence INT, Total INT);

INSERT INTO A VALUES (1,'Aswathi',30,20,50);
INSERT INTO A VALUES (2,'Benny',20,30,50);

CREATE TABLE B (Name VARCHAR(100),  Test1 INT, Test2 INT);

INSERT INTO B VALUES('Aswathi',20,40)
INSERT INTO B VALUES('Benny',10,30)

DECLARE @QuerySQL NVARCHAR(MAX)
SET @QuerySQL = N'
SELECT
    A.ID, A.Name, ' + @TestColumns + ', A.Mark, A.Attendence, A.Total
    FROM A
INNER JOIN B
    ON A.Name = B.Name'


EXECUTE(@QuerySQL)

ROLLBACK TRAN

建议，可以相对于Test编号创建动态列，而不必将每个Test及其对应的标记记录为每个Table记录，例如，

Suggest, instead of creating dynamic column in respect with number of Test, each Test and corresponding marks can be recorded as each Table record like,

CREATE TABLE B (Name VARCHAR(100),  Test VARCHAR(25), Mark INT);
INSERT INTO B VALUES('Aswathi','Test1',20)
INSERT INTO B VALUES('Aswathi','Test2',40)

INSERT INTO B VALUES('Benny','Test1',10)
INSERT INTO B VALUES('Benny','Test2',30)

使用这种结构，可以使用下面的查询获得结果.

Using this structure, result can be achieved using the query below.

BEGIN TRAN
CREATE TABLE A (Id INT,  Name VARCHAR(100),    Mark INT, Attendence INT, Total INT);

INSERT INTO A VALUES (1,'Aswathi',30,30,0);
INSERT INTO A VALUES (2,'Benny',20,30,0);


CREATE TABLE B (Name VARCHAR(100),  Test VARCHAR(25), Mark INT);
INSERT INTO B VALUES('Aswathi','Test1',20)
INSERT INTO B VALUES('Aswathi','Test2',40)

INSERT INTO B VALUES('Benny','Test1',10)
INSERT INTO B VALUES('Benny','Test2',30)

SELECT Name, AVG(Mark) Mark INTO #tmp FROM B GROUP BY Name

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
 COALESCE(
  @PivotColumnHeaders + ',[' + [Test] + ']',
  '[' + [Test] + ']'
 )
FROM (SELECT DISTINCT TEST FROM B) TMP
ORDER BY Test

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT A.Id,TMP1.*,TMP2.Mark,A.Attendence,TMP2.Mark + A.Attendence Total FROM
(SELECT * FROM b
    PIVOT (
        AVG(Mark)
        FOR Test IN (' + @PivotColumnHeaders + ')) AS PivotTable
) TMP1
INNER JOIN #TMP TMP2
    ON TMP1.Name = TMP2.Name
INNER JOIN A
    ON A.Name = TMP1.Name
'
EXECUTE(@PivotTableSQL)


ROLLBACK TRAN

注意:您可以通过引用任何Test Master(如果可用)来避免使用DISTINCT子句.
标记是使用AVG计算的.根据您的要求，可以将其更改为SUM.

Note: You may avoid DISTINCT clause by refer to any Test master if available.
Mark is calculated using AVG. Based on your requirement this can be changed to SUM.

为什么我们不能像这样?

Why can''t we just do like this?

SELECT A.Id, B.*, A.Mark, A.Attendence, A.Total
FROM dbo.A
INNER JOIN dbo.B ON A.Name = B.Name

这篇关于在SQL中添加两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！