我有這些 Postgres 表：
create table deals_new
(
id bigserial primary key,
slip_id text,
deal_type integer,
timestamp timestamp,
employee_id bigint
constraint employee_id_fk
references common.employees
);
create table twap
(
id bigserial primary key,
deal_id varchar not null,
employee_id bigint
constraint fk_twap__employee_id
references common.employees,
status integer
);
create table employees
(
id bigint primary key,
account_id integer,
first_name varchar(150),
last_name varchar(150)
);
New table to query:
create table accounts
(
id bigint primary key,
account_name varchar(150) not null
);
我使用這個 SQL 查詢：
select d.*, t.id as twap_id
from common.deals_new d
left outer join common.twap t on
t.deal_id = d.slip_id and
d.timestamp between '11-11-2021' AND '11-11-2021' and
d.deal_type in (1, 2) and
d.quote_id is null
where d.employee_id is not null
order by d.timestamp desc, d.id
offset 10
limit 10;
如何擴展此 SQL 查詢以在表中搜索employees并將account_id結果映射到表accounts中id？我也想列印accounts. account_name基于employees .account_id.
uj5u.com熱心網友回復：
您需要兩個連接才能使這項作業適合您。一次連接到雇員表，再一次連接到帳戶表。
select d.*, t.id as twap_id, a.account_name
from common.deals_new d
left outer join common.twap t on
t.deal_id = d.slip_id and
d.timestamp between '11-11-2021' AND '11-11-2021' and
d.deal_type in (1, 2) and
d.quote_id is null
join employees as e on d.employee_id = e.id
join accounts as a on a.id = e.account_id
where d.employee_id is not null
order by d.timestamp desc, d.id
offset 10
limit 10;
注意：我沒有擺弄這個，所以可能有一個錯字，但我想你在這里明白了。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/410446.html
標籤：
上一篇：通過從下到上的外鍵連接表/獲取值（使用where子句IN陣列）？（見示例）
下一篇：在ms訪問中按組選擇前x