Transact-SQL 不明确的列名 [英] Transact-SQL Ambiguous column name

问题描述

我在使用 Microsoft SQL 2012 Server Management Studio 时遇到了 Transact-SQL 中的模糊列名称"问题.

I'm having trouble with the 'Ambiguous column name' issue in Transact-SQL, using the Microsoft SQL 2012 Server Management Studio.

我一直在查看 Stackoverflow 上已经发布的一些答案，但它们似乎对我不起作用，而且其中的一部分我根本不理解或失去了总体看法.

I´ve been looking through some of the answers already posted on Stackoverflow, but they don´t seem to work for me, and parts of it I simply don´t understand or loses the general view of.

执行以下脚本:

USE CDD

SELECT Artist, Album_title, track_title, track_number, Release_Year, EAN_code
FROM   Artists AS a INNER JOIN CD_Albumtitles AS c 
    ON     a.artist_id = c.artist_id
INNER JOIN Track_lists AS t 
    ON     c.title_id = t.title_id
WHERE track_title = 'bohemian rhapsody'

触发以下错误消息:

消息 209，级别 16，状态 1，第 3 行不明确的列名EAN_code".

Msg 209, Level 16, State 1, Line 3 Ambiguous column name 'EAN_code'.

并不是说这是一个包含艺术家姓名、专辑名称和曲目列表的 CD 数据库.表CD_Albumtitles"和Track_lists"都有一列，具有相同的 EAN 代码.EAN码是一个重要的国际码，用于唯一标识CD专辑，这也是我想继续使用它的原因.

Not that this is a CD database with artists names, album titles and track lists. Both the tables 'CD_Albumtitles' and 'Track_lists' have a column, with identical EAN codes. The EAN code is an important internationel code used to uniquely identify CD albums, which is why I would like to keep using it.

推荐答案

您需要将别名放在选择列表和 where 子句中的所有列的前面.您收到该错误是因为您当前拥有的列之一来自联接中的多个表.如果您为列设置别名，它基本上会选择其中一个表.

You need to put the alias in front of all the columns in your select list and your where clause. You're getting that error because one of the columns you have currently is coming from multiple tables in your join. If you alias the columns, it will essentially pick one or the other of the tables.

SELECT a.Artist,c.Album_title,t.track_title,t.track_number,c.Release_Year,t.EAN_code
FROM Artists AS a INNER JOIN CD_Albumtitles AS c
ON a.artist_id = c.artist_id
INNER JOIN Track_lists AS t
ON c.title_id = t.title_id
WHERE t.track_title = 'bohemian rhapsody'

这篇关于Transact-SQL 不明确的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！