基于选择子查询列的SQL条件

发布于 2025-01-21 03:21:22 字数 1177 浏览 2 评论 0原文

使用MSSQL，我想根据选择子查询应用条件。我该如何最佳地做到这一点？

这是我的初始查询

SELECT tblA.Col, tblB.firstdate, tblC.CCol
FROM tblA
OUTER APPLY (SELECT TOP 1 t.PlacementDate firstdate
             FROM ( SELECT PlacementDate from TAB1 WHERE TAB1.Col3 = tblA.ACol UNION SELECT PlacementDate FROM TAB2 WHERE TAB2.Col3 = tblA.ACol) t 
             ORDER BY PlacementDate) tblB
INNER JOIN tblC on tblC.ColNameC = tblA.ColNameA

，我想添加使用表t（联合结果）的条件以选择当今的数据：

WHERE CAST(t.PlacementDate AS DATE) = CAST(getdate() AS DATE)

因此，最终代码是：

SELECT tblA.Col, tblB.firstdate, tblC.CCol
FROM tblA
OUTER APPLY (SELECT TOP 1 t.PlacementDate firstdate
             FROM ( SELECT PlacementDate from TAB1 WHERE TAB1.Col3 = tblA.ACol UNION SELECT PlacementDate FROM TAB2 WHERE TAB2.Col3 = tblA.ACol) t 
             ORDER BY PlacementDate) tblB
INNER JOIN tblC on tblC.ColNameC = tblA.ColNameA
WHERE CAST(t.PlacementDate AS DATE) = CAST(getdate() AS DATE)

但是我无法参考列> placementdate来自t，我有此错误消息出现：

The multi part identifier 't.PlacementDate' could not be bound

原文

Using MSSQL, I want to apply a condition based on a select subquery. How can I do it optimally ?

Here is my initial query

SELECT tblA.Col, tblB.firstdate, tblC.CCol
FROM tblA
OUTER APPLY (SELECT TOP 1 t.PlacementDate firstdate
             FROM ( SELECT PlacementDate from TAB1 WHERE TAB1.Col3 = tblA.ACol UNION SELECT PlacementDate FROM TAB2 WHERE TAB2.Col3 = tblA.ACol) t 
             ORDER BY PlacementDate) tblB
INNER JOIN tblC on tblC.ColNameC = tblA.ColNameA

And I want to add WHERE condition using table t ( UNION result ) in order to select today's data :

WHERE CAST(t.PlacementDate AS DATE) = CAST(getdate() AS DATE)

So the final code is :

SELECT tblA.Col, tblB.firstdate, tblC.CCol
FROM tblA
OUTER APPLY (SELECT TOP 1 t.PlacementDate firstdate
             FROM ( SELECT PlacementDate from TAB1 WHERE TAB1.Col3 = tblA.ACol UNION SELECT PlacementDate FROM TAB2 WHERE TAB2.Col3 = tblA.ACol) t 
             ORDER BY PlacementDate) tblB
INNER JOIN tblC on tblC.ColNameC = tblA.ColNameA
WHERE CAST(t.PlacementDate AS DATE) = CAST(getdate() AS DATE)

But I can't reference the column PlacementDate from t and I have this error message appearing :

The multi part identifier 't.PlacementDate' could not be bound

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

纸短情长 2025-01-28 03:21:22

用这个

SELECT tblA.Col, tblB.PlacementDate, tblC.CCol
FROM tblA
OUTER APPLY (SELECT TOP 1 PlacementDate 
             FROM 
             ( SELECT PlacementDate from TAB1 WHERE TAB1.Col3 = tblA.ACol 
               UNION 
               SELECT PlacementDate FROM TAB2 WHERE TAB2.Col3 = tblA.ACol
             ) t 
             WHERE CAST(t.PlacementDate AS DATE) = CAST(getdate() AS DATE)
             ORDER BY PlacementDate) tblB
INNER JOIN tblC on tblC.ColNameC = tblA.ColNameA

use this

SELECT tblA.Col, tblB.PlacementDate, tblC.CCol
FROM tblA
OUTER APPLY (SELECT TOP 1 PlacementDate 
             FROM 
             ( SELECT PlacementDate from TAB1 WHERE TAB1.Col3 = tblA.ACol 
               UNION 
               SELECT PlacementDate FROM TAB2 WHERE TAB2.Col3 = tblA.ACol
             ) t 
             WHERE CAST(t.PlacementDate AS DATE) = CAST(getdate() AS DATE)
             ORDER BY PlacementDate) tblB
INNER JOIN tblC on tblC.ColNameC = tblA.ColNameA

回复收藏 0 原文

~没有更多了~