有如下表结构：
customername productCode productName saleAmount saleDate
欣欣超市 1 软中华 100 2007-1-1
华联商贸 2 硬中华 50 2007-1-3
欣欣超市 1 软中华 200 2007-1-6
文峰超市 1 软中华 60 2007-1-7
欣欣超市 3 苏烟 50 2007-1-8
农工商超市 2 硬中华 300 2007-1-9
统计要求：可以按照查询要求统计 在某段时间内，某一种或某几种商品达到一定销量的客户名单信息。比如：
在2007-1-1到2007-1-10间 软中华销量达到100-400区间的客户是:欣欣超市
在2007-1-1到2007-1-10间 软中华销量达到60-100区间的客户是:文峰超市
基本的SQL语句我写成如下：
select sum(saleamount) as allamount,customername,productname from View_ProductSellInfoDetail where allamount>=100 and allamount<=400 group by customername,productname
但执行时出错。说是：“列名'allamount'无效”
请教高手该如何操作
------解决方案--------------------
select sum(saleamount) as allamount,customername,productname from View_ProductSellInfoDetail where allamount >=100 and allamount <=400 group by customername,productname
--------------
select sum(saleamount) as allamount,customername,productname
from View_ProductSellInfoDetail
group by customername,productname
having sum(saleamount) between 100 and 400
------解决方案--------------------
--还要加上日期
select sum(saleamount) as allamount,customername,productname
from View_ProductSellInfoDetail
where saleDate between '2007-01-10' and '2007-01-10'
group by customername,productname
having sum(saleamount) between 100 and 400
------解决方案--------------------
use this:
- SQL code
select * from(select sum(saleAmount) as allamount, customerName, productName from View_ProductSellInfoDetailgroup by customername, productname)twhere allamount >= 100 and allamount <= 400
------解决方案--------------------
sorry, i am wrong. using having is the right way.
- SQL code
select sum(saleAmount) as allamount, customerName, productNamefrom View_ProductSellInfoDetailgroup by customername, productnamehaving sum(saleAmount) between 100 and 400
------解决方案--------------------
- SQL code
declare @t table(customername nvarchar(5), productCode int, productName nvarchar(5), saleAmount int,saleDate datetime)insert @t select '欣欣超市',1, '软中华',100, '2007-1-1' insert @t select '华联商贸',2, '硬中华',50 , '2007-1-3' insert @t select '欣欣超市',1, '软中华',200, '2007-1-6' insert @t select '文峰超市',1, '软中华',60 , '2007-1-7' insert @t select '欣欣超市',3, '苏烟',50 , '2007-1-8' insert @t select '农工商超市',2, '硬中华', 300, '2007-1-9' select customername, productName, sum(saleAmount)as '销量' from @t where saleDate between '2007-1-1' and '2007-1-10'group by customername,productName having (sum(saleAmount) between 100 and 400 )