sql - How To Pull In Columns From A Derived Table Or Sub Query
问题描述
I have a query that looks for records that don't have a matching account number and tries to match those accounts by address.
I am getting the results I want, but I want to include columns from the table2 below. How can I do this?
Select DISTINCT
account_num
,product
,accountName
,address_1
,address_2
,city
,state
,zip
,short_address
INTO #Matching_Address
From #Non_Matching_Accounts t
Where EXISTS
(SELECT * FROM (SELECT
left(ADDRESS_LINE1_TXT,20) AS matching_add
,CITY
,STATE
,ZIP
,ACCOUNT_OWNER
From [database].[dbo].[table2]) v (matching_add, CITY, STATE,ZIP,ACCOUNT_OWNER)
WHERE
t.short_address= v.matching_add
AND t.city= v.NAME
AND t.state = v.STATE
AND t.zip = v.ZIP
AND t.accountName LIKE '%'+v.ACCOUNT_OWNER+'%')
I've tried:
Select DISTINCT
account_num
,product
,accountName
,address_1
,address_2
,city
,state
,zip
,short_address
,matching_add
,CITY
,STATE
,ZIP
,ACCOUNT_OWNER
INTO #Matching_Address
From #Non_Matching_Accounts t
Where EXISTS
(SELECT * FROM (SELECT
left(ADDRESS_LINE1_TXT,20) AS Select DISTINCT
account_num
,product
,accountName
,address_1
,address_2
,city
,state
,zip
,short_address
INTO #Matching_Address
From #Non_Matching_Accounts t
Where EXISTS
(SELECT * FROM (SELECT
left(ADDRESS_LINE1_TXT,20) AS matching_add
,CITY
,STATE
,ZIP
,ACCOUNT_OWNER
From [database].[dbo].[table2]) v (matching_add, CITY, STATE,ZIP,ACCOUNT_OWNER)
WHERE
t.short_address= v.matching_add
AND t.city= v.NAME
AND t.state = v.STATE
AND t.zip = v.ZIP
AND t.accountName LIKE '%'+v.ACCOUNT_OWNER+'%')
From [database].[dbo].[table2]) v (matching_add, CITY, STATE,ZIP,ACCOUNT_OWNER)
WHERE
t.short_address= v.matching_add
AND t.city= v.NAME
AND t.state = v.STATE
AND t.zip = v.ZIP
AND t.accountName LIKE '%'+v.ACCOUNT_OWNER+'%')
Expected Results:
acct_num|prd|actName|add1|add2|city|state|zip|act_num2|prd2|actName|add1|add2|city2|state2|zip2|
----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
a | a | a | a | a | a | a | a | a | a | a | a | a | a a| a
b | b | b | b | b | b | b | b | b | b | b | b | b | b | b
c | c | c | c | c | c | c | c | c | c | c | c | c | c | c |
d | d | d | d | d | d | d | d | d | d | d | d | d | d | d |
解决方案
当建议使用“内部连接”时,您使用的是“存在”。重组如下:
select
distinct t.account_num,
t.product,
t.accountName,
t.address_1,
t.address_2,
t.city,
t.state,
t.zip,
t.short_address,
matching_add = left(v.address_line1_txt,20),
vCity = v.city,
vState = v.state,
vZip = v.zip,
v.account_owner
into #Matching_Address
from #Non_Matching_Accounts t
join [database].[dbo].[table2] v
on t.short_address = v.matching_add
and t.city = v.name
and t.state = v.state
and t.zip = v.zip
and t.accountName like '%' + v.account_owner + '%'
内部连接(或简称“连接”)只会返回匹配项,因此在这个意义上它就像“存在”。但它使您可以使用右侧表格中的列。
我的预感是你可能已经尝试过了。我在您的查询中看到了一个“不同的”,这对于“存在”来说可能是不必要的。您是否因为重复您的行而放弃了“内部联接”?如果是这样,“存在”仍然不是答案。也许交叉申请可以帮助您:
select ... (same as above)
into #Matching_Address
from #Non_Matching_Accounts t
cross apply (
select
top 1 *
from [database].[dbo].[table2] v
where t.short_address = v.matching_add
and t.city = v.name
and t.state = v.state
and t.zip = v.zip
and t.accountName like '%' + v.account_owner + '%'
order by v.matching_add -- or whatever puts the better one on top
) v
使用 'top 1','v' 结果将在 't' 中每行产生不超过 1 条记录。使用 'cross apply',如果 'v' 的结果是没有记录,则 't' 不会返回一行,(类似于 'exists' 或 'inner join')。
推荐阅读
- python - 将数据框与映射中的多级列合并
- r - dplyr 在 mutate 中同时使用 rowwise 和 df-wise 值
- ios - iOS Swift 中的 ARKit 导航?
- javascript - 嵌入 YouTube 视频作为背景,没有广告
- qt - 不同事件的 Qt Quick 布局更改
- bash - 用于执行 linux 文件的 Bash 脚本
- javascript - SweetAlert2 : 单击按钮后基于 div 的选择执行“函数”或“异步函数”
- java - 通过从具有不同对象的重复元素的列表中删除对象来创建新的不同列表
- pytorch - 使用 PyTorch 运行单个预测的问题
- laravel - Laravel 中的授权策略参数错误