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
解决方案 »
- 有关XSD,请问 SQL Server 接受 sqltypes:datetime 或 sql:smalldatetime 类型的值吗?
- 菜鸟问题:如何判断当前安装的SQL2000是个人版还是企业版?
- 求解!!
- mysql LOAD DATA LOCAL INFILE导入数据 表里面没有结果 也不报错
- 关于日期时间的提取合并
- 一个存储过程里调用另一个存储过程产生不执行问题
- 求助!使用“数据转换服务查询设计器”时出现的奇怪错误
- group by生成综合报表的sql问题(200分答谢,明天再加100分)!
- 紧急求教!!!!!如何在VC环境下检测SQLSERVER?(已经贴了一个月了)
- 在控制面板中OracleStartORCL无法启动,提示权限不够?
- SQLServer 的全文索引 的 全文目录出了问题,重新生成没有项计数,而且目录大小总是0MB
- 寻找SQL达人求教!
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 行)
*/