SQL-FUNCTION

Table of contents

  1. SQL使用类比
  2. 带上序号的分页
    1. MYSQL
    2. ORACLE
    3. DB2
  3. 函数使用
    1. 1.判断列字段是否包含关键字.

SQL使用类比

带上序号的分页

MYSQL

第一页

select * from (select d.*,@rownum := @rownum + 1 as num from (
    select * from mm_subcompany_tc
    ) d, (SELECT @rownum := 0) myTempTable LIMIT 10 ) TT where num >= 1 ;

第二页

select * from (select d.*,@rownum := @rownum + 1 as num from (
    select * from mm_subcompany_tc
    ) d, (SELECT @rownum := 0) myTempTable LIMIT 20 ) TT where num >= 11 ;

ORACLE

KingBase 的Oracle模式。

第一页

select * from (select d.*,rownum as num from (
      select * from mm_subcompany_tc
    ) d where rownum <= 10 )where num >= 1 ;

第二页

select * from (select d.*,rownum as num from (
      select * from mm_subcompany_tc
    ) d where rownum <= 20 )where num >= 11 ;

DB2 

SELECT *FROM (
    SELECT row_number () OVER (ORDER BY CC_BRAND.BRAND_CODE) AS rown,
        MS.* FROM mm_subcompany_tc MS ) AS A
WHERE A.ROWN >= 1 AND A.ROWN <= 10;

第二页

SELECT *FROM (
    SELECT row_number () OVER (ORDER BY CC_BRAND.BRAND_CODE) AS rown,
        MS.* FROM mm_subcompany_tc MS ) AS A
WHERE A.ROWN >= 11 AND A.ROWN <= 20;

函数使用

1.判断列字段是否包含关键字.

判断列的值里是否包含 BP2101 关键字。

MYSQL:

select locate('BP2101',powerrole) > 0 from t_test;

Oracle

select instr(powerrole, 'BP2101') > 0 from t_test;

kingBase:

select strpos(powerrole,'BP2101')>0  from t_test ;