My SQL query: Based on the ID and Name column, get the the Number column data-CodePudding

for eg: if ID = 1000097 and name = 'case_create' and Number = 12 then the output of number column should be 12 for other Name column types also (case_clip_add, case_update, case_fetch, case_share)

Input:

   ID       Name          Number
 1000097  case_create      12
 1000097  case_clip_add    13
 1000097  case_update      14
 1000097  case_fetch       15
 1000098  case_share       13
 1000099  case_fetch       14

OutPut:

   ID       Name          Number
 1000097  case_create      12
 1000097  case_clip_add    12
 1000097  case_update      12
 1000097  case_fetch       12
 1000098  case_share       13
 1000099  case_fetch       14

CodePudding user response：

A join to a record for an id with a name of case_create with coalesce.

DROP TABLE IF EXISTS T;

create table t
 (ID int,      Name    varchar(20),      Number int);
 insert into t values
 (1000097 , 'case_create'   ,   12),
 (1000097 , 'case_clip_add' ,   13),
 (1000097 , 'case_update'   ,   14),
 (1000097 , 'case_fetch'    ,   15),
 (1000098 , 'case_share'    ,   13),
 (1000099 , 'case_fetch'    ,   14);
 
select t.id,t.name,
         coalesce(s.number,t.number) number
from t
left join (select id,number from t where name = 'case_create') s
             on s.id = t.id
order by t.id;

    

 --------- --------------- -------- 
| id      | name          | number |
 --------- --------------- -------- 
| 1000097 | case_create   |     12 |
| 1000097 | case_clip_add |     12 |
| 1000097 | case_update   |     12 |
| 1000097 | case_fetch    |     12 |
| 1000098 | case_share    |     13 |
| 1000099 | case_fetch    |     14 |
 --------- --------------- -------- 
6 rows in set (0.001 sec)