sql各类的些语句

以下是在informix上跑的有些与mssql不样

-----------------------------------------------------------------------------
裸表和标准表之间可以通过语句
alter table TabName type（standard/raw)
来相互转换

raw table称为裸表，对裸表的所有操作都不记入日志；
table是标准表，对标准表的每一次修改都会写入日志。

------------------------------------------------------------------------------
----创建方法
create function yw_phone_group(adsl_id decimal(12,0)) --定义个参数
returning varchar(20); --返回值类型
define nbr_no varchar(20); --定义nbr_no
let nbr_no=''; --
--脏读：指当一个事物正在访问数据，并对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事物也访问了这个数据，然后使用了这个数据。因为这个数据是还没提交的数据，那么另外一个事物读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。
set isolation to dirty read;
if (adsl_id is null or adsl_id='') then return null;end if; --

foreach
select distinct c.product_no into nbr_no
from crmdb@ol_crm:u_service_grp_member a,crmdb@ol_crm:u_service_grp_member b,crmdb@ol_crm:pd_pdinfo c
where adsl_id=a.product_id and a.group_id=b.group_id
and a.member_role_id='1' and b.member_role_id='2'
and b.product_id=c.product_id and c.remove_flag='F'
end foreach

return nbr_no;
end function
;
---------------------------------------------------------
set isolation dirty read ;
select a.payment_date,a.amount,a.acct_id,a.state
from bsn_acctdb@ol_cash_flash:payment a
where
a.party_role_id=3172
into temp temp_payment_all with no log
;
select *
from temp_payment_all a
where a.state='5JB'
and a.payment_date between '2009-02-01 00:00:00' and '2009-02-28 23:59:59'
into temp temp_payment with no log ;


create index index_temp_payment on temp_payment(acct_id) ;
update statistics for table temp_payment; --更新索引

unload to "D:\abc.txt" select a.payment_date,a.amount,b.acct_code
from temp_payment a,bsn_acctdb@ol_cash:acct b
where a.acct_id=b.acct_id ;

drop table temp_payment_all ; --用完临时表要删除！！
drop table temp_payment ;

--------------------------------------------------------
set isolation dirty read;
select a.* from testdb:temp_240_wyq a,statdb:tab_ztephs_yfqf b
where a.acc_nbr[5,12]=b.acc_nbr[3,10] ---同substring a.acc_nbr[5,12]=b.acc_nbr[3,10] acc_nbr为varchar
into temp temp_031 with no log;
-------------------------------------------------------------
create function fhl_u_offer_ehome_comp_name
(
s_product_id decimal(12,0)
)
returning lvarchar;

define s_return lvarchar;
define v_offer_name varchar(255);

set isolation dirty read;
---初始化
let s_return='';
let v_offer_name='';

---输入参数异常判断
if s_product_id is null then
return '';
end if;

if s_product_id='' then
return '';
end if;

---列举出所有商品包标识并剔重

foreach select distinct trim(c.OFFER_NAME) into v_offer_name
from crmdb@ol_crm:U_PRODUCT_OFFER_INSTANCE_DETAIL a,
crmdb@ol_crm:U_PRODUCT_OFFER_INSTANCE b,
dataclear@ol_crm:zjp_ehome c
where a.INSTANCE_TYPE='10A'
and b.state='00A'
and a.INSTANCE_ID=s_product_id
and a.PRODUCT_OFFER_INSTANCE_ID=b.PRODUCT_OFFER_INSTANCE_ID
and b.offer_comp_ID=c.OFFER_ID
---追加
if v_offer_name='' or v_offer_name is null then
let v_offer_name='';
end if;
let s_return=s_return||v_offer_name||'/'; --||为字符串连接符用于char或varchar
--和mssql “的”+“的” 相同

end foreach

return s_return;

end function
;
------------------------------------------------------------------------
-----
select distinct type,offer_name,offer_id,product_no,phone_no,phone_id,extend(completed_date,year to month) completed_date,extend(date2,year to month) date2 from tab_06 into temp tab_07 with no log;

---drop table tab_07
--EXTEND（DATE/DATETIME EXPRESSION，[first to last]）返回经过调整的日期或日期时间值
delete from tab_07 where extend(completed_date,year to month)<extend(date2,year to month);

----------------------------------------------------------------------------
{--nvl函数：从两个表达式返回一个非 null 值。
--语法

--NVL(eExpression1, eExpression2)

--参数
eExpression1, eExpression2
如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，
则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。
如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回 .NULL.。
--返回值类型
字符型、日期型、日期时间型、数值型、货币型、逻辑型或 null 值
--说明
在不支持 null 值或 null 值无关紧要的情况下，可以使用 NVL( ) 来移去计算或操作中的 null 值。
}
select business_id,

nvl(sum(case when offer_name ='L1' and new_flag='T' and service_id='1' then sl_count else 0 end),0) t1,
nvl(sum(case when offer_name ='L1' and new_flag='F' and service_id='1' then sl_count else 0 end),0) t2,
nvl(sum(case when offer_name ='L1' and service_id='2' then sl_count else 0 end),0) t3,


from tab_03
group by 1
---------------------------------------------------------------------------------------------------------
--nvl(sum 和 sum(nvl(t1,0))区别注意最终结果截取方式
select business_id,org_name,
sum(nvl(t1,0)) t1,
sum(nvl(t2,0)) t2,
sum(nvl(t3,0)) t3,
sum(nvl(t4,0)) t4,
sum(nvl(t5,0)) t5,
sum(nvl(t6,0)) t6,
sum(nvl(t7,0)) t7,
sum(nvl(t8,0)) t8,
sum(nvl(t9,0)) t9,
sum(nvl(t10,0)) t10,
sum(nvl(t11,0)) t11,
sum(nvl(t12,0)) t12,
sum(nvl(t13,0)) t13,
sum(nvl(t14,0)) t14,

from tab_05
group by 1,2
into temp temp_tcyh_info with no log;
--------------------------------------------------------------------------------------
--UNION 指令的目的是将两个 SQL 语句的结果合并起来。
--UNION ALL 这个指令的目的也是要将两个 SQL 语句的结果合并在一起。
--UNION ALL 和 UNION 不同之处在于 UNION ALL 会将每一笔符合条件的资料都列出来，
-- 无论资料值有无重复.
select distinct product_no,attr_val from tab_04 where product_no<>attr_val
union
select distinct product_no,physics_no from tab_02 where product_no<>physics_no
---------------------------------------------------------------------------------
--DATETIME datetime（DATE/DATETIME EXPRESSION）FIRST TO LAST 返回表达式代表的日期时间值
--INTERVAL interval（DATE/DATETIME EXPRESSION）FIRST TO LAST 返回表达式代表的时间间隔值
--EXTEND（DATE/DATETIME EXPRESSION，[first to last]）返回经过调整的日期或日期时间值
select distinct cust_id,acct_id,extend(payment_date,year to month)payment_date from tab_02
into temp tab_03 with no log;
---------------------------------------------------------------------------------
以下来源于网络：


--inner join查询得出的结果是表1和表2的交集
--经测试, inner join与join得出的结果相同,left outer join,right outer join分别与left join,right join结果相同。

--一、外连接
{1.概念：包括左向外联接、右向外联接或完整外部联接
左连接：left join 或 left outer join
--left join是以join关键字左边的表（即tbl1）为基准输出的，在右边的表（tbl2）不存在对应行时输出NULL。
右连接：right join 或 right outer join
--right join是以join关键字右边的表（即tbl2）为基准输出的，在左边的表（tbl1）不存在对应行时输出NULL。
}
--------------------------------------------------------------------------------------
--找了好久才找到相关的数据库查询前Ｎ条记录的方法，帖出来大家共同分享……
--1. ORACLE
SELECT * FROM TABLE1 WHERE ROWNUM<=N
--2. INFORMIX
SELECT FIRST N * FROM TABLE1
--3. DB2
SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N
--DB2
SELECT COLUMN FROM TABLE FETCH FIRST N ROWS ONLY
--4. SQL SERVER
SELECT TOP N * FROM TABLE1
--5. SYBASE
SELECT TOP N * FROM TABLE1
--6. mysql:
select * from table_name limit N
------------------------------------------------------------------------------------