MSSQL error: Unable to bind the identifier xxxxx composed of multiple parts

Today, a colleague asked me an SQL question. Although I was able to quickly locate and solve the problem, I felt a bit confused. Because it cannot be clearly explained (many are based on assumptions). By the way, let's record it. I hope people with clear principles can answer one or two questions. The original SQL statement is not easy to describe the problem. I will use a simple example to describe this

Today, a colleague asked me an SQL question. Although I was able to quickly locate and solve the problem, I felt a bit confused. Because it cannot be clearly explained (many are based on assumptions). By the way, let's record it. I hope people with clear principles can answer one or two questions. The original SQL statement is not easy to describe the problem. I will use a simple example to describe this

Today, a colleague asked me an SQL question. Although I was able to quickly locate and solve the problem, I felt a bit confused. Because it cannot be clearly explained (many are based on assumptions). By the way, let's record it. I hope people with clear principles can answer one or two questions.

The original SQL statement is not easy to describe the problem. I will use a simple example to describe the problem. Please refer to the following SQL:

Select type, COUNT (1) FROM sys. objects tGROUP BY sys. objects. type; message 4104, level 16, status 1, and 2nd rows cannot be bound with the identifier "sys. objects. type ". If The English version is used, The following error will be reported: Message 4104, level 16, status 1, row 3rd The multi-part identifier "sys. objects. type" cocould not be bound.

The query object sys. objects in the SQL script uses the alias t. However, if the table name is used on the GROUP BY field name, the preceding error is reported. I tried the following methods and the results are correct.

Select type, COUNT (1) FROM sys. objectsGROUP by type; select type, COUNT (1) FROM sys. objectsGROUP BY sys. objects. type; select type, COUNT (1) FROM sys. objects tGROUP BY t. type; select type, COUNT (1) FROM sys. objects tGROUP BY type;

My own explanation (hypothesis) is: because an alias is used for the table to be queried, when the SQL statement is parsed, for example, the SQL statement reported above, GROUP BY sys. objects. type is parsed into group by t. sys. objects. type; this will cause the compiler to fail to recognize the object t. sys. objects. type. Because there is no such column at all. But as I said at the beginning, there is a feeling of being unclear. It seems like this is the case, and the explanation seems reasonable, but I always feel unconvincing, so I designed the following small example:

TEST; test from sys. objects; t. name from test t, sys. objects s t. t. name t. name from test t, sys. objects s t. TEST. name message 4104, level 16, status 1, 2nd rows 18 19 cannot bind an identifier consisting of multiple parts "TEST. name ".

It is certain that if an alias is used for a table, the alias must be used instead of the original table name when the fields in the referenced table need to be different, otherwise, an error occurs when you cannot bind an identifier consisting of multiple parts.