/*
Desc: 如何找出柜员未签到的日期
*/
--建表和实例数据语句
--柜员签到日期表
CREATE TABLE tb_clerkonwork (id char (4) ,tr_date char (8))
GO
insert into tb_clerkonwork values ('1011','20070101')
insert into tb_clerkonwork values ('1011','20070102')
insert into tb_clerkonwork values ('1011','20070105')
insert into tb_clerkonwork values ('1011','20070106')
insert into tb_clerkonwork values ('1012','20070101')
insert into tb_clerkonwork values ('1012','20070103')
insert into tb_clerkonwork values ('1012','20070104')
insert into tb_clerkonwork values ('1012','20070106')
insert into tb_clerkonwork values ('1013','20070105')
insert into tb_clerkonwork values ('1013','20070106')
insert into tb_clerkonwork values ('1013','20070107')
GO
--交易日期表
CREATE TABLE tb_trdate (tr_date char (8))
GO
insert into tb_trdate values ('20070101')
insert into tb_trdate values ('20070102')
insert into tb_trdate values ('20070103')
insert into tb_trdate values ('20070104')
insert into tb_trdate values ('20070105')
insert into tb_trdate values ('20070106')
insert into tb_trdate values ('20070107')
GO
--要求的结果为
/*
1011 20070103
1011 20070104
1011 20070107
1012 20070102
1012 20070105
1012 20070107
1013 20070101
1013 20070102
1013 20070103
1013 20070104
*/
------解决方案--------------------
- SQL code
--建表和实例数据语句 --柜员签到日期表 CREATE TABLE tb_clerkonwork (id char (4) ,tr_date char (8)) GO insert into tb_clerkonwork values ('1011','20070101') insert into tb_clerkonwork values ('1011','20070102') insert into tb_clerkonwork values ('1011','20070105') insert into tb_clerkonwork values ('1011','20070106') insert into tb_clerkonwork values ('1012','20070101') insert into tb_clerkonwork values ('1012','20070103') insert into tb_clerkonwork values ('1012','20070104') insert into tb_clerkonwork values ('1012','20070106') insert into tb_clerkonwork values ('1013','20070105') insert into tb_clerkonwork values ('1013','20070106') insert into tb_clerkonwork values ('1013','20070107') GO --交易日期表 CREATE TABLE tb_trdate (tr_date char (8)) GO insert into tb_trdate values ('20070101') insert into tb_trdate values ('20070102') insert into tb_trdate values ('20070103') insert into tb_trdate values ('20070104') insert into tb_trdate values ('20070105') insert into tb_trdate values ('20070106') insert into tb_trdate values ('20070107') GO select distinct a.id , b.tr_date from tb_clerkonwork a, tb_trdate b where not exists (select 1 from tb_clerkonwork where id = a.id and tr_date = b.tr_date) order by a.id , b.tr_date--drop table tb_clerkonwork,tb_trdate/*id tr_date ---- -------- 1011 200701031011 200701041011 200701071012 200701021012 200701051012 200701071013 200701011013 200701021013 200701031013 20070104（所影响的行数为 10 行）*/
------解决方案--------------------
- SQL code
select tr_date,id from tb_trdate t1,(select id from tb_clerkonwork group by id) t2EXCEPTselect tr_date,id from tb_clerkonwork