A table, A field: user ID, ID card, card type, the issuing time,
Card types have a special card and card, randomly distributed to the user;
How to evaluate a users receive the interval of two special card, how many photos have you received an ordinary card?

CodePudding user response:

Try the following, never test

 
WITH CTE 
AS 
(user ID, SELECT a. a. issuing time AS BEGIN_DATE, b. the issuing time AS END_DATE 
FROM the TABLE A 
CROSS the APPLY (SELECT TOP 1 card time FROM the TABLE WHERE the user ID=a. user ID AND the card type='special card AND issuing time & gt; A. the issuing time ORDER BY issuing time) AS B 
WHERE A. Card type='special card') 

SELECT a user ID, b. B.B EGIN_DATE, COUNT (*) 
FROM the TABLE A 
JOIN CTE B ON a. user ID=b. user ID AND a. issuing time BETWEEN B.B EGIN_DATE AND B.E ND_DATE AND a. card types='ordinary' 
GROUP BY b. user ID, B.B EGIN_DATE 

CodePudding user response:

With a single statement of the script will be very complicated, but also a little blurred, the requirements of the building Lord such as no clear user may receive special KaZhang number,
Probably approach is as follows:
1. The extraction of all special card access time
2. Before extracting n - 1 row data, set the card access time to start time
3. The next line extraction has been extracted data line get time, set to end time, this time should be n - 1 row data
4. Then for each line, get all classic card access number calculation

CodePudding user response:

refer to the second floor shoppo0505 response:

with a single statement of script is very complicated, but also a little blurred, the requirements of the building Lord such as no clear user may receive special KaZhang number,
Probably approach is as follows:
1. The extraction of all special card access time
2. Before extracting n - 1 row data, set the card access time to start time
3. The next line extraction has been extracted data line get time, set to end time, this time should be n - 1 row data
4. Then for each line, calculate all classic card access number

refer to the second floor shoppo0505 response:

with a single statement of script is very complicated, but also a little blurred, the requirements of the building Lord such as no clear user may receive special KaZhang number,
Probably approach is as follows:
1. The extraction of all special card access time
2. Before extracting n - 1 row data, set the card access time to start time
3. The next line extraction has been extracted data line get time, set to end time, this time should be n - 1 row data
4. Then for each line, calculate all classic card access number

Thank the boss to reply, but this way of thinking in the SQL should be impassability, feeling a little flaw, I study the brother on the ground floor of the reply, I think may be right

CodePudding user response:

Can use row_number function according to user's id, id card to group, according to the issuing time sorting, comparing with the line number, such as row=row + 1,