面试最常出现的sql语句

A表有个ID属性，B表也有ID属性，查询出a表中有这个ID而b表中没有这个ID的数据（三种实现方式）；

简单容易理解但效率很低

Select * from a where ID not in (select ID from B);

左外查询

Select * from a left join b on a.id=b.id where b.id is null;

效率最高的

Select * from a where (select count(1) from b where b.id=a.id)=0

查询每个栏目下价格最高的

Select cat_id,count(good_price) from group by cat_id

查询价格最高的商品

Select good_id,count(good_price) from group by good_id

Where 和having的异同点

查询两门或两门以上不及格的学生

Select sid,sname,sum(score<60) as s from stu group by sname having s>2;

用where表达的话是

Select sid,sname,sum(score<60) as s from stu group by sname where sum(score<60)>2;

个人博客：java1024家园