sql - 尝试在两个过滤表之间执行 SQL 连接
问题描述
我正在尝试加入两个表,但对两者都应用了过滤器。例如。过滤左表,然后与右表的过滤版本连接,保留左表。
我的查询如下:
SELECT REP_Exposure_secured.DeliveryRegion, REP_Exposure_secured.DeliveryCountry,
REP_Exposure_secured.EngagementCompanyCode, REP_Exposure_secured.EngagementId,
REP_Exposure_secured.Engagement, REP_Exposure_secured.EngagementType,
REP_Exposure_secured.EngagementStatus, REP_Exposure_secured.EngagementPartner,
REP_Exposure_secured.EngagementPartnerID, REP_Exposure_secured.EngagementManager,
REP_Exposure_secured.ServiceLine, REP_Exposure_secured.SubServiceLine,
REP_Exposure_secured.Competency, REP_Exposure_secured.ServiceOffering,
REP_Exposure_secured.EngagementServiceCode, REP_Exposure_secured.EngagementService,
REP_Exposure_secured.EngagementLastTimeChargedDate, REP_Exposure_secured.EngagementCreationDate,
REP_Exposure_secured.Account, REP_Exposure_secured.UltimateDunsNumber,
REP_Exposure_secured.Client, REP_Exposure_secured.ClientID,
REP_Exposure_secured.AR_0_30, REP_Exposure_secured.AR_31_60,
REP_Exposure_secured.AR_61_90, REP_Exposure_secured.AR_91_120,
REP_Exposure_secured.AR_121_150, REP_Exposure_secured.AR_151_179,
REP_Exposure_secured.AR_180_365, REP_Exposure_secured.AR_Above_365,
REP_Exposure_secured.MTD_TER, REP_Exposure_secured.MTD_BilledAmt,
REP_Exposure_secured.MTD_CollectedAmt, REP_Exposure_secured.UnbilledInvAging30Amt,
REP_Exposure_secured.UnbilledInvAging60Amt, REP_Exposure_secured.UnbilledInvAging90Amt,
REP_Exposure_secured.UnbilledInvAging150Amt, REP_Exposure_secured.UnbilledInvAging180Amt,
REP_Exposure_secured.UnbilledInvAging365Amt, REP_Exposure_secured.UnbilledInvAging365PlusAmt,
REP_FTEReport_secured.GPN, REP_FTEReport_secured.Country AS FTE_Country,
REP_FTEReport_secured.Country AS FTE_Country.EmployeeName AS FTE_Name
FROM Reporting.REP_Exposure_secured
LEFT JOIN REP_FTEReport_secured
ON REP_Exposure_secured.EngagementPartnerID = REP_FTEReport_secured.GUI
WHERE (REP_Exposure_secured.DeliveryCountry IN ('Cambodia', 'Singapore', 'Malaysia', 'Indonesia',
'Guam', 'Laos', 'Maldives', 'Myanmar', 'Philippines', 'Sri Lanka', 'Thailand', 'Vietnam')
OR REP_Exposure_secured.DeliveryCountry LIKE 'Brunei Daruss%')
AND REP_Exposure_secured.SubServiceLine='Forensics'
AND REP_Exposure_secured.EngagementType LIKE 'External%'
AND REP_Exposure_secured.Currency='USD'
AND REP_FTEReport_secured.AccountingCycleDate IN
(
SELECT
DISTINCT
MAX(AccountingCycleDate)
FROM
REP_FTEReport_secured
)
AND REP_FTEReport_secured.Country IN ('Brunei', 'Cambodia', 'Guam', 'Indonesia', 'Lao', 'Malaysia', 'Maldives',
'Myanmar', 'Philippines', 'Singapore', 'Sri Lanka', 'Thailand', 'Vietnam')
AND REP_FTEReport_secured.SubServiceLine='Forensics'
AND REP_FTEReport_secured.RankName IN ('Partner', 'Director', 'Executive Director');
这将返回以下错误:
: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]'.' 附近的语法不正确。(102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][ SQL Server]关键字“AND”附近的语法不正确。(156)")
对我做错的任何帮助将不胜感激!
解决方案
我认为您的问题在于这一行,即您的 select 子句中的最后一个字段。
REP_FTEReport_secured.Country AS FTE_Country.EmployeeName AS FTE_Name
它有 2 个别名和 2 个dots
混淆在最后两行
REP_FTEReport_secured.GPN, REP_FTEReport_secured.Country AS FTE_Country,
REP_FTEReport_secured.Country AS FTE_Country.EmployeeName AS FTE_Name
让我们先让它们更具可读性
REP_FTEReport_secured.GPN,
REP_FTEReport_secured.Country AS FTE_Country,
REP_FTEReport_secured.Country AS FTE_Country.EmployeeName AS FTE_Name
现在你可以看到你最后有问题。
你可能需要这个
REP_FTEReport_secured.GPN,
REP_FTEReport_secured.Country AS FTE_Country,
REP_FTEReport_secured.EmployeeName AS FTE_Name
如您所见,只需使查询更具可读性,在这种情况下,每行只放置一个字段,而不是 2,错误立即变得可见
推荐阅读
- python - 如何从 python 中的 1 个问题添加到 2 个列表
- python - 用于黑盒功能的 MILP 求解器
- elasticsearch - 更改 Filebeat 配置以添加字段
- python - 需要帮助集成 Flask 和 Stripe
- reactjs - 不需要在 CRA 中加载图像
- c - 为什么我的 map-reduce 文件读取代码出现分段错误?
- javascript - 如何将 API 数据放入全局数组?
- android - 第一次运行时出现 Flutter 和 Firebase 问题
- swig - 即时处理后流程不会终止
- python - 在 Python 中使用 selenium webdriver 导航 Kijiji 时遇到空白屏幕