取中间数据及连接

1.MSSQLServer2000、Oracle、DB2取中间几条数据

1）在MSSQLServer2000中取前5用top，取第六名开始3个人的信息。首先将前5名的主键取出来，在检索时排除这5名的前3人即可。

select top 3 * from employee where fnumber not in (select top 5 fnumber from employee order by fsalary desc)order by fsalary desc);

2）在MSSQLServer2005中可通过row_number()

select row_number() over(order by fsalarydesc),fnumber,fname,fsalary,fage from employee where (row_number() over(orderby fsalary desc))>=3 and (row_number() over(order by fsalary desc))<=5;

报错:开窗函数只能出现在select或 order by子句中

修正:用子查询返回第3行到第5行的数据

select * from (select row_number() over(order by fsalary desc) as rownum,fnumber,fname,fsalary,fage from employee) as a where a.rownum >=3 anda.rownum <=5;

3）Oracle中支持开窗函数row_number()，用法与MSSQLServer2005相同，rownum 在Oracle中为保留字,将rownum 换为row_num，在Oracle中定义表的别名时不用as

select * from (select row_number() over(order by fsalary desc) as row_num,fnumber,fname,fsalary,fage from employee) a where a.row_num >=3 anda.row_num <=5;

4）Oracle中有默认的表示行号的rownum，方便查询，但是rownum只能查找前几条，取中间条数的数据时必须借助row_num()

select * from employee where rownum <= 6 order by fsalary desc;

 5） DB2

DB2中指出窗口函数row_number(),用法与上一样

取前几条可以用fetch first条数 rows only，而且在order by 之后

select *from employee order by fsalary desc fetch first 6 rows only;

没有直接查范围的功能函数，但是可以用子查询

select *from employee where fnumber not in(select * from employee order by fsalary desc fetch first 5 rows only)
order by fsalary desc fetch first 3 rows only;

2.连接

(1) MySQL:concat(参数…)，参数可以是多个，只有一个参数时，可以看做是将这个参数尝试转化为字符串类型值的函数。

concat_ws(分隔符,参数,参数…)

(2) MSSQLServer："+"拼接

(3) Oracle："||"拼接

concat(参数,参数):只支持2个参数，如果想支持多个可以用多个concat()嵌套使用

select concat(concat(concat('工号为:',fnumber),'的员工姓名是'),fname) from employee where fname is not null;