在一个查询中多次选择一个表 [英] Select one table multiple times in one Query

问题描述

我有三张桌子.我想进行选择查询，以产生结果.
请帮助我...

颜色
1. ID
2.名称
3.代码

值(1，"RED"，#FF0000")；
值(2，"GREEN"，#00FF00")；
值(3，蓝色"，#0000FF")；

百分比
1. ID
2.百分比
3.说明

值(1，0，零");
值(2、25，更轻")；
值(3、50，"Mid")；
值(4，75，较高"));
值(5，100，"Full");

SampleTable
1. ID
2.名称
3. Color_Id1
4. Percentage_Id1
5. Color_Id2
6. Percentage_Id2
7. Color_Id3
8. Percentage_Id3
9.说明
值(1，"ZYX"，1、2、2、3、3、2，"DDDDDDDDDDDDD")；

I have Three Tables. I want to make a Select Query which will produce the result.
Please Help me ...

Color
1. Id
2. Name
3. Code

Values( 1, "RED", "#FF0000");
Values( 2, "GREEN", "#00FF00");
Values( 3, "BLUE", "#0000FF");

Percentage
1. Id
2. Percentage
3. Description

Values( 1, 0, "Zero" );
Values( 2, 25, "Lighter" );
Values( 3, 50, "Mid" );
Values( 4, 75, "Higher" );
Values( 5, 100, "Full" );

SampleTable
1. Id
2. Name
3. Color_Id1
4. Percentage_Id1
5. Color_Id2
6. Percentage_Id2
7. Color_Id3
8. Percentage_Id3
9. Description
Values( 1, "ZYX", 1, 2, 2, 3, 3, 2, "DDDDDDDDDDD");

I want : ( 1, "ZYX",  "RED", "#FF0000",  25, "Lighter" ,
                      "GREEN", "#00FF00", 50, "Mid"  ,
                      "BLUE", "#0000FF", 25, "Lighter", "DDDDDDDD")

推荐答案

请尝试以下查询.

Hi,

Please try the following query.

SELECT     SampleTable.ID, SampleTable.Name, Color.Name AS Expr1, Color.Code, Percentage.Percentage, Percentage.Description, Color_1.Name AS Expr2, 
                      Color_1.Code AS Expr3, Percentage_1.Percentage AS Expr4, Percentage_1.Description AS Expr5, Color_2.Name AS Expr6, Color_2.Code AS Expr7, 
                      Percentage_2.Percentage AS Expr8, Percentage_2.Description AS Expr9
FROM         SampleTable INNER JOIN
                      Color ON SampleTable.Color_Id1 = Color.ID INNER JOIN
                      Percentage ON SampleTable.Percentage_Id1 = Percentage.ID INNER JOIN
                      Color AS Color_1 ON SampleTable.Color_Id2 = Color_1.ID INNER JOIN
                      Percentage AS Percentage_1 ON SampleTable.Percentage_Id2 = Percentage_1.ID INNER JOIN
                      Color AS Color_2 ON SampleTable.Color_Id3 = Color_2.ID INNER JOIN
                      Percentage AS Percentage_2 ON SampleTable.Percentage_Id3 = Percentage_2.ID

希望对您有所帮助.

谢谢，
Viprat

I hope it might be help you.

Thanks,
Viprat

谢谢.
我用...........
解决了问题
(字段名称已更改，但结构尚可)

Thanks to you.
I Solved the problem with ...........

(Field Name change but structure is OK)

Select 
 [SampleTable].[Id] AS [Id],
 [SampleTable].[Name] AS [Name],
 [SampleTable].[Color_Id1] AS [Color Id1],
 [Color].[Id] AS [Color_Id],
 [Color].[Color_Name] AS [Color_Color Name],
 [Color].[Description] AS [Color_Description],
 [SampleTable].[Percentage_Id1] AS [Percentage Id1],
 [Percentage].[Id] AS [Percentage_Id],
 [Percentage].[Percentage] AS [Percentage_Percentage],
 [Percentage].[Description] AS [Percentage_Description],
 [SampleTable].[Color_Id2] AS [Color Id2],
 [Color4].[Id] AS [Color4_Id],
 [Color4].[Color_Name] AS [Color4_Color Name],
 [Color4].[Description] AS [Color4_Description],
 [SampleTable].[Percentage_Id2] AS [Percentage Id2],
 [Percentage5].[Id] AS [Percentage5_Id],
 [Percentage5].[Percentage] AS [Percentage5_Percentage],
 [Percentage5].[Description] AS [Percentage5_Description],
 [SampleTable].[Color_Id3] AS [Color Id3],
 [Color6].[Id] AS [Color6_Id],
 [Color6].[Color_Name] AS [Color6_Color Name],
 [Color6].[Description] AS [Color6_Description],
 [SampleTable].[Percentage_Id3] AS [Percentage Id3],
 [Percentage7].[Id] AS [Percentage7_Id],
 [Percentage7].[Percentage] AS [Percentage7_Percentage],
 [Percentage7].[Description] AS [Percentage7_Description],
 [SampleTable].[Details] AS [Details]

from  [SampleTable] 
 INNER JOIN [Color] AS [Color4] ON [SampleTable].[Color_Id2] = [Color4].[Id]
 INNER JOIN [Percentage] AS [Percentage5] ON [SampleTable].[Percentage_Id2] = [Percentage5].[Id]
 INNER JOIN [Color] AS [Color6] ON [SampleTable].[Color_Id3] = [Color6].[Id]
 INNER JOIN [Percentage] AS [Percentage7] ON [SampleTable].[Percentage_Id3] = [Percentage7].[Id]
 , [Color], [Percentage]
where 
 [Color].[Id] = [SampleTable].[Color_Id1] AND
 [Percentage].[Id] = [SampleTable].[Percentage_Id1] 

==========================

============================

这篇关于在一个查询中多次选择一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！