SQL语句汇总的写法 (100分)

潮

潮州人

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-28

• #1

环境：
DELPHI6+ADO+ACCESS2000
Quote(报价表)
QuoteID CustomerID
001 001
002 008
003 002
QuoteDetails(报价明细表)
QuoteID ProductID Quantity UnitPrice
001 002 1 ￥2.00
001 003 2 ￥3.00
002 002 4 ￥2.00

汇总后的结果
QuoteID CountProduct(产品的只数) Sum总价
001 2 ￥2.00
002 1 ￥6.00
003 0 ￥8.00

 

N

navychen

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-28

• #2

select QuoteId, CountProduct=(select count(*) from QuoteDetails where QuoteDetails.QuoteID=Quote.QuoteID) from Quote

 

潮

潮州人

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-28

• #3

这样写不运行不了.

 

F

fancy105

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-28

• #4

你的"產品的只數"是什么意思﹖
你上面的數據（2﹐1﹐0）要從哪里算來﹖

 

F

free_knight

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-28

• #5

select a.QuoteId,b.count(*) CountProduct
from Quote a,QuoteDetails b
where Quote.QuoteID=QuoteDetails.QuoteID(+)
或
select a.QuoteId,(select count(*) from QuoteDetails b where a.QuoteID=b.QuoteID) CountProduct
from Quote a

 

潮

潮州人

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-28

• #6

to fancy105
產品的只數就是汇总来的,其实也是订单的明细记录

 

S

snjat

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-28

• #7

你的数据结构好象不够好，再说你要求的统计，如果是0的话就不需要了，直接统计
表2的数据就行了，
select count(QuoteID) from table2 group by QuoteID

 

潮

潮州人

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-28

• #8

to snjat
那你说应该怎么设计才比较好呢?还有就是我汇总才来的每一张订单都要显示出来的.

 

潮

潮州人

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-28

• #9

修改了一下,请各位再帮我看一下.

 

潮

潮州人

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-28

• #10

'''

 

Y

yyanghhong

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-29

• #11

select count(QuoteID), sum(UnitPrice) from table2 group by QuoteID

 

快

快乐男孩

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-29

• #12

SELECT a.*, c.Sum, c.CountProduct
FROM Quote AS a LEFT JOIN [SELECT QuoteID, count(QuoteID) AS CountProduct, Sum(CCur([UnitPrice]*[Quantity]*[PointRate])) AS Sum
FROM QuoteDetails
GROUP BY QuoteDetails.QuoteID]. AS c ON a.QuoteID=c.QuoteID
ORDER BY a.QuoteID;

 

潮

潮州人

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-12-29

• #13

接受答案了.