在所有的join和order by 的字段上建立索引。
在where中的大多数字段建立索引。
WHERE datecol >;= "this/date" AND datecol <= "that/date"
要比
WHERE datecol BETWEEN "this/date" AND "that/date" 慢
用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;
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);
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