Ask a simple SQL statement, thank you ~

lion991125

Form A:

Place of origin
Nanjing Shanghai
Shanghai Beijing
 ...
 ...
 ...

b table
1 Shanghai
2 Nanjing
3 Beijing


Now show:

Place of origin
twenty one
1 3
 ...
 ...
 ...

wgfxman

select B. area ID AS sending place, C. area ID AS receiving place
FROM A table A LEFT JOIN b table B ON A. Issued place = B. Place name
 LEFT JOIN b Table C ON A. Receiving place = C. Place name

我是杀人狂

update table1 set issued = b.id from table2 b where issued = b.region
update table1 set receiving place = b.id from table2 b where receiving place = b.area
select * from table1

fanfan1980

create table A (originating nvarchar (10), receiving nvarchar (10))
insert A select 'Nanjing', 'Shanghai'
union all select 'Shanghai', 'Beijing'

create table B (id int, col nvarchar (10))
insert B select 1, 'Shanghai'
union all select 2, 'Nanjing'
union all select 3, 'Beijing'

select send = tmpA.id, receive = tmpB.id from A
left join B as tmpA on A. issued = tmpA.col
left join B as tmpB on A. receiving place = tmpB.col
 
--result
Place of origin
----------- -----------
twenty one
1 3

(2 row (s) affected)

cv4bf5d65fd

select (select id from b table where name = a. sending place) 'sending place', (select id from b table where name = a. receiving place) 'receiving place' from table A

michealhenry

select t1.id as sending place, t2.id as receiving place
from a inner join b t1 on a. emitted = t1. name
       inner join b t2 on a. receiving place = t2. name

啊啦是奶牛

--Use custom functions
create table a (departure place varchar (10), receiving place varchar (10))
insert into a
select 'Beijing', 'Nanjing'
union all select 'Nanjing', 'Shanghai'
union all select 'Shanghai', 'Beijing'
go
create table b (id int, name varchar (10))
insert into b
select 1, 'Shanghai'
union all select
2, 'Nanjing'
union all select
3, 'Beijing'
go
create function getid (@name varchar (10))
returns int
as
begin
declare @id int
select @ id = id from b where name = @ name
return @id
end
go
select dbo.getid (departure place) as departure place, dbo.getid (receive place) as receive place from a

drop function getid
drop table a
drop table b

--result
Place of departure
3 2
twenty one
1 3