如何将多个子选择的SQL压缩成更合理的东西？

以下查询根据需要返回单行.'contract'表有6个字段,每个字段都有不同的用户名,我想从中检索单独的'users'表中的名/姓.这很好用,但还有更简洁的东西吗？我认为解决方案必须是使用GROUP BY contracts.id来保持一行,但我似乎找不到比这一系列子选择更好的东西.

救命!

SELECT contracts.field1, contracts.field2, 
(SELECT first_name FROM users WHERE username = service_provider_1),
(SELECT last_name FROM users WHERE username = service_provider_1),
(SELECT first_name FROM users WHERE username = service_provider_2),
(SELECT last_name FROM users WHERE username = service_provider_2),
(SELECT first_name FROM users WHERE username = service_org_business_contact),
(SELECT last_name FROM users WHERE username = service_org_business_contact),
(SELECT first_name FROM users WHERE username = client_service_contact_1),
(SELECT last_name FROM users WHERE username = client_service_contact_1),
(SELECT first_name FROM users WHERE username = client_service_contact_2),
(SELECT last_name FROM users WHERE username = client_service_contact_2),
(SELECT first_name FROM users WHERE username = client_business_contact),
(SELECT last_name FROM users WHERE username = client_business_contact)
FROM contracts
WHERE id = ?

它不会那么糟糕,如果我能得到既从一个单一的子选择/姓......所以即使有眉头子选择的解决方案我有两次,我想我需要冗长...

编辑:我现在明白了.能够多次加入同一个表的答案是使用表的别名.谢谢大家!新代码是:

SELECT contracts.field1, contracts.field2, 
sp1.first_name, sp1.last_name, 
sp2.first_name, sp2.last_name, 
sobc.first_name, sobc.last_name, 
csc1.first_name, csc1.last_name, 
csc2.first_name, csc2.last_name, 
cbc.first_name, cbc.last_name
FROM contracts
JOIN users AS sp1 ON service_provider_1 = sp1.username
JOIN users AS sp2 ON service_provider_2 = sp2.username
JOIN users AS sobc ON service_org_business_contact = sobc.username
JOIN users AS csc1 ON client_service_contact_1 = csc1.username
JOIN users AS csc2 ON client_service_contact_2 = csc2.username
JOIN users AS cbc ON client_business_contact = cbc.username
WHERE contracts.id = ?

遗憾的是,使用连接几乎和使用子选择一样冗长,但我认为它可能更快？

为什么不加入到用户表6次？

SELECT contracts.field1, contracts.field2, 
sp1.first_name,
sp1.last_name 
sp2.first_name,
sp2.last_name,
/* etc, etc */
FROM
contracts
INNER JOIN
users sp1
ON
contracts.id = sp1.id
AND sp1.username = service_provider_1
INNER JOIN 
users sp2
ON contracts.id = sp2.id
AND sp2.username = service_provider_2
INNER JOIN 
users sobc
ON contracts.id = sobc.id
AND sobc.username = service_org_business_contact
INNER JOIN
/* etc, etc */
WHERE contracts.id = @myid

您是否也想结合first_name,并last_name为每个用户名？你可以这样做

RTRIM(sp1.first_name) + ' ' + RTRIM(sp1.last_name) as sp1_name

在你的SELECT子句中.如果数据类型是(N)CHAR,则RTRIM是必需的,如果类型是(N)VARCHAR则不需要

编辑:正如在这个答案的评论中所述,id上的JOIN可能没有必要,在这种情况下它变成了

SELECT 
contracts.field1, 
contracts.field2, 
sp1.first_name,
sp1.last_name 
sp2.first_name,
sp2.last_name,
/* etc, etc */
FROM
contracts
INNER JOIN
users sp1
ON
sp1.username = service_provider_1
INNER JOIN 
users sp2
ON
sp2.username = service_provider_2
INNER JOIN 
users sobc
ON 
sobc.username = service_org_business_contact
INNER JOIN
/* etc, etc */
WHERE contracts.id = @myid

我的布局可能会让它看起来更长!如果可能的合同记录中没有first_name和last_name用户表中的一个字段,则可能需要使用LEFT OUTER JOINS .

select 
 c.field1,c.field2
,SP1.first_name ,SP1.last_name
,SP2.first_name ,SP2.last_name
,SOBC.first_name,SOBC.last_name
,CSC1.first_name,CSC1.last_name
,CSC2.first_name,CSC2.last_name
,CBC.first_name ,CBC.last_name
from contracts C
left join users as SP1  on SP1.Username  = C.service_provider_1 
left join users as SP2  on SP2.Username  = C.service_provider_2 
left join users as SOBC on SOBC.Username = C.service_org_business_contact 
left join users as CSC1 on SP1.Username  = C.client_service_contact_1 
left join users as CSC2 on SP1.Username  = C.client_service_contact_2 
left join users as CBC  on CBC.Username  = C.client_business_contact 

where c.ID = ?