Grouping SQL relational query how to implement?-CodePudding

 
The CREATE TABLE # t (
P varchar (5), - name 
Gp VARCHAR (5) - no 
) 
- the first relationship group 
INSERT INTO # t VALUES (' a ', '1') 
INSERT INTO # t VALUES (' b ', '1') 
INSERT INTO # t VALUES (' b ', '2') 
INSERT INTO # t VALUES (' c ', '2') 
INSERT INTO # t VALUES (' c ', '3') 

- the second relationship group 
INSERT INTO # t VALUES (' e ', '4') 
INSERT INTO # t VALUES (' e ', '5') 

- the third relationship group 
INSERT INTO # t VALUES (' f ', '6')

Hope the query results:
A group 1
Group b 1
Group c 1

Group e 2

Group 3 f

CodePudding user response:

Group 1 2 group 3 is how to judge?

CodePudding user response:

The

reference 1/f, February 16 response:

1 group 2 group 3 is how to judge?

See, p field using gp build relationship, ABC is the chain relations, similar to a distant cousin of the relatives is relative, as long as points on a group of relatives

CodePudding user response:

ABC is even type relationship, ef too

CodePudding user response:

refer to February 16 reply: 3/f

ABC is even type relationship, ef too

Ef has nothing to do

CodePudding user response:

 

; With haveRelation 
As (
The Select Amy polumbo as p, 1 as gp 
The From # t a 
Atul gawande at the join # t b on p=b.g p and Amy polumbo & lt;> P. 
Group by Amy polumbo 
), 
NoRelation 
As (
Select p, Max (gp) as gp 
The From (Select * From # t Where p not in (Select p From haveRelation)) a 
Group By p 

) 

Select * From haveRelation 
The union 
Select * From noRelation

CodePudding user response:

references conveniently and 5 floor 1 response:

 

; With haveRelation 
As (
The Select Amy polumbo as p, 1 as gp 
The From # t a 
Atul gawande at the join # t b on p=b.g p and Amy polumbo & lt;> P. 
Group by Amy polumbo 
), 
NoRelation 
As (
Select p, Max (gp) as gp 
The From (Select * From # t Where p not in (Select p From haveRelation)) a 
Group By p 

) 

Select * From haveRelation 
The union 
Select * From noRelation

Wrong, for the following data, wants to DE assigned to a group, you will put the abcde all points a set up,
#

 INSERT INTO t VALUES (' a ', '1') 
INSERT INTO # t VALUES (' b ', '1') 
INSERT INTO # t VALUES (' b ', '2') 
INSERT INTO # t VALUES (' c ', '2') 
INSERT INTO # t VALUES (' c ', '3') 

- the second relationship group 
INSERT INTO # t VALUES (' d ', '4') 
INSERT INTO # t VALUES (' e ', '4') 

- the third relationship group 
INSERT INTO # t VALUES (' f ', '6')

CodePudding user response:

refer to 6th floor jinghun1999 response:

Quote: references of the fifth floor of 1 reply:

 

; With haveRelation 
As (
The Select Amy polumbo as p, 1 as gp 
The From # t a 
Atul gawande at the join # t b on p=b.g p and Amy polumbo & lt;> P. 
Group by Amy polumbo 
), 
NoRelation 
As (
Select p, Max (gp) as gp 
The From (Select * From # t Where p not in (Select p From haveRelation)) a 
Group By p 

) 

Select * From haveRelation 
The union 
Select * From noRelation

Wrong, for the following data, wants to DE assigned to a group, you will put the abcde all points a set up,
#

 INSERT INTO t VALUES (' a ', '1') 
INSERT INTO # t VALUES (' b ', '1') 
INSERT INTO # t VALUES (' b ', '2') 
INSERT INTO # t VALUES (' c ', '2') 
INSERT INTO # t VALUES (' c ', '3') 

- the second relationship group 
INSERT INTO # t VALUES (' d ', '4') 
INSERT INTO # t VALUES (' e ', '4') 

- the third relationship group 
INSERT INTO # t VALUES (' f ', '6')

A bit complicated, so will you into a tree data structure relations recirculation recursive can get the result.

CodePudding user response:

Can not use a group by group

CodePudding user response:

refer to the eighth floor lsy999 response:

can not use a group by grouped?

I also want to know it, but only the recursive feasible?

CodePudding user response:

 

WITH CTE_1 
AS 
(SELECT *, ROW_NUMBER () OVER (ORDER BY 1) (SELECT) AS SEQ FROM # T), 

CTE_2 
AS 
(SELECT A. *, 
The CASE WHEN A.S EQThe WHEN ISNULL (B.S EQ, c.s. EQ) & lt; A.S EQ AND ISNULL (B.S EQ, c.s. EQ) & lt; ISNULL (c.s. EQ, B.S EQ) THEN ISNULL (B.S EQ, c.s. EQ) 
The ELSE ISNULL (ISNULL (c.s. EQ, B.S EQ), 0) END AS PARENT_ID 
The FROM CTE_1 A 
LEFT the JOIN CTE_1 ON Amy polumbo B=p. AND A.S EQ<> B.S EQ 
Be sad P chtistina georgina rossetti.british poetess LEFT JOIN CTE_1 ON al-qeada P=C AND A.S EQ<> C.s. EQ), 

CTE_3 
AS 
(SELECT *, P AS GROUP_ID FROM CTE_2 WHERE PARENT_ID=0 
UNION ALL 
SELECT A. *, B.G ROUP_ID 
The FROM CTE_2 A 
The JOIN CTE_3 ON Amy polumbo ARENT_ID B=B.S EQ) 

The SELECT GROUP_ID, P 
The FROM CTE_3 
GROUP BY GROUP_ID, P