一、创建表并插入数据
create table TestResults
( test_name char ( 20 ) not null,
test_step integer not null,
comp_date date ,
primary key ( test_name , test_step ))
insert into TestResults ( test_name , test_step , comp_date )
values ( 'Reading Skills' , 1 , '2017-1-5' ),
( 'Reading Skills' , 2 , '2017-1-8' ),
( 'Reading Skills' , 3 , '2017-1-6' ),
( 'Reading Skills' , 4 , '2017-1-4' ),
( 'Math Skills' , 1 ,null),
( 'Math Skills' , 2 ,null),
( 'Math Skills' , 3 ,null),
( 'Language Skills' , 1 ,null),
( 'Language Skills' , 2 ,null),
( 'Language Skills' , 3 ,null),
( 'Language Skills' , 4 ,null),
( 'Language Skills' , 5 , '2017-1-2' )
二、查询数据
1、解惑一
select distinct test_name
from TestResults as t1
where not exists ( select *
from TestResults as t2
where t1 . test_name = t2 . test_name
and t2 . comp_date is null)
2、解惑二（很巧妙，count(*)计算包含null的数据，count(*)计算日期的次数）
select test_name
from TestResults
group by test_name
having COUNT (*) = COUNT ( comp_date )
3、解惑三
select test_name ,
COUNT (*) as test_steps_needed ,
( COUNT (*) - COUNT ( comp_date )) as test_steps_missing
from TestResults
group by test_name
having COUNT (*) <> COUNT ( comp_date )
4、自己的写法
select distinct t2 . test_name
from TestResults as t2
where test_name not in ( select distinct t1 . test_name
from TestResults as t1
where t1 . comp_date is null
group by t1 . test_name )
版权声明：本文为jjjraddit原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。