疑难问题

2010-01-10 03:24:18 183 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

别再手动算了！用Excel函数一键搞定AQI和首要污染物分析（附完整公式模板）本文介绍如何利用Excel函数自动化计算AQI和首要污染物分析，大幅提升数据处理效率。通过构建标准化输入模板、智能计算IAQI、自动判定AQI与首要污染物，并生成可视化仪表盘，帮助环保工作者快速完成每日监测报告。阅读详情

表TblCustomerTest有以下字段.
CustomerNo,CustomerName
'A001','A0001对应的名称'
'A002','A0002对应的名称'
'A003','A0003对应的名称'
'A004','A0004对应的名称'
'A005','A0005对应的名称'
'A006','A0006对应的名称'

表TblNode有以下字段(注MainBranch只取固定值'0001','0002','0003','0004',BankNodeNo 只取TblNode表字段NodeNo值字段)
NodeNo,NodeName,CustomerNo,MainBranch,BankNodeNo
内容如下,
'N0001','N0001Name','A001','0001','N0002'
'N0002','N0002Name','A001','0001','N0003'
'N0003','N0003Name','A003','0002,'N0003'
'N0004','N0004Name','A003','0002,'N0003'
'N0005','N0005Name','A004','0003,'N0004'
'N0006','N0006Name','A004','0004,'N0003'
'N0007','N0007Name','A005','0001,'N0003'
'N0008','N0008Name','A006','0003,'N0005'
'N0009','N0009Name','A006','0004,'N0005'

已知一个结果表TblReportB表:
CustomerNo,MainBranch,Cost,OtherNum
'A001','0001',1000,0
'A002','0002',1200,0
'A003','0002',1300,0

我需要核算TblReportB表中的OtherNum值.需要以下处理.

更新TblReportB的OtherNum字段需要符合以下要求.
统计TblNode的CustomerNo属于TblCustomerTest,且MainBranch属于固定值'0001','0002','0003','0004',
并且BankNodeNo属于TblNodes的值.

我的处理方式如下，但不是我想要的结果。
请大家帮我处理一下游标中的SQL语句。

DECLARE crsBranch CURSOR FORWARD_ONLY STATIC FOR
--四个固定值
Select BranchNo = '0001'
Union All
Select BranchNo = '0002'
Union All
Select BranchNo = '0003'
Union All
Select BranchNo = '0004'

OPEN crsBranch
FETCH NEXT FROM crsBranch INTO @MainBranch
WHILE @@FETCH_STATUS = 0
BEGIN

Update [TblReportB]
Set OtherNum = b.SNodeNum
From [TblReportB] as a
Join (
Select Cust.CustomerNo,SNodeNum = Count(*) From
TblNode Node
Left Join
TblCustomerTest Cust
On Cust.CustomerNo = Node.CustomerNo And Node.MainBranch=@MainBranch
And Node.BankNodeNo in
(
Select Node.NodeNo From
TblNode Node
Left Join
TblCustomerTest Cust
On Cust.CustomerNo = Node.CustomerNo And Node.MainBranch=@MainBranch
)
Group by Cust.CustomerNo
Having Cust.CustomerNo is not null
) as b
On a.CustomerNo =b.CustomerNo And MainBranch =@MainBranch
FETCH NEXT FROM crsBranch INTO @MainBranch
END

DEALLOCATE crsBranch

我需要的结果是：
预期结果表TblReportB表的值:
CustomerNo,MainBranch,Cost,OtherNum
'A001','0001',1000,2
'A002','0002',1200,0
'A003','0002',1300,2

哪位朋友帮帮忙。

...全文

RedHat 9 替换yum源镜像引言：我们个人通常使用的红帽系统yum源是需要注册付费的，所以在使用yum源安装一些软件时无法正常安装（例如：MySQL），这时我们就需要将yum源替换为CentOS对应的源，以实现在红帽系统中正常安装软件。阅读详情

HappyQQ

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

HappyQQ 2010-01-14

打赏
举报

原来是我第一个存储过程的错,那位帮助我的朋友,我们的结果都没错.

所以考虑问题以后一定不要认为自己的后来的程序有问题.

fanzhouqi 2010-01-10

打赏
举报



if object_id('[TblCustomerTest]') is not null drop table [TblCustomerTest]

create table [TblCustomerTest] (CustomerNo varchar(4),CustomerName varchar(15))

insert into [TblCustomerTest]

select 'A001','A0001对应的名称' union all

select 'A002','A0002对应的名称' union all

select 'A003','A0003对应的名称' union all

select 'A004','A0004对应的名称' union all

select 'A005','A0005对应的名称' union all

select 'A006','A0006对应的名称'

--> 测试数据: [TblNode]

if object_id('[TblNode]') is not null drop table [TblNode]

create table [TblNode] (NodeNo varchar(5),NodeName varchar(9),CustomerNo varchar(4),MainBranch varchar(4),BankNodeNo varchar(5))

insert into [TblNode]

select 'N0001','N0001Name','A001','0001','N0002' union all

select 'N0002','N0002Name','A001','0001','N0003' union all

select 'N0003','N0003Name','A003','0002','N0003' union all

select 'N0004','N0004Name','A003','0002','N0003' union all

select 'N0005','N0005Name','A004','0003','N0004' union all

select 'N0006','N0006Name','A004','0004','N0003' union all

select 'N0007','N0007Name','A005','0001','N0003' union all

select 'N0008','N0008Name','A006','0003','N0005' union all

select 'N0009','N0009Name','A006','0004','N0005'



--> 测试数据: [TblReportB]

if object_id('[TblReportB]') is not null drop table [TblReportB]

create table [TblReportB] (CustomerNo varchar(4),MainBranch varchar(4),Cost int,OtherNum int)

insert into [TblReportB]

select 'A001','0001',1000,0 union all

select 'A002','0002',1200,0 union all

select 'A003','0002',1300,0



declare @t table(MainBranch varchar(4))

insert into @t 

	select '0001'

	union all select '0002'

	union all select '0003'

	union all select '0004'



select CustomerNo,MainBranch,Cost

	,OtherNum = (select count(1)

		from  [TblCustomerTest] b

		inner join [TblNode] c on b.CustomerNo = c.CustomerNo 

		inner join @t d on c.MainBranch = d.MainBranch

	where a.CustomerNo = b.CustomerNo) 

from [TblReportB] a



CustomerNo MainBranch Cost        OtherNum    

---------- ---------- ----------- ----------- 

A001       0001       1000        2

A002       0002       1200        0

A003       0002       1300        2



（所影响的行数为 3 行）

pt1314917 2010-01-10

打赏
举报

QQ:4693718

HappyQQ 2010-01-10

打赏
举报

方便留下其他联系方式吗?

我这个问题苦恼了我一天了,其他我都算出来了,就差一个字段没核算出来了.

兄弟你的核算结果与我的SQL结果好像一样.

pt1314917 2010-01-10

打赏
举报

[Quote=引用 18 楼 happyqq 的回复:]
兄弟还是不行,结果并不是我想要的.
能否再想想办法.
[/Quote]

怎么不是呢？我理解错了？

--> 测试数据: [TblCustomerTest]

if object_id('[TblCustomerTest]') is not null drop table [TblCustomerTest]

create table [TblCustomerTest] (CustomerNo varchar(4),CustomerName varchar(15))

insert into [TblCustomerTest]

select 'A001','A0001对应的名称' union all

select 'A002','A0002对应的名称' union all

select 'A003','A0003对应的名称' union all

select 'A004','A0004对应的名称' union all

select 'A005','A0005对应的名称' union all

select 'A006','A0006对应的名称'

--> 测试数据: [TblNode]

if object_id('[TblNode]') is not null drop table [TblNode]

create table [TblNode] (NodeNo varchar(5),NodeName varchar(9),CustomerNo varchar(4),MainBranch varchar(4),BankNodeNo varchar(5))

insert into [TblNode]

select 'N0001','N0001Name','A001','0001','N0002' union all

select 'N0002','N0002Name','A001','0001','N0003' union all

select 'N0003','N0003Name','A003','0002','N0003' union all

select 'N0004','N0004Name','A003','0002','N0003' union all

select 'N0005','N0005Name','A004','0003','N0004' union all

select 'N0006','N0006Name','A004','0004','N0003' union all

select 'N0007','N0007Name','A005','0001','N0003' union all

select 'N0008','N0008Name','A006','0003','N0005' union all

select 'N0009','N0009Name','A006','0004','N0005'



--> 测试数据: [TblReportB]

if object_id('[TblReportB]') is not null drop table [TblReportB]

create table [TblReportB] (CustomerNo varchar(4),MainBranch varchar(4),Cost int,OtherNum int)

insert into [TblReportB]

select 'A001','0001',1000,0 union all

select 'A002','0002',1200,0 union all

select 'A003','0002',1300,0



update [TblReportB] set othernum=cnt

from [TblReportB] a,

(select customerno,cnt=count(*) from [TblNode] c where exists(select 1 from tblnode where nodeno=c.banknodeno) group by customerno)b

where a.customerno=b.customerno    



--查看结果：

select * from [TblReportB]

HappyQQ 2010-01-10

打赏
举报

兄弟还是不行,结果并不是我想要的.
能否再想想办法.

pt1314917 2010-01-10

打赏
举报

[Quote=引用 16 楼 happyqq 的回复:]
兄弟,你没有把BankNodeNo 考虑进去.

在核算时,需要将核算的条件都要考虑进去.
[/Quote]



--try:

update [TblReportB] set othernum=cnt

from [TblReportB] a,

(select customerno,cnt=count(*) from [TblNode] c where exists(select 1 from tblnode where nodeno=c.banknodeno) group by customerno)b

where a.customerno=b.customerno

HappyQQ 2010-01-10

打赏
举报

兄弟,你没有把BankNodeNo 考虑进去.

在核算时,需要将核算的条件都要考虑进去.

pt1314917 2010-01-10

打赏
举报

--> 测试数据: [TblCustomerTest]

if object_id('[TblCustomerTest]') is not null drop table [TblCustomerTest]

create table [TblCustomerTest] (CustomerNo varchar(4),CustomerName varchar(15))

insert into [TblCustomerTest]

select 'A001','A0001对应的名称' union all

select 'A002','A0002对应的名称' union all

select 'A003','A0003对应的名称' union all

select 'A004','A0004对应的名称' union all

select 'A005','A0005对应的名称' union all

select 'A006','A0006对应的名称'

--> 测试数据: [TblNode]

if object_id('[TblNode]') is not null drop table [TblNode]

create table [TblNode] (NodeNo varchar(5),NodeName varchar(9),CustomerNo varchar(4),MainBranch varchar(4),BankNodeNo varchar(5))

insert into [TblNode]

select 'N0001','N0001Name','A001','0001','N0002' union all

select 'N0002','N0002Name','A001','0001','N0003' union all

select 'N0003','N0003Name','A003','0002','N0003' union all

select 'N0004','N0004Name','A003','0002','N0003' union all

select 'N0005','N0005Name','A004','0003','N0004' union all

select 'N0006','N0006Name','A004','0004','N0003' union all

select 'N0007','N0007Name','A005','0001','N0003' union all

select 'N0008','N0008Name','A006','0003','N0005' union all

select 'N0009','N0009Name','A006','0004','N0005'



--> 测试数据: [TblReportB]

if object_id('[TblReportB]') is not null drop table [TblReportB]

create table [TblReportB] (CustomerNo varchar(4),MainBranch varchar(4),Cost int,OtherNum int)

insert into [TblReportB]

select 'A001','0001',1000,0 union all

select 'A002','0002',1200,0 union all

select 'A003','0002',1300,0





update [TblReportB] set othernum=cnt

from [TblReportB] a,

(select customerno,cnt=count(*) from [TblNode] group by customerno)b

where a.customerno=b.customerno	



--查看结果：

select * from [TblReportB]



--结果：



CustomerNo MainBranch Cost        OtherNum

---------- ---------- ----------- -----------

A001       0001       1000        2

A002       0002       1200        0

A003       0002       1300        2

HappyQQ 2010-01-10

打赏
举报

Select Cust.CustomerNo,SNodeNum = Count(*) From

							TblNode Node

							Left Join 

							TblCustomerTest Cust

							On Cust.CustomerNo = Node.CustomerNo And Node.MainBranch=@MainBranch						

							And Node.BankNodeNo in

								(		

										Select Node.NodeNo From

										TblNode Node

										Left Join 

										TblCustomerTest Cust

										On Cust.CustomerNo = Node.CustomerNo And 										Node.MainBranch=@MainBranch 		

								)

							Group by Cust.CustomerNo

							Having Cust.CustomerNo is not null

以上我的思路,但结果不是我想要的.

HappyQQ 2010-01-10

打赏
举报

标红色的记录是属于我要求的记录.
所以最后

TblReportB表的值将是:
CustomerNo,MainBranch,Cost,OtherNum
'A001','0001',1000,2
'A002','0002',1200,0
'A003','0002',1300,2

HappyQQ 2010-01-10

打赏
举报

未核算之前
TblReportB表已存在以下记录:
CustomerNo,MainBranch,Cost,OtherNum
'A001','0001',1000,0
'A002','0002',1200,0
'A003','0002',1300,0

核算之后的值:
TblReportB表:
CustomerNo,MainBranch,Cost,OtherNum
'A001','0001',1000,2
'A002','0002',1200,0
'A003','0002',1300,2

核算的条件是:
统计TblNode表中CustomerNo属于TblCustomerTest,且MainBranch属于固定值'0001','0002','0003','0004',
并且TblNode表中BankNodeNo字段值属于TblNode的NodeNo字段.

表TblNode
NodeNo,NodeName,CustomerNo,MainBranch,BankNodeNo
'N0001','N0001Name','A001','0001','N0002'
'N0002','N0002Name','A001','0001','N0003'
'N0003','N0003Name','A003','0002,'N0003'
'N0004','N0004Name','A003','0002,'N0003'
'N0005','N0005Name','A004','0003,'N0004'
'N0006','N0006Name','A004','0004,'N0003'
'N0007','N0007Name','A005','0001,'N0003'
'N0008','N0008Name','A006','0003,'N0005'
'N0009','N0009Name','A006','0004,'N0005'

pt1314917 2010-01-10

打赏
举报

[Quote=引用 10 楼 happyqq 的回复:]
不好意思,第一次在这上面发这么长的问题,也不知道如何方便你们处理我的问题.

[/Quote]

还把TblReportB表的数据贴出来。另外把想要的结果。以及得到结果的规律说一下。

HappyQQ 2010-01-10

打赏
举报

不好意思,第一次在这上面发这么长的问题,也不知道如何方便你们处理我的问题.

HappyQQ 2010-01-10

打赏
举报

已知一个结果表TblReportB表:
CustomerNo,MainBranch,Cost,OtherNum
'A001','0001',1000,0
'A002','0002',1200,0
'A003','0002',1300,0

我需要核算TblReportB表中的OtherNum值.需要以下处理.
条件是:
统计TblNode的CustomerNo属于TblCustomerTest,且MainBranch属于固定值'0001','0002','0003','0004',
并且BankNodeNo属于TblNodes的值.
正确的结果是:
CustomerNo,MainBranch,Cost,OtherNum
'A001','0001',1000,2
'A002','0002',1200,0
'A003','0002',1300,2

其他,请参考我的那段SQL语句.

alexjack1210 2010-01-10

打赏
举报

楼主你题目怎么这么长，都晕了！你要得到什么样的结果咯？

netcup 2010-01-10

打赏
举报

太长了，看着晕，。另外，麻烦提问者把建表语句和插入数据的语句都写好，别人省了时间了

pt1314917 2010-01-10

打赏
举报

CustomerNo,MainBranch,Cost,OtherNum
'A001','0001',1000,2
'A002','0002',1200,0
'A003','0002',1300,2

---------------
能说说你这结果是怎么来的吗？

HappyQQ 2010-01-10

打赏
举报

顶起来,高手们,帮帮忙.

HappyQQ 2010-01-10

打赏
举报

顶起来,高手们,帮帮忙.

加载更多回复（3）

疑难问题

相关推荐