Northwinds雇员姓名拼接及雇佣年份SQL查询问题求助

你遇到的问题大概率是不同数据库提取年份的语法差异导致的，以下针对常见数据库给出正确的SQL语句：

若使用PostgreSQL/Redshift等支持date_part的数据库 #

直接将年份提取字段添加到SELECT列表即可，注意给字段起别名：

SELECT firstname || ' ' || lastname AS "Name",
       date_part('year', hiredate) AS "Hire Year"
FROM employees;

也可以用EXTRACT函数实现相同效果：

SELECT firstname || ' ' || lastname AS "Name",
       EXTRACT(YEAR FROM hiredate) AS "Hire Year"
FROM employees;

若使用SQL Server/Access #

这类数据库不支持date_part，改用YEAR()函数提取年份，字符串拼接用+或更安全的CONCAT：

-- 基础拼接方式
SELECT firstname + ' ' + lastname AS "Name",
       YEAR(hiredate) AS "Hire Year"
FROM employees;

-- 用CONCAT处理可能的NULL值（SQL Server 2012+支持）
SELECT CONCAT(firstname, ' ', lastname) AS "Name",
       YEAR(hiredate) AS "Hire Year"
FROM employees;

若使用MySQL #

MySQL中字符串拼接推荐用CONCAT()，提取年份同样用YEAR()：

SELECT CONCAT(firstname, ' ', lastname) AS `Name`,
       YEAR(hiredate) AS `Hire Year`
FROM employees;

内容的提问来源于stack exchange，提问作者Samruddhi Mungal