php - 如何正确编写MySQL别名？

对于所有关心的人，我发现我需要重新阅读有关查询的基础知识。排序后，我意识到我的代码中有很多错误。

我有一个正在尝试建立的查询，关于歧义性，我抛出了一个又一个错误。谁能告诉我如何正确执行MySQL别名？

到目前为止，这是我的代码。直到我将联接放到那里之前，它都运行良好。我怀疑这是因为我在同一查询中多次引用“葡萄酒”，但这可能是许多问题之一。

$query =
"SELECT
    wine_id,
    wine_name,
    winery_name,
    region_name,
    year,
    variety
FROM
    wine AS w,
    winery,
    region,
    grape_variety
JOIN
    wine
ON
    grape_variety.variety_id = wine.wine_id
JOIN
    wine_variety
ON
    wine.wine_id = wine_variety.variety_id
WHERE
    winery.region_id = region.region_id
AND
    wine.winery_id = winery.winery_id";

if (!empty($wineName)) {
    $query .= " AND wine_name = '{$wineName}'";
}

if (!empty($wineryName)) {
    $query .= " AND winery_name = '{$wineryName}'";
}

// If the user has specified a region, add the regionName
// as an AND clause
if (isset($regionName) && $regionName != "All") {
    $query .= " AND region_name = '{$regionName}'";
}

// If the user has specified a variety, add the grapeVariety
// as an AND clause
if (isset($grapeVariety) && $grapeVariety != "Riesling") {
    $query .= " AND variety = '{$grapeVariety}'";
}

最佳答案

尝试通过以下方式进行操作：

SELECT w.wine_id, w.wine_name,  winery_name, region_name,  year, variety
FROM
    wine AS w
    join winery on w.winery_id = winery.winery_id
    join region on winery.region_id = region.region_id,
    join grape_variety on grape_variety.variety_id = w.wine_id