在having后面可以跟子查询如:
select id from sc group by id having sum(case when score<60 then 1 else 0 end)>1;
或者是select c# from sc group by c# having count()=(select count() from student group by s#);
left join 后面的on和where的区别
on后面跟and则当做连接条件,左侧的表依然会全部显示出来,
on后面跟where的话则代表俩表都要过滤,即左表可能也会显示不完全的信息,一般用在左外连接(只有左表有的数据)
例子如下:
select a.,b. from a left join b on a.name=b.name and b.name is null;
a.name a.age b.name b.age
wang 3 NULL NULL
li 4 NULL NULL
zhao 6 NULL NULL
当and后面的条件是规定左表的话则没有任何意义,如果是规定右表则有效果
select a.,b. from a left join b on a.name=b.name where b.name is null;
a.name a.age b.name b.age
zhao 6 NULL NULL
实时关注大数据最新动态