INFORMIX SQL语句笔记（一）081020更新

zxe

INFORMIX SQL语句笔记（一）按季度统计出库金额

2008-10-15

以下INFORMIX SQL语句均经过测试，如非特别注明，测试版本为IDS11.5开发版。每一期结合一个具体应用详细介绍。
一、选择记录
1.选择前10条记录select first 10 * from hcrk
IDS7.30、IDS11支持
2.选择11-15条记录select skip 10 limit 5 * from hcrk
IDS7.30不支持，IDS11支持
select first 5 hcrkzlbh from (select first 15 hcrkzlbh from hcrk) order by hcrkzlbh desc
IDS7.30用此方法实现


二、导出数据库结构
dbschema -d db_name -ss

三、合成表名
select * from hxq(select id from tab_a)
informix 11支持，informix10、9、7均不支持。

四、日期字段
2008-10-13
1.直接从datatime字段（）截取其中一段
select extend(hcrkczsj,year to month) from hcrk
取年月

五、select语句中group by和order by使用的一些方法
2008-10-13
数据表hcrk，字段hcrkje（数值型*.**）、hcrkrq（字符型YYYYMM）
1.在group by、order by 中使用select字段的顺序号1、2、3…指定字段，select 字段可以是表达式、字段名、别名
例：select hcrkje,z_quarter(hcrkrq) as jd from hcrk group by 2 order by 2
当select字段为z_quarter(hcrkrq) as jd时
group by不允许表达式“z_quarter(hcrkrq)”，也不允许别名“jd”，但允许字段名“hcrkrq”和顺序号。
order by不允许字段名“hcrkrq”，但允许表达式“z_quarter(hcrkrq)”、顺序号和别名“jd”。

六、select语句中case、when的用法
算奇、偶月份
1.select case mod(hcrkrq[5,6] /2) when 1 then “1” else “2” from hcrk
2.select case when mod(hcrkrq[5,6] /2)=1 then “1” else “2” from hcrk
上面将表达式分别写在case后，when后，两种写法都可以。
case、when也可以用在自建函数中。

七、自建函数创建方法
1.建立函数（SPL？）可以象informix内置函数（如sum、max）一样调用
返回季度，传入参数rq，格式“YYYYMM”，字符型。
函数必须指定返回类型。
CREATE FUNCTION z_quarter ( rq VARCHAR(20) )

RETURNS INTEGER

DEFINE quarter INTEGER;

LET quarter = trunc((rq[5,6]-1)/3)+1;
(或者LET quarter = trunc(mod(rq-1,100)/3)+1

RETURN quarter;
END FUNCTION

如果返回类型为字符型，必须指定长度。否则返回单个字符。
CREATE FUNCTION z_quarter ( rq VARCHAR(20) )

RETURNS CHAR(20)

DEFINE quarter CHAR(20);
LET quarter = case trunc((rq-1)/3)+1 when 1 then "1季度" when 2 then "2季度" when 3 then "3季度"
when 4 then "4季度" else "N" end;

RETURN quarter;
END FUNCTION

可以用“||”连接字串（象select中连接字串一样）
例：LET quarter = case trunc((rq-1)/3)+1 when 1 then "1" when 2 then "2" when 3 then "3" when 4 then "4" else "N" end||"季度";

但是LET quarter = quarter||"季度";是不行的，报错。
2.删除函数
drop function z_quarter
3.调用函数
select trim(z_quarter(hcrkrq[5,6]) from hcrk group by 1 order by 1
trim()函数截去前后空格。

八、按季度统计出库金额
2008-10-14
1.group by、order by不用字段顺序号的方法时需二次select
select jd,sum(zje) from (SELECT sum(hcrkje) as zje, trunc((hcrkrq[5,6]-1)/3)+1 as jd FROM hcrk group by hcrkrq order by jd) group by jd order by jd
季度 金额
1 558
2 320
3 2277
4 399
2. group by、order by用字段顺序号
SELECT trunc((hcrkrq[5,6]-1)/3)+1 as jd,sum(hcrkje)

FROM hcrk group by 1 order by 1

3.将季度替换为中文名称
select
case trunc((hcrkrq[5,6]-1)/3)+1
when 1 then "1季度"
when 2 then "2季度"
when 3 then "3季度"
when 4 then "4季度"
else hcrkrq
end as jd, sum(hcrkje)
from hcrk group by 1 order by 1
4.使用自建函数
使用函数，当规则改变时，只需要修改一处，灵活，使用简单。
select z_quarter(hcrkrq[5,6]) as jd, sum(hcrkje)
from hcrk group by 1 order by 1

九、限定范围、区间between X and Y
2008-10-16
小的需要在前面。
select z_quarter(hcrkrq[5,6]) as jd, sum(hcrkje)
from hcrk where hcrkrq between “200701” and “200704” group by 1 order by 1
如果是datetime字段，必须按指定的日期格式。
select z_quarter(hcrkrq[5,6]) as jd, sum(hcrkje)
from hcrk where hcrkczsj between "2007-3-1 15:01:12" and "2007-7-15 11:01:23" group by 1 order by 1
not between不包括。

十、其它统计
2008-10-17
分网点按季度统计。
select hcrkwdbh,z_quarter(hcrkrq[5,6]) as jd, sum(hcrkje)
from hcrk group by 1,2 order by 1,2
2008-10-20
分网点按月份统计，在一条记录中显示12个月份的统计数据（这种方法耗资源、时间）
SELECT hcrkwdbh 网点编号,
(select sum(hcrkje) from hcrk where hcrkwdbh=b1.hcrkwdbh and hcrkrq="200701" Jan,
(select sum(hcrkje) from hcrk where hcrkwdbh=b1.hcrkwdbh and hcrkrq="200702" Feb,
(select sum(hcrkje) from hcrk where hcrkwdbh=b1.hcrkwdbh and hcrkrq="200703" Mar,
(select sum(hcrkje) from hcrk where hcrkwdbh=b1.hcrkwdbh and hcrkrq="200704" Apr,
(select sum(hcrkje) from hcrk where hcrkwdbh=b1.hcrkwdbh and hcrkrq="200705" May,
(select sum(hcrkje) from hcrk where hcrkwdbh=b1.hcrkwdbh and hcrkrq="200706" Jun
FROM hcrk b1 group by hcrkwdbh order by hcrkwdbh
耗资源、时间并且写语句麻烦，不知道有没有其它替代方法？



[ 本帖最后由 zxe 于 2008-10-20 17:21 编辑 ]

george002

不错，有些10支持就好了。

wuicpp

上面的功能10基本上都支持。

yunzhongyue

不错，一直用IDS7.3，像那个SKIP都没见过！

lrg223

学习了

happer_xc

功能不错，收藏先。
要测试一下大数据量的情况下效率如何。

sdccf

继续发表。

zxe

是啊！有什么好的测试工具？有谁知道的请不吝赐教，把文档放出来。谢谢！
如果可以，把测试数据也放出来。

zxe

DECODE函数
DECODE(test, a, a_value, b, b_value, ..., n, n_value, exp_m )在通常情况下，当 a 等于 test 时，DECODE 返回 a_value，当 b 等于 test 时返回 b_value，当 n 等于 test 时返回 n_value。
如果有若干表达式与 test 匹配，那么 DECODE 返回找到的第一个表达式的 n_value。如果没有表达式与 test 匹配，那么 DECODE 返回 exp_m；如果没有表达式与 test 匹配并且不存在 exp_m，那么 DECODE 返回 NULL。
SELECT decode(trunc((hcrkrq[5,6]-1)/3)+1,1,1,2,2,3,3,4,4) jd,sum(hcrkje) from hcrk
group by 1 order by 1

zxe

使用decode方法比前面的方法快约35%（下面每条语句运行5次，每次运行时间相差不超过20ms）
SELECT decode(trunc((hcrkrq[5,6]-1)/3)+1,1,1,2,2,3,3,4,4) jd,sum(hcrkje) from hcrk
group by 1 order by 1   耗时205ms
select z_quarter(hcrkrq[5,6]) as jd, sum(hcrkje)
from hcrk group by 1 order by 1   耗时145ms