22,210
社区成员
发帖
与我相关
我的任务
分享
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 行)
??--> 测试数据: [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]
--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
--> 测试数据: [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
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