SQL技巧
来源:互联网 发布:淘宝店铺首页打不开 编辑:程序博客网 时间:2024/05/21 09:13
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
意义
%
*
匹配0或多个字符
-
?
匹配一个字符
\
\
转义字符
无
[]
选择匹配
例:matches ‘*Sp’;匹配以任何字符开始,以Sp结束的字段值
matches ‘?l*’;匹配第一个字符任意,第二个字符为l,其余字符任意的字段值
matches ‘[A-N]*’;匹配以A到N的字符开始,其余字符任意的字段值
matches ‘*[sS]*’;匹配含有s或S的字段值,扩展以下可用于case insensitive查询
like ‘%\%%’;匹配含有%的字段值
表一: 表二:
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
Array 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(Array,’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-0Array-24 Array00
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 Array00 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-0Array-24", Array00);
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,
yArray 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(Array, 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(yArray*fei) yArray,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(yArray*fee) yArray,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的情况时,先对每条记录进行旋转,然后在累加求和。
- sql技巧
- SQL技巧
- SQl技巧
- sql技巧
- sql 技巧
- sql技巧
- sql技巧
- sql技巧
- SQL技巧
- sql技巧
- sql 技巧
- SQL技巧
- SQL技巧
- sql技巧
- sql-技巧
- SQL技巧
- SQL 技巧
- SQL技巧
- Firefox os初体验
- Ubuntu 环境变量及 ADB 配置
- ExtJs使用总结(非常详细)
- DbContext 和ObjectContext两者的区别
- 修改my.cnf文件导致mysql启动不了的问题
- SQL技巧
- Android通讯录查询篇--ContactsContract.Data开篇
- 程序员装b指南(转)
- 软件架构师不等同于资深程序员
- 使用Entity Framework 4进行代码优先开发-续
- java设计模式总结篇--结构型模式
- 英文培训有感
- 关于fatal error LNK1120: 1 unresolved externalsd而解决方法
- C# - 函数参数的传递(值传递和引用传递)