select
a.Customer as 客户,count(distinct lngSaleNo) as 票数,sum(c.DblAmount) as 金额
from
Sale a,SaleDetail b
where
a.LngSaleNo=b.LngSaleNo
group by
a.Customer
a.Customer as 客户,count(distinct lngSaleNo) as 票数,sum(c.DblAmount) as 金额
from
Sale a,SaleDetail b
where
a.LngSaleNo=b.LngSaleNo
group by
a.Customer
insert into @Sale select 1,'A'
insert into @Sale select 2,'A'
insert into @Sale select 3,'B'
insert into @Sale select 4,'B'
insert into @Sale select 5,'C'
declare @SaleDetail table(LngSaleNo int,DblAmount int)
insert into @SaleDetail select 1,3
insert into @SaleDetail select 1,5
insert into @SaleDetail select 2,8
insert into @SaleDetail select 2,7
insert into @SaleDetail select 2,6
insert into @SaleDetail select 2,5
insert into @SaleDetail select 3,7
insert into @SaleDetail select 3,4
insert into @SaleDetail select 3,2
insert into @SaleDetail select 3,6
insert into @SaleDetail select 4,5
insert into @SaleDetail select 5,7select
a.Customer as 客户,count(distinct a.lngSaleNo) as 票数,sum(b.DblAmount) as 金额
from
@Sale a,@SaleDetail b
where
a.LngSaleNo=b.LngSaleNo
group by
a.Customer/*
客户 票数 金额
---------- ----------- -----------
A 2 34
B 2 24
C 1 7
*/
a.Customer as 客户,count(distinct a.lngSaleNo) as 票数,sum(b.DblAmount) as 金额
from
Sale a,SaleDetail b
where
a.LngSaleNo=b.LngSaleNo
group by
a.Customer
"lngSaleNo
(销售单号)" "Customer
(客户)"
1 A
2 B
3 C
4 A
5 B
销售单明细SaleDetail
"LngSaleNo
(销售单号)" 应收/应付(StrYSYF) 币种(StrBZ) 金额(DblAmount)
1 应收 RMB 3
1 应收 HKD 5
2 应付 RMB 8
2 应收 RMB 7
2 应收 HKD 6
2 应收 USD 5
3 应付 USD 7
3 应收 RMB 4
3 应付 HKD 2
3 应收 USD 6
4 应收 USD 6
5 应付 RMB 3
问题:如果通过Sale,SaleDetail两个表,用SQL语句实现下面的查询结果
"LngSaleNo
(销售单号)" "Customer
(客户)" 票数 应收RMB 应收HKD 应收USD 应付RMB 应付HKD 应付USD
1 A 2 3 5 6
2 B 2 7 6 5 11
3 C 1 4 6 2 7
怎么实现,注意票数列
a.Customer as 客户,
count(distinct a.lngSaleNo) as 票数,
sum(case when StrYSYF='应收' and StrBZ='RMB' then b.DblAmount end) as 应收RMB,
sum(case when StrYSYF='应收' and StrBZ='HKD' then b.DblAmount end) as 应收HKD,
sum(case when StrYSYF='应收' and StrBZ='USD' then b.DblAmount end) as 应收USD,
sum(case when StrYSYF='应付' and StrBZ='RMB' then b.DblAmount end) as 应付RMB,
sum(case when StrYSYF='应付' and StrBZ='HKD' then b.DblAmount end) as 应付HKD,
sum(case when StrYSYF='应付' and StrBZ='USD' then b.DblAmount end) as 应付USD
from
Sale a,SaleDetail b
where
a.LngSaleNo=b.LngSaleNo
group by
a.Customer