如何从SQL服务器中的两个表合并中检索数据？ [英] How to retrieve data from two tables merge in SQL server?

问题描述

您好，



我需要两张桌子： -

1.桌子A



Id |尺寸|

1 | abc

2 | xyz

3 | asd

4 | fgh

5 | opo



2. TableB



Id | TableAId1 | TableAId2 | TableAId3 | TableAId4

1 | 1 | 2 | 3 | 4

2 | 1 | 5 | 3 | 4



结果应显示：

TableBId | Dimension

1 | abc

1 | xyz

1 | asd

1 | fgh

2 | abc

2 | pop

2 | asd

2 | fgh



所以怎么写这个查询在sql server？



请帮帮我。

先谢谢。



Ankit Agarwal

软件工程师



我尝试过：

 SELECT ta2.Id，ta1.Dimension 
 FROM tableA ta1 
 JOIN tableB ta2 on ta1.id！= ta2.id 
 WHERE ta1.Id = ta2.TableAId1和ta1.Id = ta2.TableAId2和ta1.Id = ta2.TableAId3和ta1.Id = ta2.TableAId3 

解决方案

  SELECT  ta2.Id，ta1.Dimension 
  FROM  Tab1 ta1 
  JOIN  TabB ta2  on  ta1.id！= ta2.id 
  WHERE  ta1.Id = ta2.tabaid1 或 ta1.Id = ta2.tabaid2 或 ta1.Id = ta2.tabaid3 或 ta1 .Id = ta2.tabaid4 

尝试用OR替换AND：

 ...  WHERE  ta1.Id = ta2.TableAId1  OR  ta1.Id = ta2.TableAId2 ... 

AND要求双方都为真，或要求任何一方都为真，但不一定都是。

 CREATE TABLE TableA（ID int NOT NULL主键，描述varchar（100））; 
插入TableA（ID，描述）值（1，'abc'）; 
插入TableA（ID，描述）值（2，'xyz'）; 
插入TableA（ID，描述）值（3，'asd'）; 
插入TableA（ID，描述）值（4，'fgh'）; 
插入TableA（ID，描述）值（5，'opo'）; 
 CREATE TABLE TableB（[ID] int NOT NULL主键，TableAid1 int NOT NULL，TableAid2 int NOT NULL，TableAid3 int NOT NULL，TableAid4 int NOT NULL，
 CONSTRAINT FK_tblBAid1 FOREIGN KEY（TableAid1）
 REFERENCES TableA（ID），CONSTRAINT FK_tblBAid2 FOREIGN KEY（TableAid2）
 REFERENCES TableA（ID），
 CONSTRAINT FK_tblBAid3 FOREIGN KEY（TableAid3）
 REFERENCES TableA（ID），
 CONSTRAINT FK_tblBAid4 FOREIGN KEY（TableAid4）
 REFERENCES TableA（ID））; 
插入表B（ID，TableAid1，TableAid2，TableAid3，TableAid4）值（1,1,2,3,4）; 
插入表B（ID，TableAid1，TableAid2，TableAid3，TableAid4）值（2,1,5,3,4）; 

 SELECT ta2.Id，ta1.Dimension 
 FROM tableA ta1 
 JOIN tableB ta2 on ta1.id！= ta2.id 
 WHERE ta2.TableAId1 = ta1.Id OR ta2.TableAId2 = ta1.Id OR ta2.TableAId3 = ta1.Id OR ta2.TableAId3 = ta1.Id 
 UNION 
 SELECT ta2.ID，ta1.Description 
 FROM tableA ta1 
 JOIN tableB ta2 on ta1.ID = ta2.ID 
 WHERE ta2.TableAId1 = ta1.ID OR ta2.TableAId2 = ta1.ID OR ta2.TableAId3 = ta1.ID OR ta2.TableAId3 = ta1.ID 
 or ta2 .TableAId4 = ta1.ID 

以上解决方案是外键（ SQL小提琴 [ ^ ]）

Hello,

I need two table:-
1. TableA

Id|Dimension|
1|abc
2|xyz
3|asd
4|fgh
5|opo

2. TableB

Id|TableAId1|TableAId2|TableAId3|TableAId4
1| 1 |2 |3 |4
2| 1 |5 |3 |4

result should be display:
TableBId|Dimension
1|abc
1|xyz
1|asd
1|fgh
2|abc
2|pop
2|asd
2|fgh

so how to write query for this in sql server?

Please help me.
Thanks in Advance.

Ankit Agarwal
Software Engineer

What I have tried:

SELECT ta2.Id,ta1.Dimension
FROM tableA ta1
JOIN tableB ta2 on ta1.id != ta2.id
WHERE ta1.Id = ta2.TableAId1 and ta1.Id = ta2.TableAId2 and ta1.Id = ta2.TableAId3 and ta1.Id=ta2.TableAId3

解决方案

SELECT ta2.Id,ta1.Dimension
FROM Tab1 ta1
JOIN TabB ta2 on ta1.id != ta2.id
WHERE ta1.Id = ta2.tabaid1 or ta1.Id = ta2.tabaid2 or ta1.Id = ta2.tabaid3 or ta1.Id=ta2.tabaid4

Try replacing the AND with OR:

... WHERE ta1.Id = ta2.TableAId1 OR ta1.Id = ta2.TableAId2 ...

AND requires both sides to be true, OR requires either side to be true, but not necessarily both.

CREATE TABLE TableA(ID int NOT NULL Primary Key,Description varchar(100));
Insert into TableA(ID,Description)values(1,'abc');
Insert into TableA(ID,Description)values(2,'xyz');
Insert into TableA(ID,Description)values(3,'asd');
Insert into TableA(ID,Description)values(4,'fgh');
Insert into TableA(ID,Description)values(5,'opo');
CREATE TABLE TableB([ID] int NOT NULL Primary Key,TableAid1 int NOT NULL ,TableAid2 int NOT NULL,TableAid3 int NOT NULL,TableAid4 int NOT NULL,
                   CONSTRAINT FK_tblBAid1 FOREIGN KEY (TableAid1)
    REFERENCES TableA(ID),CONSTRAINT FK_tblBAid2 FOREIGN KEY (TableAid2)
    REFERENCES TableA(ID),
                   CONSTRAINT FK_tblBAid3 FOREIGN KEY (TableAid3)
    REFERENCES TableA(ID),
                   CONSTRAINT FK_tblBAid4 FOREIGN KEY (TableAid4)
    REFERENCES TableA(ID));
Insert into TableB(ID,TableAid1,TableAid2,TableAid3,TableAid4)values(1,1,2,3,4);
Insert into TableB(ID,TableAid1,TableAid2,TableAid3,TableAid4)values(2,1,5,3,4);

SELECT ta2.Id,ta1.Dimension
FROM tableA ta1
JOIN tableB ta2 on ta1.id != ta2.id
WHERE ta2.TableAId1=ta1.Id OR ta2.TableAId2=ta1.Id  OR ta2.TableAId3=ta1.Id  OR ta2.TableAId3=ta1.Id
UNION
SELECT ta2.ID,ta1.Description
FROM tableA ta1
JOIN tableB ta2 on ta1.ID = ta2.ID
WHERE ta2.TableAId1=ta1.ID OR ta2.TableAId2=ta1.ID  OR ta2.TableAId3=ta1.ID  OR ta2.TableAId3=ta1.ID
or ta2.TableAId4=ta1.ID

The above solution is with foreign key(SQL Fiddle[^])

这篇关于如何从SQL服务器中的两个表合并中检索数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！