--建表和实例数据语句
--柜员签到日期表
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 20070103
1011 20070104
1011 20070107
1012 20070102
1012 20070105
1012 20070107
1013 20070101
1013 20070102
1013 20070103
1013 20070104(所影响的行数为 10 行)
*/
--柜员签到日期表
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 20070103
1011 20070104
1011 20070107
1012 20070102
1012 20070105
1012 20070107
1013 20070101
1013 20070102
1013 20070103
1013 20070104(所影响的行数为 10 行)
*/
select tr_date,id from tb_trdate t1,(select id from tb_clerkonwork group by id) t2
EXCEPT
select tr_date,id from tb_clerkonwork
dawugui 大侠的SQL语句运行没有问题。
no1francis 的语句运行时提示“在关键字 'EXCEPT' 附近有语法错误。”
no1francis用的2005