以下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也可以用在自建函数中。
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 耗资源、时间并且写语句麻烦,不知道有没有其它替代方法?