大力!QXJ让我找你!
上海老李 2003-11-03 11:26:58 我的存储过程是下面的
ALTER PROCEDURE monthlist @curyear char(4),@quarter char(2),@unitno char(2),@endunitno char(2)
AS
declare @month1 varchar(2)
declare @month2 varchar(2)
declare @month3 varchar(2)
if (@quarter='1')
begin
select @month1='1'
select @month2='2'
select @month3='3'
end
if (@quarter='2')
begin
select @month1='4'
select @month2='5'
select @month3='6'
end
if (@quarter='3')
begin
select @month1='7'
select @month2='8'
select @month3='9'
end
if (@quarter='4')
begin
select @month1='10'
select @month2='11'
select @month3='12'
end
select distinct len(roomno),convert(int,unitno,0),roomno,ownername,f1*f2*f3 as f4,* from
(select @quarter as qua,@curyear as curyear,@month1 as month1,@month2 as month2,@month3 as month3,aa.roomno as roomno,aa.unitno as unitno,aa.ownername,convert(int,d.finish1,0) as f1,convert(int,d2.finish2,0) as f2,convert(int,d3.finish3,0) as f3
from roominfo aa
left JOIN
(SELECT roomno,isfinish as finish1
FROM Fee
WHERE (FeeName = '物业管理费')
AND (FeeYear=@curyear)and (feemonth=@month1)
AND unitno=@unitno) d
on aa.roomno=d.roomno
left JOIN
(SELECT roomno, isfinish as finish2
FROM Fee
WHERE (FeeName = '物业管理费')
AND (FeeYear=@curyear)and (feemonth=@month2)
AND unitno=@unitno) d2
on aa.roomno=d2.roomno
left JOIN
(SELECT roomno, isfinish as finish3
FROM Fee
WHERE (FeeName = '物业管理费')
AND (FeeYear=@curyear)and (feemonth=@month3)
AND unitno=@unitno) d3
on aa.roomno=d3.roomno
where (convert(int,unitno,0)>=convert(int,@unitno,0)) and (convert(int,unitno,0)<=convert(int,@endunitno,0))
) bb
order by len(roomno),convert(int,unitno,0),roomno
(2003-11-03 10:30:27) 耗子
所得到的数据是要从1到4号楼得到数据,可是只有1号楼的正确,其它得到的数据的F1,F2,F3等是NULL值
[↑]
(2003-11-03 10:31:06) 耗子
当改变1为2,2的是正确的,3,4不正确
[↑]
(2003-11-03 10:31:20) 耗子
当改成3,4就不正确
[↑]
我不知为什么
2 2-702 师成健 1 1 2003 1 2 3 2-702 2 师成健 1 1 1
2 2-801 戴荣良 1 1 2003 1 2 3 2-801 2 戴荣良 1 1 1
2 2-802 董鸣鹤 1 1 2003 1 2 3 2-802 2 董鸣鹤 1 1 1
2 2-901 沈培英 1 1 2003 1 2 3 2-901 2 沈培英 1 1 1
2 2-902 吴菊生 1 1 2003 1 2 3 2-902 2 吴菊生 1 1 1
3 3-101 褚金娣 NULL 1 2003 1 2 3 3-101 3 褚金娣 NULL NULL NULL
3 3-102 赵林生 NULL 1 2003 1 2 3 3-102 3 赵林生 NULL NULL NULL
3 3-103 陈培生 NULL 1 2003 1 2 3 3-103 3 陈培生 NULL NULL NULL
3 3-201 张平 NULL 1 2003 1 2 3 3-201 3 张平 NULL NULL NULL
3 3-202 赵芝萍 NULL 1 2003 1 2 3 3-202 3 赵芝萍 NULL NULL NULL
3 3-203 唐晓澐 NULL 1 2003 1 2 3 3-203 3 唐晓澐 NULL NULL NULL
3 3-301 陈培良 NULL 1 2003 1 2 3 3-301 3 陈培良 NULL NULL NULL
换了有多个单元号,所有后边的就得到的f1,f2,f3都是空值