SQL查询报错求助：‘unrecognized token’错误原因及问题排查

Hey there! Let's break down what's going wrong with your SQL queries and get them working smoothly. That error message is pointing straight to the issue—your database doesn't recognize 2014_Jan_Pivot as a valid table name, and here's why:

The Root Cause #

Virtually all SQL databases enforce strict rules for naming tables, columns, and other identifiers: you can't start an identifier with a number. Your tables 2014_Jan_Pivot and 2015_Jan_Pivot both begin with 2, which confuses the database engine—it interprets the leading number as a numeric value instead of a table reference.

Fixed Queries #

To resolve this, you need to wrap your numeric-starting table names in the appropriate quote characters for your database. Here are the most common implementations:

Option 1: For SQLite/MySQL (use backticks) #

-- Fixed Query 1
select Total2014, Total2015 
From `2014_Jan_Pivot` 
inner join `2015_Jan_Pivot` on `2014_Jan_Pivot`.PremiseCity2014 = `2015_Jan_Pivot`.PremiseCity2015;

-- Fixed Query 2
select Total2014, Total2015, Total2016, Total2017 
From `2014_Jan_Pivot`;

Option 2: For PostgreSQL/Oracle (use double quotes) #

-- Fixed Query 1
select Total2014, Total2015 
From "2014_Jan_Pivot" 
inner join "2015_Jan_Pivot" on "2014_Jan_Pivot".PremiseCity2014 = "2015_Jan_Pivot".PremiseCity2015;

-- Fixed Query 2
select Total2014, Total2015, Total2016, Total2017 
From "2014_Jan_Pivot";

Option 3: For SQL Server (use square brackets) #

-- Fixed Query 1
select Total2014, Total2015 
From [2014_Jan_Pivot] 
inner join [2015_Jan_Pivot] on [2014_Jan_Pivot].PremiseCity2014 = [2015_Jan_Pivot].PremiseCity2015;

-- Fixed Query 2
select Total2014, Total2015, Total2016, Total2017 
From [2014_Jan_Pivot];

Quick Heads-Up #

Even though your second query only references one table, it would still throw the same error because 2014_Jan_Pivot starts with a number—don't forget to apply the fix there too!

内容的提问来源于stack exchange，提问作者Shiva Shahmohammadi