你用了group by ,但不見你用sum()之類的函數﹖因為你只選擇出其中的字段﹐所以存在重復記錄的可能性很大。不選擇出重復記錄﹐用distinctselect distinct sellreport_tt.dealdate as xiaoshouriqi,isnull(sell_inv,0) as xiaoshoufapiao,isnull(mobileM,0) as shoujixiaoshoue
from sellreport_tt left outer join sellreport_dt_all on (sellreport_tt.dealdate=sellreport_dt_all.dealdate)
where sellreport_tt.dealdate=sellreport_dt_all.dealdate and sellreport_tt.storeid='fy01' and sellreport_tt.dealdate='2004-07-01'
from sellreport_tt left outer join sellreport_dt_all on (sellreport_tt.dealdate=sellreport_dt_all.dealdate)
where sellreport_tt.dealdate=sellreport_dt_all.dealdate and sellreport_tt.storeid='fy01' and sellreport_tt.dealdate='2004-07-01'
isnull(sellreport_tt.sell_inv,0) as xiaoshoufapiao,
isnull(sellreport_dt_all.mobileM,0) as shoujixiaoshoue
from sellreport_tt left outer join sellreport_dt_all
on (sellreport_tt.dealdate=sellreport_dt_all.dealdate)
where sellreport_tt.storeid='fy01' and sellreport_tt.dealdate='2004-07-01'
group by sellreport_tt.dealdate,isnull(sellreport_tt.sell_inv,0),isnull(sellreport_dt_all.mobileM,0)
sellreport_tt.dealdate as xiaoshouriqi,
isnull(sell_inv,0) as xiaoshoufapiao,
isnull(mobileM,0) as shoujixiaoshoue
from sellreport_tt
left outer join sellreport_dt_all
on (sellreport_tt.dealdate=sellreport_dt_all.dealdate)
where sellreport_tt.dealdate=sellreport_dt_all.dealdate
and sellreport_tt.storeid='fy01'
and sellreport_tt.dealdate='2004-07-01'
from( --先对 sellreport_dt_all 分组求和
select 日期,值=sum(值) --按日期分组求值
from sellreport_dt_all
group by 日期
)a,( --再对 sellreport_tt 分组求和
select 日期,值=sum(值) --按日期分组求值
from sellreport_tt
group by 日期
)b where a.日期=b.日期 --再合并求和结果
select distinct
sellreport_tt.dealdate as xiaoshouriqi,
isnull(sell_inv,0) as xiaoshoufapiao,
isnull(mobileM,0) as shoujixiaoshoue
from (select sellreport_tt.dealdate as xiaoshouriqi, sum(isnull(sell_inv,0)) as xiaoshoufapiao from sellreport_tt
where dealdate='2004-07-01' and storeid='fy01'
group by dealdate,storeid)
,(select sellreport_tt.dealdate as xiaoshouriqi, sum(isnull(mobileM,0)) as shoujixiaoshoue from sellreport_dt_all
where dealdate='2004-07-01' and storeid='fy01'
group by dealdate,storeid)
where sellreport_tt.dealdate=sellreport_dt_all.dealdate
为什么出错呢?多谢指点
a.dealdate as xiaoshouriqi,
isnull(sell_inv,0) as xiaoshoufapiao,
isnull(mobileM,0) as shoujixiaoshoue
from (
select dealdate as xiaoshouriqi,
sum(isnull(sell_inv,0)) as xiaoshoufapiao
from sellreport_tt
where dealdate='2004-07-01' and storeid='fy01'
group by dealdate,storeid
)a --少了别名
,(
-- select sellreport_tt.dealdate as xiaoshouriqi,--写错了字段名的引用
select dealdate as xiaoshouriqi,
sum(isnull(mobileM,0)) as shoujixiaoshoue
from sellreport_dt_all
where dealdate='2004-07-01' and storeid='fy01'
group by dealdate,storeid
) b --少写了别名
where a.dealdate=b.dealdate
a.dealdate as xiaoshouriqi,
isnull(sell_inv,0) as xiaoshoufapiao,
isnull(mobileM,0) as shoujixiaoshoue
from (
select dealdate as xiaoshouriqi,
sum(isnull(sell_inv,0)) as xiaoshoufapiao
from sellreport_tt
where dealdate='2004-07-01' and storeid='fy01'
group by dealdate--,storeid--只按日期关联,应该不要再按storeid分组,否则可能导致重复
)a --少了别名
,(
-- select sellreport_tt.dealdate as xiaoshouriqi,--写错了字段名的引用
select dealdate as xiaoshouriqi,
sum(isnull(mobileM,0)) as shoujixiaoshoue
from sellreport_dt_all
where dealdate='2004-07-01' and storeid='fy01'
group by dealdate--,storeid--只按日期关联,应该不要再按storeid分组,否则可能导致重复
) b --少写了别名
where a.dealdate=b.dealdate
a.dealdate as xiaoshouriqi,
isnull(sell_inv,0) as xiaoshoufapiao,
isnull(mobileM,0) as shoujixiaoshoue
from (
select dealdate as xiaoshouriqi,
sum(isnull(sell_inv,0)) as xiaoshoufapiao
from sellreport_tt
where dealdate='2004-07-01' and storeid='fy01'
group by dealdate,storeid
)a --少了别名
,(
-- select sellreport_tt.dealdate as xiaoshouriqi,--写错了字段名的引用
select dealdate as xiaoshouriqi,
sum(isnull(mobileM,0)) as shoujixiaoshoue
from sellreport_dt_all
where dealdate='2004-07-01' and storeid='fy01'
group by dealdate,storeid
) b --少写了别名
where a.dealdate=b.dealdate and a.storeid=b.storeid --如果按 storeid 分组是必须的