第1个回答 2009-07-15
经过测试:
select a.客户名称,count(a.单据编号) as 交易笔数,sum(a.交易金额) as 交易金额,max(b.已关闭金额 ) as 已关闭金额 from sales a
left join (select 客户名称,sum(交易金额) as 已关闭金额 from sales where isnull(关闭标志,'')='Y' group by 客户名称) b
on a.客户名称= b.客户名称
group by a.客户名称
挣几十分不容易呀
第2个回答 2009-07-15
select tmp.*, (select sum(已关闭金额
) from sales where tmp.客户名称=slaes.客户名称 and 关闭标志
='y') as 已关闭金额
from (select 客户名称,count(单据编号) as 交易笔数 count(CASE when 关闭标志 ='y'then 1 else 0 end )) as 已关闭数 sum(交易金额) as 交易金额 from sales
group by 客户名称) tmp
我也没有测试。
第3个回答 2009-07-15
sql="select 客户名称,count(单据编号) as 交易笔数,sum(交易金额) as 交易金额,sum(已关闭金额) as 已关闭金额 from sales group by 客户名称"
第4个回答 2009-07-15
sql="select 客户名称,count(单据编号) as 交易笔数,sum(交易金额) as 交易金额,SUM(case when 关闭标志 = 'Y' then 交易金额 else 0 End) as 已关闭金额 from sales group by 客户名称"
已经关闭的:
SUM(case when 关闭标志 = 'Y' then 交易金额 else 0 End) as 已关闭金额
第5个回答 2009-07-22
假设一个表jy
code name je isclose
001 kaka 1700 N
002 kaka 1000 Y
003 messy 2500 Y
004 ronaldo 1800 N
005 messy 1500 N
SQL语句;
select
a.name, isnull(a.sl,0) as '交易数量',isnull(b.sl,0) as '已关闭数量',isnull(a.zje,0) as '交易金额',isnull(b.gje,0) as '已关闭金额'
from
(select
name,count(je)as 'sl' ,sum(je) as 'zje'
from jy
group by name)a
left join
(select
name,count(je) as 'sl',sum(je) as 'gje'
from jy
where isclose='Y'
group by name)b
on a.name=b.name
结果
客户名 交易数量 关闭数量 交易金额 关闭
kaka 2 1 2700 1000
messy 2 1 4000 2500
ronaldo 1 0 1800 0