尝试在 SQL 中使用表别名

Question

我是一名图形设计师，尽力理解表别名，但它不起作用。 到目前为止

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 种颜色的颜色代码、十六进制和名称我正在寻找的。我可以让它工作，但它只提取名字、代码和十六进制，我只是没有看到我做错了什么。

Answer 1

您的问题是您仅链接颜色表中的一条记录，因为您的 SQL 中只有一个 JOIN。该记录将与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

这里发生的情况是我链接颜色表四次，针对组合表中的每个 color_id 字段链接一次。为此，我每次都需要为表名添加别名，以便知道在返回列的列表中使用四种可能的颜色实例中的哪一种。另外，如果一个或多个 color_id 列可能为空，我会使用 OUTER JOIN。如果 INNER JOIN 发生这种情况，则整行将从结果集中删除。

Answer 2

您可以使用表别名来减少所需的输入量 - 通过添加如下内容：

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; 

通过为表组合定义表别名cb，您可以使用该较短的别名在 SELECT 和语句的其他部分中，而不必总是拼写出整个表名。

但你的问题确实在于 JOIN - 你只加入一次，但你期望得到四个结果......

你需要做的是这样的：

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.qecolourid4

Answer 3

这不是一个详尽的答案，但您的问题与您使用 JOIN 的方式有关。表和列别名不影响输出结果集。

您四次选择相同的字段名称，这就是您得到奇怪结果的原因。

Answer 4

这些都很棒，但由于某种原因，当我尝试使用它们时，我在页面中收到错误：

[Microsoft][ODBC Microsoft Access Driver] 查询表达式中的语法错误（缺少运算符）

我想我了解如何使用现在我已经使用了表别名，但由于某种原因，即使我确信它应该可以工作，但页面不喜欢它。