--生成分户综合信息if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P2_FenHu]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P2_FenHu]
GOcreate procedure P2_FenHu
@TiaoJian0 varchar(2000),
@TiaoJian1 varchar(2000),
@TiaoJian2 varchar(2000),
@PaiXuYiJu varchar(2000)
as--创建返回表
create table #FenHu2
(
FenHu_id integer,
FenHu_BianMa char(12),
FenHu_MingCheng varchar(100),
FenHu_JianZhuMianJi decimal(8, 2),
FenHu_GongReMianJi decimal(8, 2),
FenHu_GaoDu decimal(4, 2),
FenHu_FangZhuXingMing char(20),
FenHu_FangZhuLianXiFangShi varchar(100),
FenHu_FangZhuDanWei varchar(100),
FenHu_DanYuan char(20),
FenHu_MenPaiHao char(20),
FenHu_YiJingGongRe char(1),
FenHu_GongReShiJian char(10),
FenHu_TouReMianJi decimal(8, 2) default 0.0,
FenHu_BiaoJi1 char(1),
FenHu_BiaoJi2 char(1),
FenHu_QiTaShuJu varchar(100),
FenHu_BeiZhu varchar(200),
FenHu_BiaoZhi char(1),
FenHu_GongReQi char(4), JianZhu_BianMa char(12),
JianZhu_MingCheng varchar(100), JiFeiLeiXing_BianMa char(2),
JiFeiLeiXing_MingCheng varchar(40),
JiFeiLeiXing_DanJia decimal(4, 2), ShouFeiYuan_BianMa char(4),
ShouFeiYuan_XingMing char(20), CaoZuoYuan_BianMa char(4),
CaoZuoYuan_XingMing char(40), XiaoQu_BianMa char(8),
XiaoQu_MingCheng char(40), BengZhan_BianMa char(8),
BengZhan_MingCheng char(40), JiZhang_YingShou decimal(8, 2) default 0.0,
JiZhang_ShiShou decimal(8, 2) default 0.0,
JiZhang_YuE decimal(8, 2) default 0.0,
QianZhang_YingShou decimal(8, 2) default 0.0,
QianZhang_ShiShou decimal(8, 2) default 0.0,
QianZhang_YuE decimal(8, 2) default 0.0
)--给返回表插入符合条件的分户记录
exec('insert into #FenHu2 (
FenHu_id,
FenHu_BianMa,
FenHu_MingCheng,
FenHu_JianZhuMianJi,
FenHu_GongReMianJi,
FenHu_GaoDu,
FenHu_FangZhuXingMing,
FenHu_FangZhuLianXiFangShi,
FenHu_FangZhuDanWei,
FenHu_YiJingGongRe,
FenHu_GongReShiJian,
FenHu_DanYuan,
FenHu_MenPaiHao,
FenHu_BiaoJi1,
FenHu_BiaoJi2,
FenHu_QiTaShuJu,
FenHu_BeiZhu,
FenHu_BiaoZhi,
FenHu_GongReQi, JianZhu_BianMa,
JianZhu_MingCheng, JiFeiLeiXing_BianMa,
JiFeiLeiXing_MingCheng,
JiFeiLeiXing_DanJia, ShouFeiYuan_BianMa,
ShouFeiYuan_XingMing, CaoZuoYuan_BianMa,
CaoZuoYuan_XingMing,
XiaoQu_BianMa,
XiaoQu_MingCheng, BengZhan_BianMa,
BengZhan_MingCheng )
select * from V_FenHu where ' + @TiaoJian1)
declare @FHBM char(12)
declare @GRMJ decimal(8, 2)
declare @YJGR char(1)
declare @GRQ char(4)
declare @TRMJ decimal(8, 2)
declare @JZ_YS decimal(8, 2)
declare @JZ_SS decimal(8, 2)
declare @JZ_YE decimal(8, 2)
declare @QZ_YS decimal(8, 2)
declare @QZ_SS decimal(8, 2)
declare @QZ_YE decimal(8, 2)
--定义游标,用于处理每个分户的投热面积和记账信息
declare FenHu_Cur cursor
static
for
select FenHu_BianMa, FenHu_GongReMianJi, FenHu_YiJingGongRe, FenHu_GongReQi
from #FenHu2
set @FHBM = ''
set @GRMJ = 0.0
set @YJGR = ''
set @GRQ = ''
set @TRMJ = 0.0set @JZ_YS = 0.0
set @JZ_SS = 0.0
set @JZ_YE = 0.0
set @QZ_YS = 0.0
set @QZ_SS = 0.0
set @QZ_YE = 0.0
open FenHu_Cur
fetch next from FenHu_Cur into @FHBM, @GRMJ, @YJGR, @GRQwhile @@fetch_status = 0
begin
--投热面积
if @YJGR = 'Y'
set @TRMJ = @GRMJ
else
set @TRMJ = 0.0 --记账
select @JZ_YS = sum(JieFang), @JZ_SS = sum(DaiFang) from JiZhang where BianMa = @FHBM and GongReQi = @GRQ
if @JZ_YS is null
set @JZ_YS = 0.0
if @JZ_SS is null
set @JZ_SS = 0.0
set @JZ_YE = @JZ_YS - @JZ_SS
--欠账 select @QZ_YS = sum(QiChuQianZhang), @QZ_SS = sum(BenQiGuiHuan) from QianZhang where BianMa = @FHBM and GongReQi = @GRQ
if @QZ_YS is null
set @QZ_YS = 0.0
if @QZ_SS is null
set @QZ_SS = 0.0
set @QZ_YE = @QZ_YS - @QZ_SS
--跟新数据
update #FenHu2 set
FenHu_TouReMianJi = @TRMJ,
JiZhang_YingShou = @JZ_YS,
JiZhang_ShiShou = @JZ_SS,
JiZhang_YuE = @JZ_YE,
QianZhang_YingShou = @QZ_YS,
QianZhang_ShiShou = @QZ_SS,
QianZhang_YuE = @QZ_YE
where FenHu_BianMa = @FHBM fetch next from FenHu_Cur into @FHBM, @GRMJ, @YJGR, @GRQ
endclose FenHu_Cur
deallocate FenHu_Cur
if len(ltrim(rtrim(@PaiXuYiJu))) > 0
exec(' select ' + @TiaoJian0 + ' from #FenHu2 where ' + @TiaoJian2 + ' order by ' + @PaiXuYiJu)
else
exec(' select ' + @TiaoJian0 + ' from #FenHu2 where ' + @TiaoJian2)return
drop procedure [dbo].[P2_FenHu]
GOcreate procedure P2_FenHu
@TiaoJian0 varchar(2000),
@TiaoJian1 varchar(2000),
@TiaoJian2 varchar(2000),
@PaiXuYiJu varchar(2000)
as--创建返回表
create table #FenHu2
(
FenHu_id integer,
FenHu_BianMa char(12),
FenHu_MingCheng varchar(100),
FenHu_JianZhuMianJi decimal(8, 2),
FenHu_GongReMianJi decimal(8, 2),
FenHu_GaoDu decimal(4, 2),
FenHu_FangZhuXingMing char(20),
FenHu_FangZhuLianXiFangShi varchar(100),
FenHu_FangZhuDanWei varchar(100),
FenHu_DanYuan char(20),
FenHu_MenPaiHao char(20),
FenHu_YiJingGongRe char(1),
FenHu_GongReShiJian char(10),
FenHu_TouReMianJi decimal(8, 2) default 0.0,
FenHu_BiaoJi1 char(1),
FenHu_BiaoJi2 char(1),
FenHu_QiTaShuJu varchar(100),
FenHu_BeiZhu varchar(200),
FenHu_BiaoZhi char(1),
FenHu_GongReQi char(4), JianZhu_BianMa char(12),
JianZhu_MingCheng varchar(100), JiFeiLeiXing_BianMa char(2),
JiFeiLeiXing_MingCheng varchar(40),
JiFeiLeiXing_DanJia decimal(4, 2), ShouFeiYuan_BianMa char(4),
ShouFeiYuan_XingMing char(20), CaoZuoYuan_BianMa char(4),
CaoZuoYuan_XingMing char(40), XiaoQu_BianMa char(8),
XiaoQu_MingCheng char(40), BengZhan_BianMa char(8),
BengZhan_MingCheng char(40), JiZhang_YingShou decimal(8, 2) default 0.0,
JiZhang_ShiShou decimal(8, 2) default 0.0,
JiZhang_YuE decimal(8, 2) default 0.0,
QianZhang_YingShou decimal(8, 2) default 0.0,
QianZhang_ShiShou decimal(8, 2) default 0.0,
QianZhang_YuE decimal(8, 2) default 0.0
)--给返回表插入符合条件的分户记录
exec('insert into #FenHu2 (
FenHu_id,
FenHu_BianMa,
FenHu_MingCheng,
FenHu_JianZhuMianJi,
FenHu_GongReMianJi,
FenHu_GaoDu,
FenHu_FangZhuXingMing,
FenHu_FangZhuLianXiFangShi,
FenHu_FangZhuDanWei,
FenHu_YiJingGongRe,
FenHu_GongReShiJian,
FenHu_DanYuan,
FenHu_MenPaiHao,
FenHu_BiaoJi1,
FenHu_BiaoJi2,
FenHu_QiTaShuJu,
FenHu_BeiZhu,
FenHu_BiaoZhi,
FenHu_GongReQi, JianZhu_BianMa,
JianZhu_MingCheng, JiFeiLeiXing_BianMa,
JiFeiLeiXing_MingCheng,
JiFeiLeiXing_DanJia, ShouFeiYuan_BianMa,
ShouFeiYuan_XingMing, CaoZuoYuan_BianMa,
CaoZuoYuan_XingMing,
XiaoQu_BianMa,
XiaoQu_MingCheng, BengZhan_BianMa,
BengZhan_MingCheng )
select * from V_FenHu where ' + @TiaoJian1)
declare @FHBM char(12)
declare @GRMJ decimal(8, 2)
declare @YJGR char(1)
declare @GRQ char(4)
declare @TRMJ decimal(8, 2)
declare @JZ_YS decimal(8, 2)
declare @JZ_SS decimal(8, 2)
declare @JZ_YE decimal(8, 2)
declare @QZ_YS decimal(8, 2)
declare @QZ_SS decimal(8, 2)
declare @QZ_YE decimal(8, 2)
--定义游标,用于处理每个分户的投热面积和记账信息
declare FenHu_Cur cursor
static
for
select FenHu_BianMa, FenHu_GongReMianJi, FenHu_YiJingGongRe, FenHu_GongReQi
from #FenHu2
set @FHBM = ''
set @GRMJ = 0.0
set @YJGR = ''
set @GRQ = ''
set @TRMJ = 0.0set @JZ_YS = 0.0
set @JZ_SS = 0.0
set @JZ_YE = 0.0
set @QZ_YS = 0.0
set @QZ_SS = 0.0
set @QZ_YE = 0.0
open FenHu_Cur
fetch next from FenHu_Cur into @FHBM, @GRMJ, @YJGR, @GRQwhile @@fetch_status = 0
begin
--投热面积
if @YJGR = 'Y'
set @TRMJ = @GRMJ
else
set @TRMJ = 0.0 --记账
select @JZ_YS = sum(JieFang), @JZ_SS = sum(DaiFang) from JiZhang where BianMa = @FHBM and GongReQi = @GRQ
if @JZ_YS is null
set @JZ_YS = 0.0
if @JZ_SS is null
set @JZ_SS = 0.0
set @JZ_YE = @JZ_YS - @JZ_SS
--欠账 select @QZ_YS = sum(QiChuQianZhang), @QZ_SS = sum(BenQiGuiHuan) from QianZhang where BianMa = @FHBM and GongReQi = @GRQ
if @QZ_YS is null
set @QZ_YS = 0.0
if @QZ_SS is null
set @QZ_SS = 0.0
set @QZ_YE = @QZ_YS - @QZ_SS
--跟新数据
update #FenHu2 set
FenHu_TouReMianJi = @TRMJ,
JiZhang_YingShou = @JZ_YS,
JiZhang_ShiShou = @JZ_SS,
JiZhang_YuE = @JZ_YE,
QianZhang_YingShou = @QZ_YS,
QianZhang_ShiShou = @QZ_SS,
QianZhang_YuE = @QZ_YE
where FenHu_BianMa = @FHBM fetch next from FenHu_Cur into @FHBM, @GRMJ, @YJGR, @GRQ
endclose FenHu_Cur
deallocate FenHu_Cur
if len(ltrim(rtrim(@PaiXuYiJu))) > 0
exec(' select ' + @TiaoJian0 + ' from #FenHu2 where ' + @TiaoJian2 + ' order by ' + @PaiXuYiJu)
else
exec(' select ' + @TiaoJian0 + ' from #FenHu2 where ' + @TiaoJian2)return
应该有改善的空间吧,该如何做?
谢谢!
drop procedure [dbo].[P2_FenHu]
GOcreate procedure P2_FenHu
@TiaoJian0 varchar(2000),
@TiaoJian1 varchar(2000),
@TiaoJian2 varchar(2000),
@PaiXuYiJu varchar(2000)
as--创建返回表
create table #FenHu2
(
FenHu_id integer,
FenHu_BianMa char(12),
FenHu_MingCheng varchar(100),
FenHu_JianZhuMianJi decimal(8, 2),
FenHu_GongReMianJi decimal(8, 2),
FenHu_GaoDu decimal(4, 2),
FenHu_FangZhuXingMing char(20),
FenHu_FangZhuLianXiFangShi varchar(100),
FenHu_FangZhuDanWei varchar(100),
FenHu_DanYuan char(20),
FenHu_MenPaiHao char(20),
FenHu_YiJingGongRe char(1),
FenHu_GongReShiJian char(10),
FenHu_TouReMianJi decimal(8, 2) default 0.0,
FenHu_BiaoJi1 char(1),
FenHu_BiaoJi2 char(1),
FenHu_QiTaShuJu varchar(100),
FenHu_BeiZhu varchar(200),
FenHu_BiaoZhi char(1),
FenHu_GongReQi char(4), JianZhu_BianMa char(12),
JianZhu_MingCheng varchar(100), JiFeiLeiXing_BianMa char(2),
JiFeiLeiXing_MingCheng varchar(40),
JiFeiLeiXing_DanJia decimal(4, 2), ShouFeiYuan_BianMa char(4),
ShouFeiYuan_XingMing char(20), CaoZuoYuan_BianMa char(4),
CaoZuoYuan_XingMing char(40), XiaoQu_BianMa char(8),
XiaoQu_MingCheng char(40), BengZhan_BianMa char(8),
BengZhan_MingCheng char(40), JiZhang_YingShou decimal(8, 2) default 0.0,
JiZhang_ShiShou decimal(8, 2) default 0.0,
JiZhang_YuE decimal(8, 2) default 0.0,
QianZhang_YingShou decimal(8, 2) default 0.0,
QianZhang_ShiShou decimal(8, 2) default 0.0,
QianZhang_YuE decimal(8, 2) default 0.0
)--给返回表插入符合条件的分户记录
exec('insert into #FenHu2 (
FenHu_id,
FenHu_BianMa,
FenHu_MingCheng,
FenHu_JianZhuMianJi,
FenHu_GongReMianJi,
FenHu_GaoDu,
FenHu_FangZhuXingMing,
FenHu_FangZhuLianXiFangShi,
FenHu_FangZhuDanWei,
FenHu_YiJingGongRe,
FenHu_GongReShiJian,
FenHu_DanYuan,
FenHu_MenPaiHao,
FenHu_BiaoJi1,
FenHu_BiaoJi2,
FenHu_QiTaShuJu,
FenHu_BeiZhu,
FenHu_BiaoZhi,
FenHu_GongReQi, JianZhu_BianMa,
JianZhu_MingCheng, JiFeiLeiXing_BianMa,
JiFeiLeiXing_MingCheng,
JiFeiLeiXing_DanJia, ShouFeiYuan_BianMa,
ShouFeiYuan_XingMing, CaoZuoYuan_BianMa,
CaoZuoYuan_XingMing,
XiaoQu_BianMa,
XiaoQu_MingCheng, BengZhan_BianMa,
BengZhan_MingCheng )
select * from V_FenHu where ' + @TiaoJian1)
update t set
FenHu_TouReMianJi = case when t.FenHu_YiJingGongRe='Y' then isnull(t.FenHu_TouReMianJi,0) else 0 end,
JiZhang_YingShou =isnull(jz.JZ_YS,0),
JiZhang_ShiShou = isnull(jz.JZ_SS,0),
JiZhang_YuE = isnull(jz.JZ_YS,0)-isnull(jz.JZ_SS,0),
QianZhang_YingShou = isnull(qz.QZ_YS,0),
QianZhang_ShiShou = isnull(qz.QZ_SS,0),
QianZhang_YuE = isnull(qz.QZ_YS,0)-isnull(qz.QZ_SS,0)
from #FenHu2 t left join (
select BianMa,GongReQi,JZ_YS = sum(JieFang), JZ_SS = sum(DaiFang) from JiZhang group by BianMa,GongReQi
) as jz
on jz.BianMa = t.FenHu_BianMa and jz.GongReQi = t.FenHu_GongReQi
left join (
select BianMa,GongReQi,QZ_YS = sum(QiChuQianZhang), QZ_SS = sum(BenQiGuiHuan) from QianZhang group by BianMa,GongReQi
) as qz
on qz.BianMa = t.FenHu_BianMa and qz.GongReQi = t.FenHu_GongReQiif len(ltrim(rtrim(@PaiXuYiJu))) > 0
exec(' select ' + @TiaoJian0 + ' from #FenHu2 where ' + @TiaoJian2 + ' order by ' + @PaiXuYiJu)
else
exec(' select ' + @TiaoJian0 + ' from #FenHu2 where ' + @TiaoJian2)return
TSQL的SELECT,UPDATE等操作本身就具备了一定的循环能力,就看你怎么用S它们去组织这些逻辑!
我最佩服能够写出复杂sql的人。
看见这种sql我就发愁。
我试试看。
谢谢。
如何学好sql,给我推荐本书。