如何在现有SQL查询中添加WkVehFl表关联的VIN_NO字段查询？

方法一：使用标量子查询作为字段 #

直接在SELECT字段列表中添加标量子查询（需确保子查询返回单个值），修改后的完整SQL如下：

select rof.branch AS 'Location', 
work_order = rof.ro_number,
rof.driver, 
rof.REG,
-- 新增的pin字段（关联VIN_NO）
pin = (SELECT VIN_NO from WkVehFl where WkVehFl.REG=rof.REG),
customer = rof.driver || ' - ' || (select if isnull(company_name,'') <> '' 
        then company_name 
        else name || ' ' || surname endif
        from contact 
        where contact.contact_code = rof.driver), 
wos.job_code, 
wos.DETAIL_LINE,
wos.STATUS,
wos.INVOICE_NO,
sales_type = case wos.type
    when 'W' then 'Warranty'
    when 'R' then 'Retail'
    when 'S' then 'Misc'
    when 'I' then 'Internal'
    when 'P' then 'Policy'
    when 'F' then 'Fleet'
    when 'E' then 'Excess'
    when 'B' then 'Project Billing'
    else wos.type end, 
opened_date = date(wos.creation_date), 
last_labor_date = (select max(date(finish_time)) from wkmechwk where wkmechwk.ro_number = rof.ro_number and wkmechwk.job_code = wos.job_code), 
days_open = today() - date(wos.creation_date)+ 1
from WkRoFile rof inner join wkothsub wos on rof.ro_number = wos.ro_number
where 
date(wos.creation_date) >={ts '2021-01-01 00:00:00.00000'}  

如果WkVehFl中同一个REG对应多条VIN记录，子查询会返回多行导致报错，此时可以用TOP 1或聚合函数限制结果：

-- 取第一条匹配的VIN_NO（可按需调整排序规则）
pin = (SELECT TOP 1 VIN_NO from WkVehFl where WkVehFl.REG=rof.REG ORDER BY VIN_NO),
-- 或取最大的VIN_NO
pin = (SELECT MAX(VIN_NO) from WkVehFl where WkVehFl.REG=rof.REG),

方法二：使用JOIN关联表 #

若WkVehFl与WkRoFile是一对一/一对多关系，用JOIN关联的方式性能更优，适合大数据量场景。修改后的SQL如下：

select rof.branch AS 'Location', 
work_order = rof.ro_number,
rof.driver, 
rof.REG,
-- 直接从关联表获取VIN_NO作为pin
pin = wvf.VIN_NO,
customer = rof.driver || ' - ' || (select if isnull(company_name,'') <> '' 
        then company_name 
        else name || ' ' || surname endif
        from contact 
        where contact.contact_code = rof.driver), 
wos.job_code, 
wos.DETAIL_LINE,
wos.STATUS,
wos.INVOICE_NO,
sales_type = case wos.type
    when 'W' then 'Warranty'
    when 'R' then 'Retail'
    when 'S' then 'Misc'
    when 'I' then 'Internal'
    when 'P' then 'Policy'
    when 'F' then 'Fleet'
    when 'E' then 'Excess'
    when 'B' then 'Project Billing'
    else wos.type end, 
opened_date = date(wos.creation_date), 
last_labor_date = (select max(date(finish_time)) from wkmechwk where wkmechwk.ro_number = rof.ro_number and wkmechwk.job_code = wos.job_code), 
days_open = today() - date(wos.creation_date)+ 1
from WkRoFile rof 
inner join wkothsub wos on rof.ro_number = wos.ro_number
-- 用REG字段关联车辆表
left join WkVehFl wvf on wvf.REG = rof.REG
where 
date(wos.creation_date) >={ts '2021-01-01 00:00:00.00000'}  

这里用LEFT JOIN是为了保留原查询中所有记录（无匹配VIN时pin为NULL）；若仅需保留有对应VIN的记录，可替换为INNER JOIN。

内容的提问来源于stack exchange，提问作者user20574790