大家好,MS SQL2000视图查询提示超过了表的最大数目(260)。
恳请协助,谢谢!
使用Union all 创建基础视图很简单,如下所示:
ALTER view BQ_业务余额表 as
Select
cDwCode as 供应商代码,
cDwName as 供应商名称,
1 as '月份',
sum(case when iPeriod<1 then iCAmount-iDAmount else 0 end) as 期初本币, --期初本币
sum(iCAmount-iDAmount) as 期末本币 --期末本币
FROM UFDATA_111_2010.dbo.Ap_DetailVend
WHERE iFlag<3
AND iPeriod<=3 AND cExch_Name='人民币'
AND cFlag<>'AR'
GROUP BY cDwCode ,cDwName,iPeriod
union all
Select
cDwCode as 供应商代码,
cDwName as 供应商名称,
2 as '月份',
sum(case when iPeriod<2 then iCAmount-iDAmount else 0 end) as 期初本币, --期初本币
sum(iCAmount-iDAmount) as 期末本币 --期末本币
FROM UFDATA_111_2010.dbo.Ap_DetailVend
WHERE iFlag<3
AND iPeriod<=3 AND cExch_Name='人民币'
AND cFlag<>'AR'
GROUP BY cDwCode ,cDwName,iPeriod
union all
Select
cDwCode as 供应商代码,
cDwName as 供应商名称,
3 as '月份',
sum(case when iPeriod<3 then iCAmount-iDAmount else 0 end) as 期初本币, --期初本币
sum(iCAmount-iDAmount) as 期末本币 --期末本币
FROM UFDATA_111_2010.dbo.Ap_DetailVend
WHERE iFlag<3
AND iPeriod<=3 AND cExch_Name='人民币'
AND cFlag<>'AR'
GROUP BY cDwCode ,cDwName,iPeriod
union all
Select
cDwCode as 供应商代码,
cDwName as 供应商名称,
4 as '月份',
sum(case when iPeriod<4 then iCAmount-iDAmount else 0 end) as 期初本币, --期初本币
sum(iCAmount-iDAmount) as 期末本币 --期末本币
FROM UFDATA_111_2010.dbo.Ap_DetailVend
WHERE iFlag<3
AND iPeriod<=4 AND cExch_Name='人民币'
AND cFlag<>'AR'
GROUP BY cDwCode ,cDwName,iPeriod
union all
Select
cDwCode as 供应商代码,
cDwName as 供应商名称,
5 as '月份',
sum(case when iPeriod<5 then iCAmount-iDAmount else 0 end) as 期初本币, --期初本币
sum(iCAmount-iDAmount) as 期末本币 --期末本币
FROM UFDATA_111_2010.dbo.Ap_DetailVend
WHERE iFlag<3
AND iPeriod<=5 AND cExch_Name='人民币'
AND cFlag<>'AR'
GROUP BY cDwCode ,cDwName,iPeriod
union all
Select
cDwCode as 供应商代码,
cDwName as 供应商名称,
6 as '月份',
sum(case when iPeriod<6 then iCAmount-iDAmount else 0 end) as 期初本币, --期初本币
sum(iCAmount-iDAmount) as 期末本币 --期末本币
FROM UFDATA_111_2010.dbo.Ap_DetailVend
WHERE iFlag<3
AND iPeriod<=6 AND cExch_Name='人民币'
AND cFlag<>'AR'
GROUP BY cDwCode ,cDwName,iPeriod
union all
Select
cDwCode as 供应商代码,
cDwName as 供应商名称,
7 as '月份',
sum(case when iPeriod<7 then iCAmount-iDAmount else 0 end) as 期初本币, --期初本币
sum(iCAmount-iDAmount) as 期末本币 --期末本币
FROM UFDATA_111_2010.dbo.Ap_DetailVend
WHERE iFlag<3
AND iPeriod<=7 AND cExch_Name='人民币'
AND cFlag<>'AR'
GROUP BY cDwCode ,cDwName,iPeriod
union all
Select
cDwCode as 供应商代码,
cDwName as 供应商名称,
8 as '8月',
sum(case when iPeriod<8 then iCAmount-iDAmount else 0 end) as 期初本币, --期初本币
sum(iCAmount-iDAmount) as 期末本币 --期末本币
FROM UFDATA_111_2010.dbo.Ap_DetailVend
WHERE iFlag<3
AND iPeriod<=8 AND cExch_Name='人民币'
AND cFlag<>'AR'
GROUP BY cDwCode ,cDwName,iPeriod
union all
Select
cDwCode as 供应商代码,
cDwName as 供应商名称,
9 as '9月',
sum(case when iPeriod<9 then iCAmount-iDAmount else 0 end) as 期初本币, --期初本币
sum(iCAmount-iDAmount) as 期末本币 --期末本币
FROM UFDATA_111_2010.dbo.Ap_DetailVend
WHERE iFlag<3
AND iPeriod<=9 AND cExch_Name='人民币'
AND cFlag<>'AR'
GROUP BY cDwCode ,cDwName,iPeriod
union all
Select
cDwCode as 供应商代码,
cDwName as 供应商名称,
10 as '10月',
sum(case when iPeriod<10 then iCAmount-iDAmount else 0 end) as 期初本币, --期初本币
sum(iCAmount-iDAmount) as 期末本币 --期末本币
FROM UFDATA_111_2010.dbo.Ap_DetailVend
WHERE iFlag<3
AND iPeriod<=10 AND cExch_Name='人民币'
AND cFlag<>'AR'
GROUP BY cDwCode ,cDwName,iPeriod
union all
Select
cDwCode as 供应商代码,
cDwName as 供应商名称,
11 as '11月',
sum(case when iPeriod<11 then iCAmount-iDAmount else 0 end) as 期初本币, --期初本币
sum(iCAmount-iDAmount) as 期末本币 --期末本币
FROM UFDATA_111_2010.dbo.Ap_DetailVend
WHERE iFlag<3
AND iPeriod<=11 AND cExch_Name='人民币'
AND cFlag<>'AR'
GROUP BY cDwCode ,cDwName,iPeriod
union all
Select
cDwCode as 供应商代码,
cDwName as 供应商名称,
12 as '12月',
sum(case when iPeriod<12 then iCAmount-iDAmount else 0 end) as 期初本币, --期初本币
sum(iCAmount-iDAmount) as 期末本币 --期末本币
FROM UFDATA_111_2010.dbo.Ap_DetailVend
WHERE iFlag<3
AND iPeriod<=12 AND cExch_Name='人民币'
AND cFlag<>'AR'
GROUP BY cDwCode ,cDwName,iPeriod