SQL problem-CodePudding

The class date
A 2020/1/31
A 2020/2/29
A 2020/3/31
B 2020/4/28
A 2020/5/28
A 2020/6/28

Integrated into
The class date
A 2020/3/31
B 2020/4/28
A 2020/6/28

, what is the good way to

CodePudding user response:

SELECT the CLASS, MAX (DATE) AS the DATE FROM the TABLE GROUP BY CLASS

CodePudding user response:

reference 1st floor RINK_1 response:

SELECT CLASS, MAX (DATE) AS the DATE FROM the TABLE GROUP BY CLASS

This is the purpose of not only divided into two segments I want three

CodePudding user response:

reference weixin_41330591 reply: 3/f

Quote: refer to 1st floor RINK_1 response:
SELECT CLASS, MAX (DATE) AS the DATE FROM the TABLE GROUP BY CLASS

This is the purpose of not only divided into two segments I want three

 

The CREATE TABLE # T 
(CLASS VARCHAR (10), 
DT DATE) 

INSERT INTO # T 
SELECT the 'A', '2020/1/31' UNION ALL 
SELECT the 'A', '2020/2/29' UNION ALL 
SELECT the 'A', '2020/3/31' UNION ALL 
SELECT 'B', '2020/4/28' UNION ALL 
SELECT the 'A', '2020/5/28' UNION ALL 
SELECT the 'A', '2020/6/28' 

WITH CTE 
AS 
(SELECT *, ROW_NUMBER () OVER (ORDER BY DT) AS an RN FROM # T) 

SELECT the CLASS, DT 
The FROM 
(SELECT * FROM CTE A 
WHERE the EXISTS (SELECT 1 FROM CTE WHERE A.C LASS<> CLASS AND A.R N=RN 1) 
UNION ALL 
SELECT * FROM CTE A 
WHERE NOT the EXISTS (SELECT 1 FROM CTE WHERE RN> A.R N)) AS A 
The ORDER BY RN

CodePudding user response:

RINK_1

reference 4 floor response:

Quote: refer to the third floor weixin_41330591 response:

Quote: refer to 1st floor RINK_1 response:
SELECT CLASS, MAX (DATE) AS the DATE FROM the TABLE GROUP BY CLASS

This is the purpose of not only divided into two segments I want three

 

The CREATE TABLE # T 
(CLASS VARCHAR (10), 
DT DATE) 

INSERT INTO # T 
SELECT the 'A', '2020/1/31' UNION ALL 
SELECT the 'A', '2020/2/29' UNION ALL 
SELECT the 'A', '2020/3/31' UNION ALL 
SELECT 'B', '2020/4/28' UNION ALL 
SELECT the 'A', '2020/5/28' UNION ALL 
SELECT the 'A', '2020/6/28' 

WITH CTE 
AS 
(SELECT *, ROW_NUMBER () OVER (ORDER BY DT) AS an RN FROM # T) 

SELECT the CLASS, DT 
The FROM 
(SELECT * FROM CTE A 
WHERE the EXISTS (SELECT 1 FROM CTE WHERE A.C LASS<> CLASS AND A.R N=RN 1) 
UNION ALL 
SELECT * FROM CTE A 
WHERE NOT the EXISTS (SELECT 1 FROM CTE WHERE RN> A.R N)) AS A 
The ORDER BY RN

RINK_1

reference 4 floor response:

Quote: refer to the third floor weixin_41330591 response:

Quote: refer to 1st floor RINK_1 response:
SELECT CLASS, MAX (DATE) AS the DATE FROM the TABLE GROUP BY CLASS

This is the purpose of not only divided into two segments I want three

 

The CREATE TABLE # T 
(CLASS VARCHAR (10), 
DT DATE) 

INSERT INTO # T 
SELECT the 'A', '2020/1/31' UNION ALL 
SELECT the 'A', '2020/2/29' UNION ALL 
SELECT the 'A', '2020/3/31' UNION ALL 
SELECT 'B', '2020/4/28' UNION ALL 
SELECT the 'A', '2020/5/28' UNION ALL 
SELECT the 'A', '2020/6/28' 

WITH CTE 
AS 
(SELECT *, ROW_NUMBER () OVER (ORDER BY DT) AS an RN FROM # T) 

SELECT the CLASS, DT 
The FROM 
(SELECT * FROM CTE A 
WHERE the EXISTS (SELECT 1 FROM CTE WHERE A.C LASS<> CLASS AND A.R N=RN 1) 
UNION ALL 
SELECT * FROM CTE A 
WHERE NOT the EXISTS (SELECT 1 FROM CTE WHERE RN> A.R N)) AS A 
The ORDER BY RN

Thank you bosses want to to ask how to put the minimum of time in the same line
Namely become
The class date_end date_bgn
A 2020/3/31 2020/1/31
B 2020/4/28 2020/4/28
A 2020/6/28 2020/5/28