select a.name, a.date
from (
select *
from
(select distinct name from tableA) as a,
(select distinct date from tableA) as b
) as a
left join tabelA b on a.name=b.name and a.date=b.date
where b.name is null
from (
select *
from
(select distinct name from tableA) as a,
(select distinct date from tableA) as b
) as a
left join tabelA b on a.name=b.name and a.date=b.date
where b.name is null
insert @T2
SELECT 'A','2009-6-11' UNION ALL
SELECT 'B','2009-6-11' UNION ALL
SELECT 'C','2009-6-11' UNION ALL
SELECT 'A','2009-6-12' UNION ALL
SELECT 'B','2009-6-12' UNION ALL
SELECT 'D','2009-6-12'declare @DateCnt int
select @DateCnt=count( distinct convert(varchar(10),date,120)) from @T2select *
from @T2 a
where
(select count(1)
from @T2
where a.name=name)<@DateCntname date
---------- -----------------------
C 2009-06-11 00:00:00
D 2009-06-12 00:00:00(2 行受影响)
declare @tb table (name nvarchar(10),[date] datetime )
insert into @tb select 'a','2009-6-11'
union all select 'b','2009-6-11'
union all select 'c','2009-6-11'
union all select 'a','2009-6-12'
union all select 'b','2009-6-12'
union all select 'd','2009-6-12'
select * from @tb where name not in(
select name from @tb
group by name having COUNT(*)>=2)
order by name desc(6 行受影响)
name date
---------- -----------------------
d 2009-06-12 00:00:00.000
c 2009-06-11 00:00:00.000(2 行受影响)