我有两个表,如下
Company Country
---------------------
abc 123 abc USA
def 456 def USA
ghi 789 ghi USA
Company State
------------------------
abc 123 TX
def 234 def def NY
ghi 789 AZ
我需要从表1中查询Company,并将前两个单词与表2中的Company进行比较,如果匹配,则打印输出。我已经成功地使用代码从表1中获得了前两个单词
SELECT SUBSTRING (
tbSurvey.company,
0,
CHARINDEX (' ',
tbSurvey.company,
CHARINDEX (' ', tbSurvey.company, 0) + 1))
FROM tbSurvey;
我无法将该列与表2中的公司列相匹配。我正在尝试使用代码,
SELECT endcustomername, endcustomercode, country
FROM tbLicense
WHERE EXISTS
(SELECT company, endcustomername, endcustomercode
FROM tbSurvey, tblicense
WHERE tbSurvey.company < tbLicense.endcustomername
AND tbSurvey.company <> ' '
AND tbLicense.endcustomercode LIKE
SUBSTRING (
tbSurvey.company,
0,
CHARINDEX (
' ',
tbSurvey.company,
CHARINDEX (' ', tbSurvey.company, 0) + 1))
+ '%');
但是我没有得到想要的输出。请帮忙。
嗯,它不是很简洁和可读,但我认为它可以完成你需要的工作
with cte_survey as (
select
t.*,
case when s.i > 0 then left(t.company, s.i - 1) else t.company end as k
from tbSurvey as t
outer apply (select charindex(' ', t.company) as i) as f
outer apply (select case when f.i > 0 then charindex(' ', t.company, f.i + 1) else len(company) end as i) as s
)
select
s.company as sur_compant, l.company as lic_company, s.country, l.[state] as [state]
from cte_survey as s
inner join tblicense as l on l.company = s.k
sql fiddle演示
如果您想按前两个字比较两列:
with cte_survey as (
select
t.*,
case when s.i > 0 then left(t.company, s.i - 1) else t.company end as k
from tbSurvey as t
outer apply (select charindex(' ', t.company) as i) as f
outer apply (select case when f.i > 0 then charindex(' ', t.company, f.i + 1) else len(company) end as i) as s
),
cte_license as (
select
t.*,
case when s.i > 0 then left(t.company, s.i - 1) else t.company end as k
from tblicense as t
outer apply (select charindex(' ', t.company) as i) as f
outer apply (select case when f.i > 0 then charindex(' ', t.company, f.i + 1) else len(company) end as i) as s
)
select
s.company as sur_compant, l.company as lic_company, s.country, l.[state] as [state]
from cte_survey as s
inner join cte_license as l on l.k = s.k
sql-fiddle演示