-
写出删除表中重复记录的语句 oracle
2. 写一条SQL,查询姓张学生中平均成绩大于75的学生信息delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by
select * from student where name in (select name from student where name like '张%' group
by name having avg(score) > 75)
3. order by 多条件排序
ORDER子句按一个或多个(最多16个)字段排序查询结果,可以是升序(ASC)也可以是降序(DESC),缺省是升序。ORDER子句通常放在SQL
语句的最后。4. case when 语法ORDER子句中定义了多个字段,则按照字段的先后顺序排序:
select * from tablename order by column1 desc, column2 asc, column3 desc;
在column2中有'AAA','BBB'两种值。让AAA在前,BBB在后排序的情况:
方法一 select * from tablename order by column1 desc,case column2 when 'AAA' then 0 when 'BBB' then 1 end,column3 desc;
方法二 select * from tablename order by column1 desc,CHARINDEX(column2,'AAA,BBB') , column3 desc
1。
- select name,
- case
- when birthday<'1981' then 'old'
- when birthday>'1988' then 'yong'
- else 'ok' END YORN
- from lee;
2。
- select NAME,
- case name
- when 'sam' then 'yong'
- when 'lee' then 'handsome'
- else 'good' end
- from lee;
当然了case when语句还可以复合
3。
- select name,birthday,
- case
- when birthday>'1983' then 'yong'
- when name='lee' then 'handsome'
- else 'just so so ' end
- from lee;