MS Sql:每次生成不包括某些行的多个表

Question

假设我有一个员工表，每个员工只属于一个部门。

我需要用ms编写一个查询，以便能够生成多个表，而在每个表中，我希望将员工排除在一个部门之外。

即

John   IT
Helen  HR
Doris  IT
Peter  SALES
Paul   SALES
Joane  HR

结果：

Helen  HR     not it
Peter  SALES  not it
Paul   SALES  not it
Joane  HR     not it

John   IT     not hr
Doris  IT     not hr
Peter  SALES  not hr
Paul   SALES  not hr

John   IT   not sales
Helen  HR   not sales
Doris  IT   not sales
Joane  HR   not sales

你能帮我得出这个结果吗？提前谢谢。

结果我不需要太多的表格。就像上面的一样。

Answer 1

简单的回答，做一个UNION ALL

select emp, dep, 'not it' from tablename where dep <> 'IT'
UNION ALL
select emp, dep, 'not hr' from tablename where dep <> 'HR'
UNION ALL
select emp, dep, 'not sales' from tablename where dep <> 'SALES'

更一般的答案，做一个SELF JOIN

select t1.emp, t1.dep, 'not ' || t2.dep
from tablename t1
  join (select distinct dep from tablename) t2 ON t1.dep <> t2.dep
order by t2.dep

其中||是ANSI连接。也许MS SQL Server还会有别的东西？

SQL>create table tablename (emp varchar(10), dep varchar(10));
SQL>insert into tablename values ('John','IT');
SQL>insert into tablename values ('Helen','HR');
SQL>insert into tablename values ('Doris','IT');
SQL>insert into tablename values ('Peter','SALES');
SQL>insert into tablename values ('Paul','SALES');
SQL>insert into tablename values ('Joane','HR');
SQL>select t1.emp, t1.dep, 'not ' || t2.dep
SQL&    from tablename t1
SQL&      join (select distinct dep from tablename) t2 ON t1.dep <> t2.dep
SQL&    order by t2.dep
SQL&;
emp        dep
========== ========== ==============
John       IT         not HR
Doris      IT         not HR
Peter      SALES      not HR
Paul       SALES      not HR
Helen      HR         not IT
Peter      SALES      not IT
Paul       SALES      not IT
Joane      HR         not IT
John       IT         not SALES
Helen      HR         not SALES
Doris      IT         not SALES
Joane      HR         not SALES

                 12 rows found

如果希望部门名称用小写表示，可以执行LOWER(t2.dep)。