SELECT sbds_1.bh, (sbds_2.bcds - sbds_1.bcds) * dbo.ftys.fts AS 用水量 FROM dbo.ftys FULL OUTER JOIN dbo.sbds sbds_2 ON dbo.ftys.bh = sbds_2.bh FULL OUTER JOIN dbo.sbds sbds_1 ON dbo.ftys.bh = sbds_1.bh AND sbds_2.bz = sbds_1.bz + 1 AND sbds_2.bh = sbds_1.bh WHERE (sbds_2.bcds - sbds_1.bcds IS NOT NULL) 不好意思: 你的 [2。水表设定 sbsd(根据每个表的上级表号来计算其分摊量)] 的表没用上,我是真的猜不透他是干啥事的?
SELECT sbds_1.bh, (sbds_2.bcds - sbds_1.bcds) * dbo.ftys.fts AS 用水量 FROM dbo.ftys FULL OUTER JOIN dbo.sbds sbds_2 ON dbo.ftys.bh = sbds_2.bh FULL OUTER JOIN dbo.sbds sbds_1 ON dbo.ftys.bh = sbds_1.bh AND sbds_2.bz = sbds_1.bz + 1 AND sbds_2.bh = sbds_1.bh WHERE (sbds_2.bcds - sbds_1.bcds IS NOT NULL) 不好意思: 你的 [2。水表设定 sbsd(根据每个表的上级表号来计算其分摊量)] 的表没用上,我是真的猜不透他是干啥事的?
select sbsd.sjbh,sum(sbds.bcds) from sbds left join sbsd on sbds.bh=sbsd.bh where sbds.bz=1 and sbsd.sjbh in (select sjbh from sbsd group by sjbh) group by sbsd.sjbh 这求出了每个总表对应分表的水量总和,后面就很难实现了, 你可以用程序控制他,不要存储过程,你用(总表总水量-对应分表的水量总和)/对应分表的总数就可了了呀
declare @aaa table (bh char.. null,bcds float null) insert into @aaa select bh ,sum(case bz when 0 then bcds else - bcds end ) from sbds group by bh --得到实际水表用量 select distinct sjbh into #tempaa from sbsd select #tempaa.sjbh,@aaa.bcds into #tempbb from #tempaa,@aaa where @aaa.bh = #tempaa.bh --得到上级水表数 select #tempaa.sjbh,(select sum(bcds) from @aaa where bh in (select bh from sbsd where sjbh = #tempaa.bh)) as bcds into #tempcc from #tempaa --得到下级水表数总和 再利用这两个表得出上级表要分摊数,再利用这个分摊数根据分摊运算得出各个分摊。希望你能看明白。呵呵。
insert into tmpsbds select sbsd.sjbh,sum(sbds.bcds) from sbds left join sbsd on sbds.bh=sbsd.bh where sbds.bz=1 and sbsd.sjbh in (select sjbh from sbsd group by sjbh) group by sbsd.sjbhinsert into tmpsbsd select sbsd.sjbh,count(sbsd.bh) from sbsd group by sbsd.sjbhinsert into tmpaverge select sbds.bh,(sbds.bcds-tmpsbds.bcds)/tmpsbsd.num from (tmpsbds left join sbds on sbds.bh=tmpsbds.bh) left join tmpsbsd on sbds.bh=tmpsbsd.bh and sbds.bz=1insert into ftys select sbsd.bh,tmpaverge.average from sbsd left join tmpaverge on sbsd.sjbh=tmpaverge.bh我新加了三个资料表,虽然有些繁,但总酸搞定,给分
CREATE TABLE [dbo].[tmpsbds] ( [bh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [bcds] [float] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[tmpaverge] ( [bh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [average] [float] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[tmpsbsd] ( [bh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [num] [int] NULL ) ON [PRIMARY] GO 生成资料表的SQL语句如上
CREATE TABLE [dbo].[tmpsbsd] ( [bh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [num] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[tmpaverge] ( [bh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [average] [float] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[tmpsbds] ( [bh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [bcds] [float] NULL ) ON [PRIMARY] GO 新表生成语句如下
CREATE PROCEDURE 你的存储过程名 AS SET NOCOUNT ON BEGIN TRAN INSERT INTO ftys(BH,FTS) SELECT DISTINCT SHBH,0 FROM sbsd IF @@ERROR<>0 GOTO ERRORFLAG INSERT INTO ftys(BH,FTS) SELECT A.BH,B.FTS FROM sbsd A INNER JOIN ( SELECT A.SJBH,B.BCDS-SUM(A.BCDS) AS FTS FROM ( SELECT A.BH,A.BCDS-ISNULL(C.BCDS,0) AS BCDS ,B.SHBH FROM (SELECT bh,bcds FROM sbds WHERE BZ=1) A INNER JOIN sbsd B ON A.BH=B.BH LEFT OUTER JOIN (SELECT bh,bcds FROM sbds WHERE BZ=0) C ON A.BH=C.BH ) A INNER JOIN ( SELECT A.BH,A.BCDS-ISNULL(C.BCDS,0) AS BCDS ,B.SHBH FROM (SELECT bh,bcds FROM sbds WHERE BZ=1) A INNER JOIN (SELECT DISTINCT SJBH FROM sbsd) B ON A.BH=B.SJBH LEFT OUTER JOIN (SELECT bh,bcds FROM sbds WHERE BZ=0) C ON A.BH=C.BH ) B ON A.SJBH=B.SJBH GROUP BY A.SJBH,B.BCDS ) B ON A.SJBH=B.SJBH IF @@ERROR<>0 GOTO ERRORFLAG COMMIT TRAN RETURN 0ERRORFLAG: BEGIN ROLLBACK TRAN RETURN -1 END
忘了给你求平均值了,应该是下面的样子 CREATE PROCEDURE 你的存储过程名 AS SET NOCOUNT ON BEGIN TRAN INSERT INTO ftys(BH,FTS) SELECT DISTINCT SHBH,0 FROM sbsd IF @@ERROR<>0 GOTO ERRORFLAG INSERT INTO ftys(BH,FTS) SELECT A.BH,B.FTS FROM sbsd A INNER JOIN ( SELECT A.SJBH,(B.BCDS-SUM(A.BCDS))/COUNT(*) AS FTS FROM ( SELECT A.BH,A.BCDS-ISNULL(C.BCDS,0) AS BCDS ,B.SHBH FROM (SELECT bh,bcds FROM sbds WHERE BZ=1) A INNER JOIN sbsd B ON A.BH=B.BH LEFT OUTER JOIN (SELECT bh,bcds FROM sbds WHERE BZ=0) C ON A.BH=C.BH ) A INNER JOIN ( SELECT A.BH,A.BCDS-ISNULL(C.BCDS,0) AS BCDS ,B.SHBH FROM (SELECT bh,bcds FROM sbds WHERE BZ=1) A INNER JOIN (SELECT DISTINCT SJBH FROM sbsd) B ON A.BH=B.SJBH LEFT OUTER JOIN (SELECT bh,bcds FROM sbds WHERE BZ=0) C ON A.BH=C.BH ) B ON A.SJBH=B.SJBH GROUP BY A.SJBH,B.BCDS ) B ON A.SJBH=B.SJBH IF @@ERROR<>0 GOTO ERRORFLAG COMMIT TRAN RETURN 0ERRORFLAG: BEGIN ROLLBACK TRAN RETURN -1 END
FROM dbo.ftys FULL OUTER JOIN
dbo.sbds sbds_2 ON dbo.ftys.bh = sbds_2.bh FULL OUTER JOIN
dbo.sbds sbds_1 ON dbo.ftys.bh = sbds_1.bh AND sbds_2.bz = sbds_1.bz + 1 AND
sbds_2.bh = sbds_1.bh
WHERE (sbds_2.bcds - sbds_1.bcds IS NOT NULL)
不好意思:
你的 [2。水表设定 sbsd(根据每个表的上级表号来计算其分摊量)]
的表没用上,我是真的猜不透他是干啥事的?
FROM dbo.ftys FULL OUTER JOIN
dbo.sbds sbds_2 ON dbo.ftys.bh = sbds_2.bh FULL OUTER JOIN
dbo.sbds sbds_1 ON dbo.ftys.bh = sbds_1.bh AND sbds_2.bz = sbds_1.bz + 1 AND
sbds_2.bh = sbds_1.bh
WHERE (sbds_2.bcds - sbds_1.bcds IS NOT NULL)
不好意思:
你的 [2。水表设定 sbsd(根据每个表的上级表号来计算其分摊量)]
的表没用上,我是真的猜不透他是干啥事的?
水表共分两级,假设有上级水表号为1的一个总水表,其下有水表号为11、12、13的三个水表,那么一般来说水表1的读数要大于水表11、12、13的和,
因此总分摊量为:读数(1)-[读数(11)+读数(12)+读数(13)],根据这个就可以算出水表11、12、13的分摊数了。当然总水表是不需要参与分摊的。
也就是说,要算某一个水表的分摊数,就首先要从sbsd表中找出其上级表,然后根据上级表的读数、上级表所拥有的下级水表数及下级水表读数的总和就可以算出每个水表的分摊数了。(当然总表分摊数设为0就可以了。)
不知我是否说明白了。
这求出了每个总表对应分表的水量总和,后面就很难实现了,
你可以用程序控制他,不要存储过程,你用(总表总水量-对应分表的水量总和)/对应分表的总数就可了了呀
insert into @aaa
select bh ,sum(case bz when 0 then bcds else - bcds end ) from sbds
group by bh
--得到实际水表用量
select distinct sjbh into #tempaa from sbsd
select #tempaa.sjbh,@aaa.bcds into #tempbb from #tempaa,@aaa where @aaa.bh = #tempaa.bh
--得到上级水表数
select #tempaa.sjbh,(select sum(bcds) from @aaa where bh in (select bh from sbsd where sjbh = #tempaa.bh)) as bcds into #tempcc from #tempaa
--得到下级水表数总和
再利用这两个表得出上级表要分摊数,再利用这个分摊数根据分摊运算得出各个分摊。希望你能看明白。呵呵。
刚开始用存储过程(delphi+sql server),有很多不明白的地方,请多指教!!
<分数绝不是问题!:)>
分数绝不是问题!:)
我先看看。
[bh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[bcds] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tmpaverge] (
[bh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[average] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tmpsbsd] (
[bh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[num] [int] NULL
) ON [PRIMARY]
GO
生成资料表的SQL语句如上
[bh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[num] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tmpaverge] (
[bh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[average] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tmpsbds] (
[bh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[bcds] [float] NULL
) ON [PRIMARY]
GO
新表生成语句如下
AS
SET NOCOUNT ON
BEGIN TRAN INSERT INTO ftys(BH,FTS)
SELECT DISTINCT SHBH,0
FROM sbsd
IF @@ERROR<>0 GOTO ERRORFLAG INSERT INTO ftys(BH,FTS)
SELECT A.BH,B.FTS
FROM sbsd A INNER JOIN
( SELECT A.SJBH,B.BCDS-SUM(A.BCDS) AS FTS
FROM ( SELECT A.BH,A.BCDS-ISNULL(C.BCDS,0) AS BCDS ,B.SHBH
FROM (SELECT bh,bcds FROM sbds WHERE BZ=1) A INNER JOIN
sbsd B ON A.BH=B.BH LEFT OUTER JOIN
(SELECT bh,bcds FROM sbds WHERE BZ=0) C ON A.BH=C.BH
) A INNER JOIN
( SELECT A.BH,A.BCDS-ISNULL(C.BCDS,0) AS BCDS ,B.SHBH
FROM (SELECT bh,bcds FROM sbds WHERE BZ=1) A INNER JOIN
(SELECT DISTINCT SJBH FROM sbsd) B ON A.BH=B.SJBH LEFT OUTER JOIN
(SELECT bh,bcds FROM sbds WHERE BZ=0) C ON A.BH=C.BH
) B ON A.SJBH=B.SJBH
GROUP BY A.SJBH,B.BCDS
) B ON A.SJBH=B.SJBH
IF @@ERROR<>0 GOTO ERRORFLAG COMMIT TRAN
RETURN 0ERRORFLAG:
BEGIN
ROLLBACK TRAN
RETURN -1
END
CREATE PROCEDURE 你的存储过程名
AS
SET NOCOUNT ON
BEGIN TRAN INSERT INTO ftys(BH,FTS)
SELECT DISTINCT SHBH,0
FROM sbsd
IF @@ERROR<>0 GOTO ERRORFLAG INSERT INTO ftys(BH,FTS)
SELECT A.BH,B.FTS
FROM sbsd A INNER JOIN
( SELECT A.SJBH,(B.BCDS-SUM(A.BCDS))/COUNT(*) AS FTS
FROM ( SELECT A.BH,A.BCDS-ISNULL(C.BCDS,0) AS BCDS ,B.SHBH
FROM (SELECT bh,bcds FROM sbds WHERE BZ=1) A INNER JOIN
sbsd B ON A.BH=B.BH LEFT OUTER JOIN
(SELECT bh,bcds FROM sbds WHERE BZ=0) C ON A.BH=C.BH
) A INNER JOIN
( SELECT A.BH,A.BCDS-ISNULL(C.BCDS,0) AS BCDS ,B.SHBH
FROM (SELECT bh,bcds FROM sbds WHERE BZ=1) A INNER JOIN
(SELECT DISTINCT SJBH FROM sbsd) B ON A.BH=B.SJBH LEFT OUTER JOIN
(SELECT bh,bcds FROM sbds WHERE BZ=0) C ON A.BH=C.BH
) B ON A.SJBH=B.SJBH
GROUP BY A.SJBH,B.BCDS
) B ON A.SJBH=B.SJBH
IF @@ERROR<>0 GOTO ERRORFLAG COMMIT TRAN
RETURN 0ERRORFLAG:
BEGIN
ROLLBACK TRAN
RETURN -1
END
我试一试。
以后还请多指教指教我这个菜鸟.......
我好不容易看懂了一半!