34,594
社区成员
发帖
与我相关
我的任务
分享
A表
f_line f_produce f_date f_qty --大概1W条
1005 KX-AAA-R1 2014-01-03 80
1004 KX-BBB-R1 2014-01-05 90
B表
f_line f_produce f_date f_qty --大概1W条
1005 KX-AAA-R1 2014-01-03 80
1004 KX-CCC-R1 2014-01-05 90
A表和B表其他字段就不打出来了,主要需要数据的字段
--现在的sql
select a.f_linename,a.f_producemodel,left(a.f_date,7),sum(a.f_qty),b.f_linename,b.f_producemodel,left(b.f_date,7),sum(b.f_qty) from a
left join b on a.f_linename = b.f_linename and a.f_producemodel = b.f_producemodel
and left(a.f_date,7) = left(b.f_date,7) where a.f_batch = 28 and b.f_batch = 28
group by a.f_linename,a.f_producemodel,left(a.f_date,7),
b.f_linename,b.f_producemodel,left(b.f_date,7)
--A create table a(f_line varchar(30),f_produce varchar(30), f_date varchar(30),f_qty int,f_batch int) --B create table b(f_line varchar(30),f_produce varchar(30), f_date varchar(30),f_qty int,f_batch int) insert a select '1005','KX-AAA-R1','2014-01-03',80,28 union all select '1004','KX-BBB-R1','2014-01-05',90,28 insert b select '1005','KX-AAA-R1','2014-01-03',80,28 union all select '1004','KX-CCC-R1','2014-01-05',90,28
你创建表看看
select a.f_line,a.f_produce,left(a.f_date,7), sum(a.f_qty),b.f_line,b.f_produce,left(b.f_date,7),sum(b.f_qty) from a left join b on a.f_line = b.f_line and a.f_produce = b.f_produce and left(a.f_date,7) = left(b.f_date,7) --where a.f_batch = 28 and b.f_batch = 28 group by a.f_line,a.f_produce,left(a.f_date,7), b.f_line,b.f_produce,left(b.f_date,7)
有where 没 where 是2个不同的结果你看看
--A
create table a(f_line varchar(30),f_produce varchar(30),
f_date varchar(30),f_qty int,f_batch int)
--B
create table b(f_line varchar(30),f_produce varchar(30),
f_date varchar(30),f_qty int,f_batch int)
insert a
select '1005','KX-AAA-R1','2014-01-03',80,28 union all
select '1004','KX-BBB-R1','2014-01-05',90,28
insert b
select '1005','KX-AAA-R1','2014-01-03',80,28 union all
select '1004','KX-CCC-R1','2014-01-05',90,28
where a.f_batch = 28 and b.f_batch = 28
加了这代码查的结果就是
1005 KX-AAA-R1 2014-01 80 1005 KX-AAA-R1 2014-01 80
没加就是
1004 KX-BBB-R1 2014-01 90 NULL NULL NULL NULL
1005 KX-AAA-R1 2014-01 80 1005 KX-AAA-R1 2014-01 80
--sql
select a.f_line,a.f_produce,left(a.f_date,7), sum(a.f_qty),b.f_line,b.f_produce,left(b.f_date,7),sum(b.f_qty)
from a left join b on a.f_line = b.f_line and a.f_produce = b.f_produce and left(a.f_date,7) = left(b.f_date,7)
--where a.f_batch = 28 and b.f_batch = 28
group by a.f_line,a.f_produce,left(a.f_date,7), b.f_line,b.f_produce,left(b.f_date,7)
不加where我2个表有很多数据 我要where筛选2表的数据啊
select 字段.. into #a from a where 筛选 group by 合并
select 字段.. into #b from b where 筛选 group by 合并
select * from #a a left join #b b on a.f_linename = b.f_linename and
a.f_producemodel = b.f_producemodel and a.f_date = b.f_date
这样查询秒速 而且查出2888条数据 这才是left join
得到这些结果我还有2个表要关联的 用的是inner join 每次的结果都要放在临时表吗?
[/quote]
先把结果集放到临时表,然后再关联,确实能提高查询速度。select a.f_linename,a.f_producemodel,left(a.f_date,7),
sum(a.f_qty),b.f_linename,b.f_producemodel,left(b.f_date,7),sum(b.f_qty)
from a
left hash join b on a.f_linename = b.f_linename and a.f_producemodel = b.f_producemodel
and left(a.f_date,7) = left(b.f_date,7) where a.f_batch = 28 and b.f_batch = 28
group by a.f_linename,a.f_producemodel,left(a.f_date,7),
b.f_linename,b.f_producemodel,left(b.f_date,7)
[/quote]
效果一样
A表合并后有2888条数据 B表合并后有3444条数据 刚刚的sql查出来有3057条数据 就是inner join的效果
而不是left join的效果
我现在的sql是
select 字段.. into #a from a where 筛选 group by 合并
select 字段.. into #b from b where 筛选 group by 合并
select * from #a a left join #b b on a.f_linename = b.f_linename and
a.f_producemodel = b.f_producemodel and a.f_date = b.f_date
这样查询秒速 而且查出2888条数据 这才是left join
得到这些结果我还有2个表要关联的 用的是inner join 每次的结果都要放在临时表吗?
select a.f_linename,a.f_producemodel,left(a.f_date,7),
sum(a.f_qty),b.f_linename,b.f_producemodel,left(b.f_date,7),sum(b.f_qty)
from a
left hash join b on a.f_linename = b.f_linename and a.f_producemodel = b.f_producemodel
and left(a.f_date,7) = left(b.f_date,7) where a.f_batch = 28 and b.f_batch = 28
group by a.f_linename,a.f_producemodel,left(a.f_date,7),
b.f_linename,b.f_producemodel,left(b.f_date,7)