SQL query with requires recursive logic

Question

I have a MySQL table and it has 2 columns which record users' login info:

 User_id  Device_id
     a       123
     b       123
     b       321
     d       321
     e       aaa
     f       ccc
     g       cba
     h       aaa
     h       ccc
     i       cba
     i       aaa

Now I want to add a column Group: if user_id are logined by the same group of device, then put them together. For instance, as shown below, user account a and b are logined on device 123, so a and b are in the same group. While user account b is also logined on device 321, then all accounts logined by 321 should join this group :

 User_id  Device_id    Group 
     a       123         1
     b       123         1
     b       321         1
     d       321         1
     e       aaa         2
     f       ccc         2
     g       cba         2
     h       aaa         2
     h       ccc         2
     i       cba         2
     i       aaa         2

This cannot be simply dealed with group by so how to use SQL to express column Group?

Note that this problem requires recursive logic. What version of MySQL are using? — Strawberry
– Strawberry, Commented Sep 16, 2019 at 7:30
@Serg Hi, thanks for your reply. I tried to detail my description. Hope is helps.. — user2894829
– user2894829, Commented Sep 16, 2019 at 7:37
Hi @Strawberry Actually I can migrate this MySQL table to Hadoop platform and deal it with Hive and query grammar there is much more flexible. So difference between MySQL versions is actually not a big deal. You can use the version you like most :-) — user2894829
– user2894829, Commented Sep 16, 2019 at 7:53

Devanathan M · Accepted Answer · 2019-09-17 12:55:05Z

1

You can use the below queries,

// For future insert

INSERT INTO tableName VALUES (j, '123', CASE grouping WHEN (SELECT count() FROM tableName tn WHERE tn.Device_id='123')=0 THEN (SELECT MAX(tn.grouping)+1 FROM tableName tn) WHEN (SELECT count() FROM tableName tn WHERE tn.Device_id='123')>0 THEN (SELECT tn.grouping FROM tableName tn WHERE tn.Device_id='123') END );

//Update the existing data

UPDATE tableName set grouping = CASE grouping WHEN (SELECT count() FROM tableName tn WHERE tn.Device_id='123')=0 THEN (SELECT MAX(tn.grouping)+1 FROM tableName tn) WHEN (SELECT count() FROM tableName tn WHERE tn.Device_id='123')>0 THEN (SELECT tn.grouping FROM tableName tn WHERE tn.Device_id='123') END;

edited Sep 17, 2019 at 12:55

answered Sep 17, 2019 at 7:40

Devanathan M

113 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DBX12 Over a year ago

Hello Devanathan M, welcome to StackOverflow! Can you clarify how a MYSQL TRIGGER will help to solve the problem?

Devanathan M Over a year ago

In MySQL, a trigger is a stored program invoked automatically in response to an event such as insert, update, or delete that occurs in the associated table. For example, you can define a trigger that is invoked automatically before a new row is inserted into a table. So as soon as you get an entry in the table you can update the Group column by referring to existing rows.

DBX12 Over a year ago

I know the concepts of a MySQL trigger. The point I was framing was your answer being broad. A better answer would be something like "Use a mysql trigger on this event and do that"

Collectives™ on Stack Overflow

SQL query with requires recursive logic

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related