brightheroes(闭关|那一剑的风情)高手的代码测试通过了,估计下面几位高手的也不会差,我等有空再试(不过都会给分的).
这里想问一句如果(按brightheroes高手的方法),客户表中实际上存在多个字段,我为了问问题方便就只列出两个,但我发现这些字段是与SQL语句相关的,就是group by a.id,a.name这句,如果我想在结果中将客户表中的字段都列出来是不是就得将它们都列在group by 中啊,这样做会不会影响执行效率呢?
select a.id as id,sum (金额) as '金额' from ( select a.id as id,c.金额 as '金额' from 客户表 as a,销售表 as b,销售明细表 as c
where a.id=b.客户id and b.销售单号=c.对应销售单号) as t group by id
select a.客户ID,d.姓名,a.消费额 (select 客户ID,sum(sum(金额)) 消费额 from ( select 销售单号,sum(金额),客户ID from (select 销售单号,sum(金额) from table3 group by 销售单号) a,table2 b where a.销售单号=b.销售单号 ) group by 客户ID) c,table1 d where c.客户ID=d.客户ID
先选出 每个客户 消费额
select sum(b.金额) as 总金额,a.客户ID from 销售明细表 b inner join 销售表 a on a.销售单号 = b.对应销售单号 group by a.客户ID
然后再和 客户表 一起查讯
select a.*,b.总金额 from 客户表 a inner join
(select sum(b.金额) as 总金额,a.客户ID from 销售明细表 b inner join 销售表 a on a.销售单号 = b.对应销售单号 group by a.客户ID )
b
on a.ID =b.客户ID
select a.ID as [客户ID], a.[姓名], isnull(sum(isnull(c.[金额],0)), 0) as [金额]
from [客户表] a
left join [销售表] b on b.[客户ID] = a.ID
left join [销售明细表] c on c.对应销售单号 = b.销售单号
group by a.ID, a.[姓名]
试试
select a.id,a.name, sum(isnull(c.金额,0))
from 客户表 as a
left join 销售表 as b on b.客户ID = a.id
left join 销售明细表 as c on c.对应销售单号 = b.id
group by a.id,a.name