- 论坛徽章:
- 1
|
informix SQL汇总
【续】
NULL值的使用
RDSQL中字段缺省值为空;并且对数值型的0和空值,以及字符型的空白和空值区别对待。
数值表达式中某个变为空,则整个表达式值为空;
聚合函数中,对空值忽略不计,若全部为空值,除COUNT(*)返回0外,其余返回空值。
布尔表达式中,结果可能为“未知”(见下表)。如TRUE AND NULL 结果为 “未知”,对“未知”结果在RDSQL中看作不符合查询条件。
and
T
F
?
or
T
F
?
not
T
T
F
?
T
T
T
T
T
F
F
F
F
?
F
T
F
?
F
T
?
?
F
?
?
T
?
?
?
?
结合上表,分析下列子句 ,其中n1=20;n2为空;n3=30。结果如右。
where n1*n2 < 1000 and n3 = 30; 结果:不符合查询条件
where n1*n2 < 1000 or n3 = 30; 结果:符合查询条件
ORDER BY子句中的空值,每一个空值为一组。
INSERT或UPDATE时,可使用关键字NULL/null表示空值。
字段是否可以为空,由CREATE TABLE语句中是否有NOT NULL指定或由ALTER修改。
Q:select count(*) from t1和select count(c1) from t1是否一样?
字符查找,主要使用LIKE和MATCHES。
LIKE
MATCHES
意义
%
*
匹配0或多个字符
-
?
匹配一个字符
\
\
转义字符
无
[]
选择匹配
例:matches ‘*Sp’;匹配以任何字符开始,以Sp结束的字段值
matches ‘?l*’; 匹配第一个字符任意,第二个字符为l,其余字符任意的字段值
matches ‘[A-N]*’; 匹配以A到N的字符开始,其余字符任意的字段值
matches ‘*[sS]*’; 匹配含有s或S的字段值,扩展以下可用于case insensitive查询
like ‘%\%%’; 匹配含有%的字段值
用SQL语句求表一中的关于name有多少不同的num,结果如表二。
表一: 表二:
id name num name count1
1 AA 1 CC 2
2 AA 2 BB 2
3 AA 3 AA 3
4 AA 1
5 AA 2
6 BB 4
7 BB 5
8 BB 4
9 BB 5
10 CC 6
11 CC 6
12 CC 7
SQL语句如下:
create table t1
(
id smallint,
name char(10),
num smallint
);
insert into t1 values(1,'AA',1);
insert into t1 values(2,'AA',2);
insert into t1 values(3,'AA',3);
insert into t1 values(4,'AA',1);
insert into t1 values(5,'AA',2);
insert into t1 values(6,'BB',4);
insert into t1 values(7,'BB',5);
insert into t1 values(8,'BB',4);
insert into t1 values(9,'BB',5);
insert into t1 values(10,'CC',6);
insert into t1 values(11,'CC',6);
insert into t1 values(12,'CC',7);
A:select name ,count(distinct num) from t1 group by name;
4)使用旋转矩阵,将表一中关于id在不同月份的费用,由纵向变为横向。
其中表一对一个id某个月份的记录数可能>;1。表一:
id d1 fee费用(分)
1 2000-01-24 100
1 2000-04-24 100
2 2000-02-24 200
2 2000-06-24 200
3 2000-04-24 400
4 2000-04-24 400
5 2000-05-24 500
6 2000-06-24 600
7 2000-09-24 900
8 2000-11-24 1100
表二:
id 1月份费用 2月份费用 …… … … 12月份费用
1 100 0 0 100 0 0 0 0 0 0 0 0
2 0 200 0 0 0 200 0 0 0 0 0 0
3 0 0 0 400 0 0 0 0 0 0 0 0
4 0 0 0 400 0 0 0 0 0 0 0 0
5 0 0 0 0 500 0 0 0 0 0 0 0
6 0 0 0 0 0 600 0 0 0 0 0 0
7 0 0 0 0 0 0 0 0 900 0 0 0
8 0 0 0 0 0 0 0 0 0 0 1100 0
SQL语句:
create table t3
(
id smallint,
d1 datetime year to day,
fee int
);
insert into t3 values(1,"2000-01-24", 100);
insert into t3 values(1,"2000-04-24", 100);
insert into t3 values(2,"2000-02-24", 200);
insert into t3 values(2,"2000-06-24", 200);
insert into t3 values(3,"2000-04-24", 400);
insert into t3 values(4,"2000-04-24", 400);
insert into t3 values(5,"2000-05-24", 500);
insert into t3 values(6,"2000-06-24", 600);
insert into t3 values(7,"2000-09-24", 900);
insert into t3 values(8,"2000-11-24", 1100);
create table t4 –旋转矩阵
(
m_code smallint,
y1 smallint,
y2 smallint,
y3 smallint,
y4 smallint,
y5 smallint,
y6 smallint,
y7 smallint,
y8 smallint,
y9 smallint,
y10 smallint,
y11 smallint,
y12 smallint
);
insert into t4 values(1, 1,0,0,0,0,0,0,0,0,0,0,0);
insert into t4 values(2, 0,1,0,0,0,0,0,0,0,0,0,0);
insert into t4 values(3, 0,0,1,0,0,0,0,0,0,0,0,0);
insert into t4 values(4, 0,0,0,1,0,0,0,0,0,0,0,0);
insert into t4 values(5, 0,0,0,0,1,0,0,0,0,0,0,0);
insert into t4 values(6, 0,0,0,0,0,1,0,0,0,0,0,0);
insert into t4 values(7, 0,0,0,0,0,0,1,0,0,0,0,0);
insert into t4 values(8, 0,0,0,0,0,0,0,1,0,0,0,0);
insert into t4 values(9, 0,0,0,0,0,0,0,0,1,0,0,0);
insert into t4 values(10,0,0,0,0,0,0,0,0,0,1,0,0);
insert into t4 values(11,0,0,0,0,0,0,0,0,0,0,1,0);
insert into t4 values(12,0,0,0,0,0,0,0,0,0,0,0,1);
--方法一
select id,month(d1) month,sum(fee) fei from t3 group by 1,2 into temp aa;
select id,
sum(y1*fei) y1,sum(y2*fei) y2,sum(y3*fei) y3,sum(y4*fei) y4,
sum(y5*fei) y5,sum(y6*fei) y6,sum(y7*fei) y7,sum(y8*fei) y8,
sum(y9*fei) y9,sum(y10*fei) y10,sum(y11*fei) y11,sum(y12*fei) y12
from aa, t4 where aa.month = t4.m_code
group by id order by id
--方法二
select id,
sum(y1*fee) y1,sum(y2*fee) y2,sum(y3*fee) y3,sum(y4*fee) y4,
sum(y5*fee) y5,sum(y6*fee) y6,sum(y7*fee) y7,sum(y8*fee) y8,
sum(y9*fee) y9,sum(y10*fee) y10,sum(y11*fee) y11,sum(y12*fee) y12
from t3, t4 where month(d1) = t4.m_code
group by id order by id
方法一和方法二的结果一样,但有所区别:
方法一中是先对id某个月的钱进行累加,然后进行旋转;
方法二中在表一对一个id某个月份的记录数可能>;1的情况时,先对每条记录进行旋转,然后在累加求和。 |
|