单表查询
①select * from (select a.id,a.name,a.singer,a.special,rownum rn from songsInfo a order by id ) where rn > 0 and rn < 10;
②select * from (select t.*,rownum rn from (select a.id,a.name,a.singer,a.special from songsInfo a ) t order by id ) where rn > 0 and rn < 10;
多表查询
select * from
(
select t.*,rownum rn from
(
select a.id,a.name,b.name as singer,c.name as special from voiceInfo a, singerInfo b, specialInfo c where a.singer = b.id and a.special = c.id
) t
) where rn > 0 and rn < 10;
修改 replace的使用(将描述中含有“制热”的改为“制冷”)
update sysConfig set description = replace (description , '制热', '制冷');
group by 的使用(查询同一张员工表中工资相同的员工详细信息)
select * from persons where salary in (select salary from persons group by salary having count(salary) > 1) order by salary
group by 的使用(查询当天采集时间的所有记录)
--这里的采集时间是varchar2类型的
select temperature,humidity,windvalue,sunvalue,COLLECTTIME from (
select temperature, humidity, windvalue,sunvalue,
to_char(to_date(da.collecttime,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') COLLECTTIME
from
da_temandhhum_info da,DA_DEVICE_INFO de
where da.deviceid=de.id and de.deviceposition=1
and
to_char(to_date(collecttime,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD') = to_char(sysdate,'YYYY-MM-DD')
group by to_char(to_date(da.collecttime,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') order by COLLECTTIME)
说明:在使用group by 时,有一个规则需要遵守,即出现在select列表中的字段,如果没有在组函数中,那么必须出现在group by 子句中。
(select中的字段不可以单独出现,必须出现在group语句中或者在组函数中。)
oracle
获取表中前十条数据
select * from (select * from 表名) where rownum <=10;