如果缺少特定角色指定，则显示“空缺”

组合和条件聚合将为您提供所需的结果。union all

首先，创建并填充示例表（请在以后的问题中保存此步骤）：

CREATE TABLE Table1 (
  [role] varchar(2), 
  [designation] varchar(20), 
  [countries] varchar(14)
);
    
INSERT INTO Table1 ([role], [designation], [countries]) VALUES
('HR', 'Director', 'United States'),
('HR', 'Advisor', 'United States'),
('HR', 'Senior Executive', 'India'),
('HR', 'Assistant', 'Argentina'),
('HR', 'Assistant', 'United States'),
('IT', 'Alternative Director', 'Japan'),
('IT', 'Advisor', 'United Kingdom');

查询：

SELECT [role], [designation], [countries], [designation] as [output designation]
FROM Table1
UNION ALL
(
  SELECT role, NULL, NULL, 'Vacant'
  FROM Table1 
  GROUP BY role
  HAVING SUM(IIF(designation = 'Director', 1, 0)) = 0
) 
ORDER BY role, designation

结果：