Sql Server子查询、GROUP BY分组、having查询

1.子查询
语法：select ... from 表1 where 字段1 > (子查询语句)
例句：查看年龄比“李斯文”大的学员，要求显示学员信息；
select * from stuinfo where stuage > (select stuage from stuinfo where stuname = '李斯文')
实战：查询北京市下面的所有地区
select name from area where cityid = (select code from city where name = '北京市')
--注意：将“子查询”和“比较运算”符联合使用，必须保证子查询返回的值不能多余一个
----------------------------------------------------------------------------------------------------------------------
2.BETWEEN在某个范围内进行查询

语法：select ... from 表1 where 字段 between 参数1 and 参数2
select * from area where id between 1 and 20
GO
select * from area where id not between 10 and 20
----------------------------------------------------------------------------------------------------------------------
3.IN和NOT IN 子查询
select * from area where cityid in('110100','130100')
GO
select * from area where cityid in (select code from city where code = '110100')
GO
select * from area where cityid not in(select code from city where code = '110100')
----------------------------------------------------------------------------------------------------------------------
4.GROUP BY分组查询
select provinceid,avg(id) from city group by provinceid
GO
select provinceid,code from city group by provinceid,code
--注意：在使用Group By关键字时，在select列表中可以指定的项目是有限制的，
--1）被分组的列
--2）每个分组返回一个值的表达式，例如一个列名作为参数的聚合函数（sum()、avg()、max()、min()、count()）。
----------------------------------------------------------------------------------------------------------------------
5.HAVING子句分组筛选
select provinceid from city group by provinceid having count(*)>0
----------------------------------------------------------------------------------------------------------------------
注意：WHERE————》GROUP BY————》HAVING次序
select 部门编号,count(*) from 表
where 工资 >= 2000
group by 部门编号
having count(*)>1
----------------------------------------------------------------------------------------------------------------------
6.distinct去除重复
select name, distinct cityid from area