多表复杂查询请教

[LOWGUN]

有四张表
SalesWasteBook （销售流水表）
------------------
SalesOrderCode
Customercode
ArrivedDateTime（时间）
......

Goods（商品信息）
GoodsName
GoodsCode
Price
.....

Customer（客户信息）
Customername
UpCustomerCode （上级客户编码）
Customercode（本身编码）
........
问题出在这里，比如很多超市有很多分场，现在要统计的是把所有的分场统计在一起，

SalesOrderDetail（销售明细）
---------------
SalesOrderCode
GoodsPrice
Quantity
.............
现在我要查询一段时间内的销售记录，这样查的话，能把所有的查出来，但是不能把分场的归到总的里面，怎么把分场的归到总的里面？

"SELECT B.GoodsCode,B.GoodsName, C.GoodsPrice,Sum(Quantity) As Amount from SalesWasteBook AS A, Goods As

B,SalesOrderDetail C ,Customer As D Where A.SalesOrderCode=C.SalesOrderCode And B.GoodsCode=C.GoodsCode and

C.GoodsCode='"&GoodsCode&"' and A.CustomerCode='"&CustomerCode&"' and ArrivedDateTime>=#"&StartTime&"# and

ArrivedDateTime <#"&EndTIme&"# Group by B.GoodsCode,B.GoodsName, C.GoodsPrice Order By GoodsName Asc"'



现在该怎么改进这个查询？数据库是ACCESS的

[LOWGUN]

怎么没有人接招呢？

[sun_power]

你不要用实例，编一个例子出来看看，写的具体点。如
a表有3个数据项(lie1,lie2,lie3)
lie1 lie2 lie3
1 a1 1987-1-1
1 a2 1987-1-2
2 b1 1999-1-1
2 b2 1999-2-2
2 b3 1999-2-2
3 c3 1993-1-1
a表和其他3个表有什么关系都写清楚了。
例如：a表中的lie1字段等于b表的XX字段

[dawugui]

对,把具体的数据和结果写出来.

[LOWGUN]

SalesWasteBook

SalesOrderCode Customercode ArrivedDateTime
Sal200600001 yyy001 2006-5-1
Sal200600002 yyy002 2006-5-2
Sal200600003 yyy001 2006-5-3
Sal200600004 yyy003 2006-5-4
Sal200600005 yyy001 2007-5-5
Sal200600006 yyy004 2006-5-5
...................

Goods(商品信息)
GoodsName GoodsCode Price
棉花 C0005 8.00
西瓜 C0006 2.00
花生 C0055 4.00
茄子 C0054 6.00
黄瓜 C0045 8.00
土豆 C0011 10.00
..............
Customer（客户信息）

Customername UpCustomerCode Customercode
张三 yyy yyy
李四 yyy yyy002
王五 aaa aaa000
赵钱 yyy yyy001
孙二 yyy yyy003
..............
SalesOrderDetail（销售明细）
---------------
SalesOrderCode GoodsPrice Quantity
Sal200600001 C0005 2
Sal200600001 C0006 2
Sal200600001 C0055 2
Sal200600001 C0045 2
Sal200600001 C0011 2
Sal200600002 C0045 10
Sal200600002 C0011 10
..................

比如我现在要查上级客户是张三在2000-1-1到2007-8-1的销售商品总合（可能他的多个下级销售了多笔）
结果要是这样的

名称 编码 单价 数量
--------------------------------------------
棉花 C0005 8.00 800
西瓜 C0006 2.00 150
花生 C0055 4.00 2000
茄子 C0054 6.00 1210
黄瓜 C0045 8.00 45454
土豆 C0011 10.00 545456

最后希望还能计算出合计的总金额

[dawugui]

create table SalesWasteBook(SalesOrderCode varchar(20),Customercode varchar(20),ArrivedDateTime datetime)
insert into SalesWasteBook values('Sal200600001', 'yyy001', '2006-5-1')
insert into SalesWasteBook values('Sal200600002', 'yyy002', '2006-5-2')
insert into SalesWasteBook values('Sal200600003', 'yyy001', '2006-5-3')
insert into SalesWasteBook values('Sal200600004', 'yyy003', '2006-5-4')
insert into SalesWasteBook values('Sal200600005', 'yyy001', '2007-5-5')
insert into SalesWasteBook values('Sal200600006', 'yyy004', '2006-5-5')
create table Goods(GoodsName varchar(10), GoodsCode varchar(10), Price int)
insert into Goods values('棉花', 'C0005', 8.00)
insert into Goods values('西瓜', 'C0006', 2.00)
insert into Goods values('花生', 'C0055', 4.00)
insert into Goods values('茄子', 'C0054', 6.00)
insert into Goods values('黄瓜', 'C0045', 8.00)
insert into Goods values('土豆', 'C0011', 10.00)
create table Customer(Customername varchar(10), UpCustomerCode varchar(10), Customercode varchar(10))
insert into Customer values('张三', 'yyy', 'yyy' )
insert into Customer values('李四', 'yyy', 'yyy002')
insert into Customer values('王五', 'aaa', 'aaa000')
insert into Customer values('赵钱', 'yyy', 'yyy001')
insert into Customer values('孙二', 'yyy', 'yyy003')
create table SalesOrderDetail(SalesOrderCode varchar(20),GoodsPrice varchar(10),Quantity int)
insert into SalesOrderDetail values('Sal200600001', 'C0005', 2 )
insert into SalesOrderDetail values('Sal200600001', 'C0006', 2 )
insert into SalesOrderDetail values('Sal200600001', 'C0055', 2 )
insert into SalesOrderDetail values('Sal200600001', 'C0045', 2 )
insert into SalesOrderDetail values('Sal200600001', 'C0011', 2 )
insert into SalesOrderDetail values('Sal200600002', 'C0045', 10 )
insert into SalesOrderDetail values('Sal200600002', 'C0011', 10 )
go
declare @Customername as varchar(10)
declare @dt1 as datetime
declare @dt2 as datetime
set @Customername = '张三'
set @dt1 = '2000-1-1'
set @dt2 = '2007-8-1'

select a.GoodsName , a.GoodsCode,a.Price,t.Quantity from Goods a,
(
select * from SalesOrderDetail where SalesOrderCode in
(
select SalesOrderCode from SalesWasteBook where ArrivedDateTime >= @dt1 and ArrivedDateTime <= @dt2 and Customercode in
(
select customercode from Customer where UpCustomerCode = (select UpCustomerCode from Customer where Customername = @Customername)
)
)
) t
where a.GoodsCode = t.GoodsPrice
--drop table SalesWasteBook,Goods,Customer,SalesOrderDetail

/*
GoodsName GoodsCode Price Quantity
---------- ---------- ----------- -----------
棉花 C0005 8 2
西瓜 C0006 2 2
花生 C0055 4 2
黄瓜 C0045 8 2
黄瓜 C0045 8 10
土豆 C0011 10 2
土豆 C0011 10 10

（所影响的行数为 7 行）
*/

[LOWGUN]

我用这个查询可以查询出结果，但是执行的效率极慢，可能要耗时几分钟，怎么改进，我用了like，因为同一上级客户的它的Upcustomercode前六位一样。
SELECT B.GoodsCode,B.GoodsName, C.GoodsPrice,Sum(C.Quantity) AS Amount from SalesWasteBook AS A, Goods As B,SalesOrderDetail AS C ,Customer As D Where A.SalesOrderCode=C.SalesOrderCode And B.GoodsCode=C.GoodsCode and A.CustomerCode like '104001* ' and ArrivedDateTime >=#2005-1-1# and ArrivedDateTime <#2008-1-1# Group by B.GoodsCode,B.GoodsName, C.GoodsPrice ,C.Quantity Order By GoodsName Asc

[dawugui]

慢的原因是我用了两次IN.
你可以自己把他改一下,使用内连接来搞.

[LOWGUN]

有请乌龟解答下，怎么改进呢？还有我在哪里用了两次in？？？

[dawugui]

把
A.CustomerCode like '104001*'
改为
left(A.CustomerCode,6) = '104001'

SELECT B.GoodsCode,B.GoodsName, C.GoodsPrice,Sum(C.Quantity) AS Amount from SalesWasteBook AS A, Goods As B,SalesOrderDetail AS C ,Customer As D Where A.SalesOrderCode=C.SalesOrderCode And B.GoodsCode=C.GoodsCode and left(A.CustomerCode,6) = '104001' and ArrivedDateTime >=#2005-1-1# and ArrivedDateTime <#2008-1-1# Group by B.GoodsCode,B.GoodsName, C.GoodsPrice ,C.Quantity Order By GoodsName Asc

[LOWGUN]

谢谢乌龟兄这么及时的回答，我改了之后依然很慢，感觉不出来变化啊?这是什么原因呢

[LOWGUN]

我看了下，goods表记录有1200多记录，customer表有1000左右记录，SalesWasteBook表有4万左右记录，而SalesOrderDetail 有10万记录，是不是数据量太大了？如果是有没有什么解决的办法？

[LOWGUN]

也不对啊，我其他查询的时候也是where条件一样，表也一样，不同的只是left(A.CustomerCode,6)= '104001' 换成了实际的A.CustomerCode='10400100000'具体的编号而已，

而我用单纯的：
SELECT * FROM Customer WHERE left(CustomerCode,6)= '104001' ORDER BY Customercode;测试又能立即返回结果。

可是和起来就慢的不能接受了。