有什么办法能快速替换表中的字段值

[低代码开发平台]

表A结构如下，存储的是商品销售申请单的信息如下
A（ID，DepartMentID， CompaneyBankID，BillNO，ShoukuanMoney）
其中DepartMentID与DepartMent（部门表中的ID对应），DepartMent表结构如下：
ID Name
1 财务部
2 销售部
CompaneyBankID与CompaneyBank表中的ID值对应，CompaneyBank表结构如下：
ID Name
1 中国银行
2 建设银行
3 工商银行
现在A表的数据如下：
ID DepartMentID CompaneyBankID BillNO ShoukuanMoney
1 1 1 111 100
2 1 2 222 200
3 2 3 333 300
，如何将A表的数据变成下面的样子：
ID DepartMentID CompaneyBankID BillNO ShoukuanMoney
1 财务部 中国银行 111 100
2 财务部 建设银行 222 200
3 销售部 工商银行 333 300
现在将创建表的语句提供如下：供大家开发测试
create table A (ID int, DepartMentID int ， CompaneyBankID int ，BillNO int，ShoukuanMoney float)
insert into A values(1 , 1 , 1，111，100)
insert into A values(2 , 1 , 2，222，200)
insert into A values(3 , 2 , 4，333，300)
create table DepartMent (ID int, Name varchar（20）)
insert into DepartMent values(1 , ‘财务部’)
insert into DepartMent values(2 , ‘销售部’)


create table CompaneyBank (ID int, Name varchar（20）)
insert into CompaneyBank values(1 , ‘中国银行’)
insert into CompaneyBank values(2 , ‘建设银行’)
insert into CompaneyBank values(2 , ‘工商银行’)

[低代码开发平台]

create table A (ID int, DepartMentID int, CompaneyBankID int ,BillNO int,ShoukuanMoney float)
insert into A values(1 , 1 , 1,111,100)
insert into A values(2 , 1 , 2,222,200)
insert into A values(3 , 2 , 4,333,300)
create table DepartMent (ID int, Name varchar(20))
insert into DepartMent values(1 , '财务部')
insert into DepartMent values(2 , '销售部')


create table CompaneyBank (ID int, Name varchar(20))
insert into CompaneyBank values(1 , '中国银行')
insert into CompaneyBank values(2 , '建设银行')
insert into CompaneyBank values(2 , '工商银行')

[csdyyr]

SELECT A.ID,D.CompaneyBank,B.CompaneyBank,BillNO,ShoukuanMoney
FROM A JOIN DepartMent AS D JOIN CompaneyBank AS B
ON A.DepartMentID=D.ID AND A.CompaneyBankID=B.ID

[等不到来世]

select A.id, DepartMentID=d.name,CompaneyBankID=c.name,a.CompaneyBankID ,a.BillNO ,a.ShoukuanMoney 
from A,DepartMent d,CompaneyBank c
where A.id=d.id and A.id=c.id
/*
id          DepartMentID         CompaneyBankID       CompaneyBankID BillNO      ShoukuanMoney
----------- -------------------- -------------------- -------------- ----------- ----------------------
1           财务部                  中国银行                 1              111         100
2           销售部                  建设银行                 2              222         200
2           销售部                  工商银行                 2              222         200
*/

[低代码开发平台]

select c.*,d.Name from (select b.Name,a.BillNO,a.ShoukuanMoney,a.CompaneyBankID from A a inner join DepartMent b on a.DepartMentID=b.ID) c inner join CompaneyBank d on c.CompaneyBankID=d.ID

有没有比这更好的方法。因为实际中我A表引用的很多字段

[duset]

--lz,你的表中,2列定义成int型了,要先转成字符型

alter table a alter column departmentid VARCHAR(20)
alter table a alter column CompaneyBankID VARCHAR(20)

update a
set departmentID = d.name,
    companeybankid = c.name
from department d,companeybank c
where a.departmentid = d.ID
    and a.CompaneyBankID = c.id
    
    SELECT * FROM a

[wxg22526451]

select    ID,
	   DepartMentID=(select Name from DepartMent where ID=A.DepartMentID), 
	   CompaneyBankID=(select Name from CompaneyBank where ID=A.CompaneyBankID),	  
           BillNO,
	   ShoukuanMoney
from A 

[duset]

--如果是要查询结果的话
SELECT a.id,d.[Name],c.[Name]
    FROM a,DepartMent d,CompaneyBank c
    WHERE a.DepartMentID = d.ID
		AND a.CompaneyBankID = c.ID

[低代码开发平台]

create table A (ID int, DepartMentID int ， CompaneyBankID int ，BillNO int，ShoukuanMoney float)
insert into A values(1 , 1 , 1，111，100)
insert into A values(2 , 1 , 2，222，200)
insert into A values(3 , 2 , 3，333，300)
create table DepartMent (ID int, Name varchar（20）)
insert into DepartMent values(1 , ‘财务部’)
insert into DepartMent values(2 , ‘销售部’)


create table CompaneyBank (ID int, Name varchar（20）)
insert into CompaneyBank values(1 , ‘中国银行’)
insert into CompaneyBank values(2 , ‘建设银行’)
insert into CompaneyBank values(3 , ‘工商银行’)
我刚才给的数据有错误，现在重新附上。3楼的好像不对。有没有比下面这个更好的方法
select c.*,d.Name from (select b.Name,a.BillNO,a.ShoukuanMoney,a.CompaneyBankID from A a inner join DepartMent b on a.DepartMentID=b.ID) c inner join CompaneyBank d on c.CompaneyBankID=d.ID

[等不到来世]

create table A (ID int,    DepartMentID int , CompaneyBankID int,BillNO int,ShoukuanMoney float) 
insert into A values(1 ,  1 ,  1,111,100) 
insert into A values(2 ,  1 ,  2,222,200) 
insert into A values(3 ,  2 ,  3,333,300) 
create table DepartMent (ID int,  Name varchar(20)) 
insert into DepartMent values(1 ,  '财务部') 
insert into DepartMent values(2 ,  '销售部') 


create table CompaneyBank (ID int,  Name varchar(20)) 
insert into CompaneyBank values(1 ,  '中国银行') 
insert into CompaneyBank values(2 ,  '建设银行') 
insert into CompaneyBank values(3 ,  '工商银行') 

select * from A

select A.id, DepartMentID=d.name,CompaneyBankID=c.name,a.CompaneyBankID ,a.BillNO ,a.ShoukuanMoney 
from A,DepartMent d,CompaneyBank c
where A.DepartMentID=d.id and A.CompaneyBankID=c.id
/*
id          DepartMentID         CompaneyBankID       CompaneyBankID BillNO      ShoukuanMoney
----------- -------------------- -------------------- -------------- ----------- ----------------------
1           财务部                  中国银行                 1              111         100
2           财务部                  建设银行                 2              222         200
3           销售部                  工商银行                 3              333         300

*/
drop table A
drop table DepartMent
drop table CompaneyBank

[等不到来世]

多写了一列。

select A.id, DepartMentID=d.name,CompaneyBankID=c.name,a.BillNO ,a.ShoukuanMoney 
from A,DepartMent d,CompaneyBank c
where A.DepartMentID=d.id and A.CompaneyBankID=c.id
/*
id          DepartMentID         CompaneyBankID       BillNO      ShoukuanMoney
----------- -------------------- -------------------- ----------- ----------------------
1           财务部                  中国银行                 111         100
2           财务部                  建设银行                 222         200
3           销售部                  工商银行                 333         300
*/

[低代码开发平台]

非常感谢大家帮忙，答案有三种，我都贴出来给大家一起讨论，那种方法比较好了
select c.*,d.Name from (select b.Name,a.BillNO,a.ShoukuanMoney,a.CompaneyBankID from A a inner join DepartMent b on a.DepartMentID=b.ID) c inner join CompaneyBank d on c.CompaneyBankID=d.ID
更好的解决办法：
1.
SELECT a.id,a.ShoukuanMoney,a.BillNo,d.[Name],c.[Name]
FROM a,DepartMent d,CompaneyBank c
WHERE a.DepartMentID = d.ID
AND a.CompaneyBankID = c.ID
2。
select ID, DepartMentID=(select Name from DepartMent where ID=A.DepartMentID), CompaneyBankID=(select Name from CompaneyBank where ID=A.CompaneyBankID), BillNO, ShoukuanMoney from A

3.
select A.id, DepartMentID=d.name,CompaneyBankID=c.name,a.CompaneyBankID ,a.BillNO ,a.ShoukuanMoney
from A,DepartMent d,CompaneyBank c
where A.DepartMentID=d.id and A.CompaneyBankID=c.id