尝试在SQL中使用表别名
我是一个图形设计师，尽我最大的努力去理解表的别名，但是它不起作用。这是我到目前为止所知道的：
SELECT colours.colourid AS colourid1,
combinations.manufacturercolourid AS colourmanid1,
colours.colourname AS colourname1,
colours.colourhex AS colourhex1,
combinations.qecolourid2 AS colouridqe2,
colours.colourid AS colourid2,
colours.colourname AS colourname2,
colours.colourhex AS colourhex2,
colours.colourid AS colourid3,
combinations.qecolourid3 AS colouridqe3,
colours.colourname AS colourname3,
colours.colourhex AS colourhex3,
colours.colourid AS colourid4,
combinations.qecolourid4 AS colouridqe4,
colours.colourname AS colourname4,
colours.colourhex AS colourhex4,
combinations.coloursupplierid
FROM combinations
INNER JOIN colours
ON colours.colourid = combinations.manufacturercolourid; 现在，想法是在颜色查找表中，id将从查找表中提取颜色代码、十六进制和名称，以便我可以提取我正在查找的4种颜色的颜色代码、十六进制和名称。我可以让它工作，但它只拉出名字，代码和十六进制，我只是看不出我做错了什么。
回答 4
Stack Overflow用户
发布于 2010-06-15 05:13:04
您的问题是，您只链接了colours表中的一条记录，因为您的SQL中只有一个连接。该记录将匹配manufacturer_colour_id指定的颜色。
您还可能有一个进一步的问题，因为您的组合表似乎不是正确的范式(尽管我可能是错的，因为我不知道您试图表示的数据的实际性质)。
如果我正确理解了您的问题，那么解决方案(使用您当前的表结构)将更类似于：
SELECT C1.colourid AS colourid1,
CMB.manufacturercolourid AS colourmanid1,
C1.colourname AS colourname1,
C1.colourhex AS colourhex1,
CMB.qecolourid2 AS colouridqe2,
C2.colourid AS colourid2,
C2.colourname AS colourname2,
C2.colourhex AS colourhex2,
C3.colourid AS colourid3,
CMB.qecolourid3 AS colouridqe3,
C3.colourname AS colourname3,
C3.colourhex AS colourhex3,
C4.colourid AS colourid4,
CMB.qecolourid4 AS colouridqe4,
C4.colourname AS colourname4,
C4.colourhex AS colourhex4,
CMB.coloursupplierid
FROM combinations CMB
LEFT OUTER JOIN colours C1
ON C1.colourid = CMB.manufacturercolourid
LEFT OUTER JOIN colours C2
ON C2.colourid = CMB.qecolourid2
LEFT OUTER JOIN colours C3
ON C3.colourid = CMB.qecolourid3
LEFT OUTER JOIN colours C4
ON C4.colourid = CMB.qecolourid4这里发生的事情是，我将colours表链接了四次，一次是针对组合表中的每个colour_id字段。为此，我每次都需要给表名加上别名，以便知道在返回的列的列表中使用四个可能的颜色实例中的哪一个。另外，在一个或多个colour_id列可能为空的情况下，我使用了外连接。如果这种情况发生在内部联接中，则整个行将从结果集中删除。
Stack Overflow用户
发布于 2010-06-15 05:05:31
您可以使用表别名来减少所需的打字量-通过添加类似以下内容：
SELECT
cl.colourid AS colourid1,
cb.manufacturercolourid AS colourmanid1,
cl.colourname AS colourname1,
... and so on.....
FROM
combinations AS cb
INNER JOIN
colours AS cl ON cl.colourid = cb.manufacturercolourid; 通过为表combinations定义一个表别名cb，您可以在SELECT和语句的其他部分中使用这个较短的别名，而不必总是拼写出整个表名。
但你的问题实际上在于连接--你只加入一次，但你希望得到四个结果。
你需要做的就是这样：
SELECT
col1.colourid AS colourid1,
cb.manufacturercolourid AS colourmanid1,
col1.colourname AS colourname1,
col1.colourhex AS colourhex1,
cb.qecolourid2 AS colouridqe2,
col2.colourid AS colourid2,
col2.colourname AS colourname2,
col2.colourhex AS colourhex2,
col2.colourid AS colourid3,
cb.qecolourid3 AS colouridqe3,
col3.colourname AS colourname3,
col3.colourhex AS colourhex3,
col3.colourid AS colourid4,
cb.qecolourid4 AS colouridqe4,
col4.colourname AS colourname4,
col4.colourhex AS colourhex4,
cb.coloursupplierid
FROM
combinations cb
INNER JOIN colours AS col1 ON col1.colourid = cb.manufacturercolourid
INNER JOIN colours AS col2 ON col2.colourid = cb.qecolourid2
INNER JOIN colours AS col3 ON col3.colourid = cb.qecolourid3
INNER JOIN colours AS col4 ON col4.colourid = cb.qecolourid4Stack Overflow用户
发布于 2010-06-15 05:08:15
这不是一个详尽的答案，但您的问题与您如何使用连接有关。表和列的别名不会影响输出结果集。
您选择相同的字段名称四次，这就是为什么您会得到奇怪的结果。
https://stackoverflow.com/questions/3040883
复制相似问题
腾讯云开发者
