如何不使用IF EXISTS实现日期条件下的订单数据筛选需求

Alright, let's tackle this problem. We need to fetch either one row per mobile number from the Order table when the current date falls within the date range defined in the settings table, or all rows if it's outside that range—without using IF EXISTS.

First, let's recap the sample tables and data we're working with:

Sample Table Setup #

DECLARE @settings TABLE(id INT, Gateway NVARCHAR(80), startDateTime DATETIME, endDateTime DATETIME);
INSERT INTO @settings VALUES (1,'com1','2018-05-1 00:00:00.000','2018-05-30 23:59:59.000');

DECLARE @Order TABLE(id INT, Gateway NVARCHAR(80), mobile NVARCHAR(80), [Date] DATETIME);
INSERT INTO @Order VALUES 
(1,'com1','222088','2018-05-17 10:15:54.047'),
(2,'com1','212409','2018-05-17 11:20:22.047'),
(3,'com1','227263','2018-05-17 12:53:42.047'),
(4,'com1','222088','2018-05-17 13:48:32.047'),
(5,'com1','212409','2018-05-17 14:43:12.047'),
(6,'com1','212409','2018-05-17 15:27:11.047'),
(7,'com1','222088','2018-05-18 15:15:54.047');

Approach 1: CASE Statement with ROW_NUMBER() #

This method uses a CASE inside the PARTITION BY clause of ROW_NUMBER() to dynamically adjust how we group rows. When we're in the date range, we group by mobile to get one row per number; when outside, we group by a unique value (like id) so every row gets a row number of 1.

SELECT o.id, o.Gateway, o.mobile, o.[Date]
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(
            PARTITION BY 
                CASE WHEN EXISTS(SELECT 1 FROM @settings WHERE GETDATE() BETWEEN startDateTime AND endDateTime)
                     THEN mobile
                     ELSE CAST(id AS NVARCHAR(20)) -- Unique per row to retain all records
                END
            ORDER BY [Date] DESC -- Adjust this to pick which row to keep per mobile (e.g., latest here)
        ) AS rn
    FROM @Order o
) AS ranked
WHERE rn = 1;

Note: Adjust the ORDER BY inside ROW_NUMBER() if you want to pick a different row per mobile (like the earliest entry instead of the latest).

Approach 2: CTE for Range Status + Filter Logic #

We first create a CTE to check if we're inside the settings date range, then cross-join that status with the Order table. We use the status to decide whether to filter for one row per mobile or keep everything.

WITH RangeStatus AS (
    SELECT 
        CASE WHEN GETDATE() BETWEEN startDateTime AND endDateTime THEN 1 ELSE 0 END AS IsInRange
    FROM @settings
)
SELECT o.id, o.Gateway, o.mobile, o.[Date]
FROM @Order o
CROSS JOIN RangeStatus rs
WHERE 
    (rs.IsInRange = 1 AND 
     ROW_NUMBER() OVER(PARTITION BY o.mobile ORDER BY o.[Date] DESC) = 1)
    OR rs.IsInRange = 0
ORDER BY o.mobile, o.[Date];

This approach keeps the range check separate, making the logic easier to read and modify later.

Approach 3: TOP 1 WITH TIES + Dynamic Partitioning #

Leverage the TOP 1 WITH TIES feature, which returns all rows that have the same row number as the top row. We use the same dynamic partitioning trick as Approach 1 to get either one row per mobile or all rows.

SELECT TOP 1 WITH TIES
    o.id, o.Gateway, o.mobile, o.[Date]
FROM @Order o
ORDER BY 
    ROW_NUMBER() OVER(
        PARTITION BY 
            CASE WHEN EXISTS(SELECT 1 FROM @settings WHERE GETDATE() BETWEEN startDateTime AND endDateTime)
                 THEN mobile
                 ELSE o.id
            END
        ORDER BY o.[Date] DESC
    );

This is the most concise method—great if you prefer shorter, more compact code.

All three approaches avoid using IF EXISTS and will return the expected results: one row per mobile when in the date range, all rows when outside.

内容的提问来源于stack exchange，提问作者forme hasan