Using SQL Server 2005.
数据在 2 个单独的表中,我只被授予写入权限。
数据如下所示:
DateTime1 | DateTime2
-----------------------
2012-06-01 | 2012-06-01
2012-06-02 | 2012-06-02
2012-06-04 | 2012-06-05
2012-06-02 | NULL
NULL | 2012-06-05
2012-06-04 | 2012-06-05
NULL | NULL
我正在尝试做的是能够计算 DateTime1 和 DateTime2 包含值、DateTime1 包含日期且 DateTime2 为 NULL、DateTime1 为 NULL 且 DateTime2 包含值的值。
总的来说,我试图避免日期时间 1 为空,日期时间 2 为空。
我的 where 语句如下所示:
Where (DateTime1 is not null or DateTime2 is not null)
唯一的问题是它仍然显示两者都是空值的位置。有谁知道为什么会发生这种情况或如何解决它?
谢谢
编辑@Lamak请求的完整查询
;With [CTE] As (
Select
TH.ID
,AMT
,Reason
,EffDate
,DateReq
,CS_ID
,ROW_NUMBER()
Over (Partition By ID Order By [pthPrimeKey] Desc) as [RN]
From
DateTime1Table as [MC] (nolock)
Left Join History as [TH] (nolock) on [TH].[ID] = [MC].[ID]
Left Join Trans as [SUB] (nolock) on [SUB].TransactionReasonCode = [TH].Reason
Left Join Renew as [RM] (nolock) on [MC].ID = [RM].ID
Where
([MC].[DateTime1] is not null or [RM].[DateTime2] is not null)
And [PostingDate] = DATEADD(dd, datediff(dd, 1, GetDate()),0)
)
SELECT
[ID]
,[AMT] as [Earned]
,[Reason] as [Reason]
,[EffDate] as [Eff]
,[DateReq] as [Date_Cancel_Req]
,[pthUserId_Number] as [CSR]
FROM [CTE]
Where RN <= 1
将允许包含行,如果
- 只有日期时间 1 具有值
- 只有日期时间 2 具有值
- 两者都有值
它将排除两个值均为 NULL 的行。这就是你所追求的吗?(我试图遵循对话,但迷路了,希望你能有一个更简单的示例数据重现 - 我认为 CTE 和所有其他联接和逻辑确实消除了您遇到的实际问题。
WHERE COALESCE([MC].[DateTime1], [RM].[DateTime2]) IS NOT NULL
但是,由于您正在执行LEFT OUTER JOIN
,这可能属于[RM]
的ON
子句,而不是WHERE
。否则,您将不知道是否因为匹配行中的值为 NULL 或因为没有匹配的行而排除了行。也许没关系,只是想我会提到它。
编辑
当然,该条款提供的结果与...
WHERE ([MC].[DateTime1] is not null or [RM].[DateTime2] is not null)
想要证据吗?
DECLARE @a TABLE(id INT, DateTime1 DATETIME);
DECLARE @b TABLE(id INT, DateTime2 DATETIME);
INSERT @a SELECT 1, '20120602' ; INSERT @b SELECT 1, NULL;
INSERT @a SELECT 2, NULL ; INSERT @b SELECT 2, '20120605';
INSERT @a SELECT 3, '20120604' ; INSERT @b SELECT 3, '20120605';
INSERT @a SELECT 4, NULL ; INSERT @b SELECT 4, NULL;
INSERT @a SELECT 5, '20120602' ; INSERT @b SELECT 9, NULL;
INSERT @a SELECT 6, NULL ; INSERT @b SELECT 10, '20120605';
INSERT @a SELECT 7, '20120604' ; INSERT @b SELECT 11, '20120605';
INSERT @a SELECT 8, NULL ; INSERT @b SELECT 12, NULL;
SELECT * FROM @a AS a LEFT OUTER JOIN @b AS b
ON a.id = b.id
WHERE COALESCE(a.DateTime1, b.DateTime2) IS NOT NULL;
SELECT * FROM @a AS a LEFT OUTER JOIN @b AS b
ON a.id = b.id
WHERE a.DateTime1 IS NOT NULL OR b.DateTime2 IS NOT NULL;
这两个查询都会产生:
id DateTime1 id DateTime2
-- ---------- ---- ----------
1 2012-06-02 1 NULL -- because left is not null
2 NULL 2 2012-06-05 -- because right is not null
3 2012-06-04 3 2012-06-05 -- because neither is null
5 2012-06-02 NULL NULL -- because of no match
7 2012-06-04 NULL NULL -- because of no match
因此,正如我在评论中建议的那样,如果您没有看到预期的行,则需要查看查询的其他部分。如果您提供样本数据和期望的结果,我们可以尝试帮助您缩小范围。事实上,我认为我们对您的架构和数据了解不足,无法确定问题所在。