有一个表,名为X1,字段为纳税人识别号NSRSBH、所属时期SSSQ、应纳税额YNSE、上期留抵税额SQLDSE.每户纳税人每月一条记录。我要获得一个记录集,内容是某年内每户纳税人应纳税额总和和上期留抵税额,而上期留抵税额就是该纳税人本年最早一个月的上期留抵税额。这样明显不行:Select NSRSBH,SUM(YNSE) T_YNSE,MIN(SQLDSE) M_SQLDSE from X1 WHERE SSSQ between '2004-1-1' and '2004-12-31' group by NSRSBH 那么该怎样写呢?
select * from (
Select NSRSBH,SUM(YNSE) T_YNSE from X1 WHERE SSSQ between '2004-1-1' and '2004-12-31' group by NSRSBH ) t1 inner join
(
select * from (
Select NSRSBH,Min(SSSQ) T_Date from X1 WHERE SSSQ between '2004-1-1' and '2004-12-31' group by NSRSBH ) t0 inner join x1 on t0.NSRSBH=x1.NSRSBH and t0.T_Date=x1.SSSQ
) t2 on t1.NSRSBH=t2.NSRSBH 还没考虑性能,要把它优化一下
(select NSRSBH,SUM(YNSE) T_YNSE from X1 group by NSRSBH) C0,
(select X1.NSRSBH,SQLDSE from X1,(select NSRSBH,MIN(SSSQ) ZRQ from X1 group by NSRSBH) t1 where X1.NSRSBH=t1.NSRSBH and SSSQ=ZRQ) C1,
(select X1.NSRSBH,QMLDSE from X1,(select NSRSBH,MAX(SSSQ) ZRQ from X1 group by NSRSBH) t2 where X1.NSRSBH=t2.NSRSBH and SSSQ=ZRQ) C2
where C0.NSRSBH=C1.NSRSBH and C0.NSRSBH=C2.NSRSBH