有一个千万条记录的表,需对该表按2种不同的条件进行统计,然后并成一张表,如果分别统计均不超过30秒,如果用left join连接查询则要40多分钟.
查询一 运行不超过30秒
查询二 运行不超过30秒 查询一 left join 查询二 on 条件 则需40分钟以上.目前我采用把查询结果写入2张临时表中,然后对2张临时表进行left join查询,约需70秒.但我个人觉得这方法比较笨,请教各位有什么好办法?
查询一 运行不超过30秒
查询二 运行不超过30秒 查询一 left join 查询二 on 条件 则需40分钟以上.目前我采用把查询结果写入2张临时表中,然后对2张临时表进行left join查询,约需70秒.但我个人觉得这方法比较笨,请教各位有什么好办法?
1、建立索引
2、使用连接选项 根据你的情况 选择
Hash Join
或
Merge Join
或
Nested Loop Join
表结构:
数据表(tb1):
部门,编号,日期,数量,状态,来源...
编号表(tb2):
部门,编号
查询语句:
查询一
select 部门,编号 from tb1 where 日期 between '2011-09-01' and '2011-11-05' and 状态='1' and 编号 not in (select 编号 from tb2) and substring(编号,1,10) between '1000000000' and '2000000000' group by 部门,编号
查询二
select 部门,编号,sum(数量) 数量 from tb1 where 日期 between '2011-09-01' and '2011-11-05' and 状态='1' and 来源='0' group by 部门,编号想得到部门,编号,数量
编号
FROM tb1
WHERE 日期 BETWEEN '2011-09-01' AND '2011-11-05'
AND 状态 = '1'
AND NOT EXISTS ( SELECT 编号
FROM tb2 WHERE tb1.编号 = tb2.编号)
AND SUBSTRING(编号, 1, 10) BETWEEN '1000000000'
AND '2000000000'
GROUP BY 部门 ,
编号
inner join,Merge Join
select 部门,编号,sum(数量) 数量
from tb1 where 日期 between '2011-09-01' and '2011-11-05' and 状态='1'
and 来源='0'
group by 部门,编号
having 编号 not in (select 编号 from tb2) and substring(编号,1,10) between '1000000000' and '2000000000'
from tb1 left join tb2 on tb1.编号=tb2.编号
where 日期 between '2011-09-01' and '2011-11-05' and 状态='1' and 来源='0'
and substring(编号,1,10) between '1000000000' and '2000000000'
and tb2.编号 is null
group by 部门,编号
from tb1 where 日期 between '2011-09-01' and '2011-11-05' and 状态='1'
group by 部门,编号
having 编号 not in (select 编号 from tb2) and substring(编号,1,10) between '1000000000' and '2000000000'
谢谢你,我给出的条件用你的方法非常好,但由于我粗心第二个查询是没有 状态='1'
的条件,为了便于大家更好的帮助我重新列出我的查询:
查询一
select 部门,编号 from tb1 where 日期 between '2011-09-01' and '2011-11-05' and 状态='1' and 编号 not in (select 编号 from tb2) and substring(编号,1,10) between '1000000000' and '2000000000' group by 部门,编号
查询二
select 部门,编号,sum(数量) 数量 from tb1 where 日期 between '2011-09-01' and '2011-11-05' and 来源='0' group by 部门,编号想得到部门,编号,数量
如果有的话 可以合并一下语句因为第一二句都由重复between '2011-09-01' and '2011-11-05'
直接LEFT JOIN 显然不可取。
那这样怕你的效率不会很高 自己验证一下 不行的话就用临时表来弄好了
select 部门,编号,sum(case when 来源='0' then 数量 else 0 end) 数量
from tb1 t where 日期 between '2011-09-01' and '2011-11-05' and exists(select 1 from tb1 where 编号=t.编号 and 状态='1')
group by 部门,编号
having 编号 not in (select 编号 from tb2) and substring(编号,1,10) between '1000000000' and '2000000000'
select 部门,编号,sum(case when 来源='0' then 数量 else 0 end) 数量
from tb1 t where exists(select 1 from tb1 where 编号=t.编号 and 状态='1')
and 编号 not in (select 编号 from tb2) and substring(编号,1,10) between '1000000000' and '2000000000'
and 日期 between '2011-09-01' and '2011-11-05'
group by 部门,编号
非常感谢你!
你最后给出的方法不行,因为sum没有 状态='1'的条件