select region,
(select sum(no),sum(square),sum(income_lost) from t t2 where t1.region=t2.region and date<3个月的),
(select sum(no),sum(square),sum(income_lost) from t t2 where t1.region=t2.region and date<6个月的),
...
from t t1
(select sum(no),sum(square),sum(income_lost) from t t2 where t1.region=t2.region and date<3个月的),
(select sum(no),sum(square),sum(income_lost) from t t2 where t1.region=t2.region and date<6个月的),
...
from t t1
下面是查了 ‘2014-10-12’ <3个月的 数量,面积和损失金额,因为是空铺,所以有个函数要找这个空铺上个合同的金额。
dbusrpub.getSpaceLastCont(a.spid, CAST(a.sdate as date)) 这个函数是找上个合同的。这个查询出来会报一个错:[Err] 42000 - [SQL Server]当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。
select t1.mfid,
(select a.muoid muoid,count(a.spid) kp1_spid,sum(a.SPLETTAREA) kp1_sparea,sum(m.ysamount) kp1_syamount
from
(select mu.muoid,sl.spid,sl.SPLETTAREA,sl.sdate,sl.EDATE
from dbusrpub.manaunit mu,dbusrpub.SHOPLIFECYCLE sl
where mu.muid = sl.muid
and sl.spstatus = 'E'
and mu.muoid = t1.mfid
and sl.sdate >= DATEADD(month, -3, '2014-10-12') and sl.sdate < DATEADD(month, 0, '2014-10-12')) a,dbusrpub.CONTMAIN m
where dbusrpub.getSpaceLastCont(a.spid, CAST(a.sdate as date)) = m.contno
group by a.muoid)
from dbusrpub.MANAORG t1
[Err] 42000 - [SQL Server]当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。
我现在改成这样,但是有个问题,就是我需要让他可以根据区域编码来查询,但是我加上 小区编码 and t.region = '0101' 之后,还是可以查询所有的小区。
select region,t2.no,t2.square,t2.income_lost from t t1
left joion
(select sum(no) no,sum(square) square,sum(income_lost) income_lost from t t2 where date<3个月的)
on t1.region=t2.region
left join
(select sum(no),sum(square),sum(income_lost) from t t3 where date<6个月的)
on t1.region=t3.region
and t.region = '0101'