sql语句是从右到左执行的←
优化原则:能迅速排查的语句放在右边
显示部门平均工资低于2000的部门号和平均工资,这里不用子查询而是用having
--having往往和group by结合使用,对分组查询的结果进行筛选
select avg(sal) avg_sal,deptno from emp
group by deptno having avg(sal) < 2000
--group by having order by同时出现的顺序
select avg(sal) avg_sal,deptno from emp
group by deptno
having avg(sal) < 2000
order by avg(sal)
--上面介绍过执行顺序right-to-left,所以下面这个是不行的
select avg(sal) avg_sal,deptno from emp
group by deptno having avg_sal < 2000
--在选择列中如果有列,表达式,和分组函数,那么这些列和表达式必须有一个出现在
--group by子句中,否则出错
--1、没有group by
select avg(sal) avg_sal,deptno from emp having avg_sal < 2000
--2、group by中的内容不是select中的
select avg(sal) avg_sal,deptno from emp
group by user_id having avg_sal < 2000
sqlserver不支持多字段in not-in
就像:
delete from user_geo_simplified
where (user_id,user_gor_simplified) in (select [user_id],user_gor_simplified from #temp1)
and timeDate not in (select max(timeDate) from user_geo_simplified group by[user_id],user_gor_simplified having count(*)>1)
但是可以这样:
Select a.user_id ,a.geo,Max(a.timeDate) as timeDate INTO USER_GEO_LEFT_5
from (SELECT user_id ,geo ,timeDate FROM user_geo_simplified) a
group by a.user_id ,a.geo
order by USER_ID,timeDate
SELECT * FROM USER_GEO_LEFT_5
事实上group by子句的执行总是在当前group内
--查询每个部门的平均工资
select avg(sal) as avg_sal, deptno from emp
group by deptno
</pre><br /><br /><p></p><p></p><pre name="code" class="sql">select distinct item_id into #temp from train_item
select [tianchi].[dbo].[train_user].user_id,
[tianchi].[dbo].[train_user].item_id,
[tianchi].[dbo].[train_user].behavior_type,
[tianchi].[dbo].[train_user].user_geohash,
[tianchi].[dbo].[train_user].timeDate
into train_user_on_certain_item
from train_user, #temp
where train_user.item_id = #temp.item_id
alter table item_category add aa bigint
update item_category set aa = item_category
alter table item_category drop column aa
以上是一些基本操作的例子
/*查看列*/
select * from information_schema.columns where table_name = '表名'
/*添加列*/
alter table 表名 add 列名 varchar(55)
/*删除列*/
alter table 表名 drop column 列名
/*修改列名称*/
exec sp_rename '表名.字段名' , '新名', 'column'
/*修改列类型*/
alter table 表名 alter column 列名 varchar(22)