select
t2.*
from
(select *
from
(select '周一' as 周 union all select '周二' union all
select '周三' union all select '周四' union all select '周五' )t
cross join
(select 員工 from table)t2)t2
left join
table t3 on t2.員工=t3.員工 and t2.周=t3.周
where t3.員工 is null
t2.*
from
(select *
from
(select '周一' as 周 union all select '周二' union all
select '周三' union all select '周四' union all select '周五' )t
cross join
(select 員工 from table)t2)t2
left join
table t3 on t2.員工=t3.員工 and t2.周=t3.周
where t3.員工 is null
if object_id('tb') is not null drop table tb
create table tb (name varchar(50),week varchar(50))
insert into tb
select 'a','周一' union all
select 'a','周二' union all
select 'b','周三' union all
select 'b','周四' union all
select 'b','周五'select * from tbselect ww.name,ww.week from (
select distinct * from (
select '周一' as week union all
select '周二' union all
select '周三' union all
select '周四' union all
select '周五') as w
cross join (select name from tb) as tm ) as ww
left join tb as tt on tt.week=ww.week
where tt.name <> ww.name order by tt.namedrop table tb/*
a 周三
a 周四
a 周五
b 周一
b 周二
*/
insert into tb values('A' , '周一')
insert into tb values('A' , '周二')
insert into tb values('A' , '周三')
insert into tb values('A' , '周五')
insert into tb values('B' , '周二')
insert into tb values('B' , '周三')
goselect t1.* from
(
select distinct m.name , n.week from tb m ,
(
select '周一' week union all
select '周二' week union all
select '周三' week union all
select '周四' week union all
select '周五' week
) n
) t1 where not exists(select 1 from tb t2 where t2.name = t1.name and t2.week = t1.week)drop table tb/*
name week
-------------------------------------------------- ----
A 周四
B 周四
B 周五
B 周一(所影响的行数为 4 行)
*/