基于同一列加入

发布于 2024-11-19 23:50:07 字数 483 浏览 0 评论 0原文

我有两个表，例如 TableA 和 TableB。

TableA 的架构：

     UoMID
     UoMName

TableB 的架构：

     ItemID
     ItemName
     UoMID
     D1_UoMID
     D2_UoMID
     D3_UoMID

这里我想根据 TableA 中的 UoMID 和 TableB 中的 UoMID、D1_UoMID、D2_UoMID 和 D3_UoMID 连接这两个表。我想检索以下列：

     ItemName, UoMName, D1_UoMName, D2_UoMName and D3_UoMName

如何检索以上列？以及如何识别相应 UoMID、D1_UoMID、D2_UoMID 和 D3_UoMID 的 UoMName？

原文

I have two tables such as TableA and TableB.

Schema for TableA:

     UoMID
     UoMName

Schema for TableB:

     ItemID
     ItemName
     UoMID
     D1_UoMID
     D2_UoMID
     D3_UoMID

Here I want to Join Both these tables based on UoMID in the TableA and UoMID, D1_UoMID, D2_UoMID and D3_UoMID in the TableB.
I want to retrieve the following columns:

     ItemName, UoMName, D1_UoMName, D2_UoMName and D3_UoMName

How to retreive the above columns? and How do I identify the UoMName for the Corresponding UoMID, D1_UoMID, D2_UoMID and D3_UoMID?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦幻的心爱 2024-11-26 23:50:08

使用别名，您可以多次连接到同一个表并区分不同的结果加入。试试这个：

SELECT
    ItemName,
    d0.UoMName,
    d1.UoMName as D1_UoMName,
    d2.UoMName as D2_UoMName,
    d3.UoMName as D3_UoMName
from TableB tb
join TableA d0 on d0.UoMID = tb.UoMID
join TableA d1 on d1.UoMID = tb.D1_UoMID
join TableA d2 on d2.UoMID = tb.D2_UoMID
join TableA d3 on d3.UoMID = tb.D3_UoMID;

Using aliases, you can join to the same table multiple times and distinguish the results of the different joins. Try this:

SELECT
    ItemName,
    d0.UoMName,
    d1.UoMName as D1_UoMName,
    d2.UoMName as D2_UoMName,
    d3.UoMName as D3_UoMName
from TableB tb
join TableA d0 on d0.UoMID = tb.UoMID
join TableA d1 on d1.UoMID = tb.D1_UoMID
join TableA d2 on d2.UoMID = tb.D2_UoMID
join TableA d3 on d3.UoMID = tb.D3_UoMID;

回复收藏 0 原文

泼猴你往哪里跑 2024-11-26 23:50:08

SELECT ta.UoMName, ta1.UoMName, ta2.UoMName ta3.UoMName 
FROM TableA AS ta, TableA AS ta1, TableA AS ta2, TableA AS ta3, TableB AS tb
WHERE ta.UoMID = tb.UoMID
AND ta.UoMID = tb.UoMID
AND ta1.UoMID = tb.D1_UoMID
AND ta2.UoMID = tb.D2_UoMID
AND ta3.UoMID = tb.D3_UoMID

如果您想多次使用同一个表，则必须为表指定别名。

SELECT ta.UoMName, ta1.UoMName, ta2.UoMName ta3.UoMName 
FROM TableA AS ta, TableA AS ta1, TableA AS ta2, TableA AS ta3, TableB AS tb
WHERE ta.UoMID = tb.UoMID
AND ta.UoMID = tb.UoMID
AND ta1.UoMID = tb.D1_UoMID
AND ta2.UoMID = tb.D2_UoMID
AND ta3.UoMID = tb.D3_UoMID

You have to give aliases to the tables, if you want to use the same table more than once.

回复收藏 0 原文

短叹 2024-11-26 23:50:08

SELECT 
     b.ItemName,
     (SELECT a1.UoMName FROM TableA a1 WHERE a1.UoMID = b.UoMID) as UoMName,
(SELECT a2.UoMName FROM TableA a2 WHERE a2.UoMID = b.D1_UoMID) as D1_UoMName,
(SELECT a3.UoMName FROM TableA a3 WHERE a3.UoMID = b.D1_UoMID) as D3_UoMName,
(SELECT a4.UoMName FROM TableA a4 WHERE a4.UoMID = b.D1_UoMID) as D3_UoMName,
FROM TableB b

希望这有效

SELECT 
     b.ItemName,
     (SELECT a1.UoMName FROM TableA a1 WHERE a1.UoMID = b.UoMID) as UoMName,
(SELECT a2.UoMName FROM TableA a2 WHERE a2.UoMID = b.D1_UoMID) as D1_UoMName,
(SELECT a3.UoMName FROM TableA a3 WHERE a3.UoMID = b.D1_UoMID) as D3_UoMName,
(SELECT a4.UoMName FROM TableA a4 WHERE a4.UoMID = b.D1_UoMID) as D3_UoMName,
FROM TableB b

Hope this works

回复收藏 0 原文

~没有更多了~