(table fields)
ID NAME START_DATA
1 aaa 2019-01-01
2 aaa 2019-01-02
3 aaa 2019-01-03
4 BBB 2019-01-03


DISTINCT query within a certain period of time, not seen in other time data

Example: (DISTINCT query data between 2019-01-02 ~ 2019-01-02, under normal circumstances can detect aaa, BBB
But aaa also has appeared in the 2019-01-01, and so should not be queried out)

CodePudding user response:

Didn't quite understand what you mean

Is only the date specified in the content of the list, with the specified date of items not listed?

For example, only the 2019-01-02 ~ 2019-01-02, BBB, because the aaa is not within the specified date so don't?

 with t as (
Select 1 as ID, 'aaa' as the NAME, the convert (date, '2019-1-1') as START_DATA 
Union all select 2, 'aaa', '2019-1-2' 
Union all select 3, 'aaa', '2019-1-3' 
Union all select 4, 'BBB', '2019-1-3' 
) 
Select * 
The from t a 
Where START_DATA between '2019-1-2' and '2019-1-3 s' 
And not the exists (
Select the top 1 1 
The from t 
Where NAME=a.N AME and START_DATA not between '2019-1-2' and '2019-1-3 s' 
) 

It is ok to additional constraints

CodePudding user response:

Don't want to write SQL
Roughly ideas

To counter should be the fastest or not a given data and not within the scope of the in query out within the specified range

CodePudding user response:

 

The CREATE TABLE # T 
(ID INT IDENTITY (1, 1), 
The NAME VARCHAR (10), 
The START_DATE DATE) 

INSERT INTO # T 
SELECT 'AAA', '2019-01-01' UNION ALL 
SELECT 'AAA', '2019-01-02' UNION ALL 
SELECT 'AAA', '2019-01-03' UNION ALL 
SELECT 'BBB', '2019-01-03' 


DECLARE @ the START_DATE DATE 
DECLARE @ END_DATE DATE 

The SET @ the START_DATE='2019-01-01' 
The SET @ END_DATE='2019-01-03' 

SELECT * FROM # T A 
WHERE the START_DATE BETWEEN @ the START_DATE AND @ END_DATE 
AND NOT the EXISTS (SELECT 1 FROM # T WHERE NAME=A.N AME AND the START_DATE NOT BETWEEN @ the START_DATE AND @ END_DATE)