Advertisement
kura2yamato

pivot waktu kerja

May 5th, 2022
973
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #### tested : https://www.db-fiddle.com/f/p98Usvarz7i7HrD2WiEnEX/3
  2.  
  3. #####SKEMA##################
  4.  
  5. CREATE TABLE `my_project` (
  6.   `id` int(11) NOT NULL,
  7.   `user_id` int(11) NOT NULL,
  8.   `proj_id` int(11) NOT NULL,
  9.   `hours` int(11) NOT NULL,
  10.   `month` date NOT NULL
  11. );
  12.  
  13. INSERT INTO `my_project` (`id`, `user_id`, `proj_id`, `hours`, `month`) VALUES
  14. (1, 20, 11, 8, '2022-05-01'),
  15. (2, 20, 22, 8, '2022-05-01'),
  16. (3, 21, 31, 8, '2022-05-01'),
  17. (4, 20, 22, 8, '2022-05-01');
  18.  
  19. ######## QUERY
  20. select
  21.     m1.user_id `user id`,
  22.     substr( GROUP_CONCAT(m2.proj_id),1,6)-100000 `primary`, if( substr( GROUP_CONCAT(m2.proj_id),8,6) - 100000 < 0, '-',
  23.  
  24. substr(GROUP_CONCAT(m2.proj_id),8,6) - 100000 ) as `second`
  25.  
  26. from
  27. (
  28.     select
  29.         m1a.user_id from my_project m1a group by m1a.user_id
  30. ) as m1
  31.     left join
  32. (  
  33.     select
  34.         m2a.user_id, sum(m2a.hours) c,   (m2a.proj_id+100000) proj_id  FROM
  35.     my_project m2a
  36.     group by
  37.     m2a.proj_id,m2a.user_id order by m2a.user_id, c desc
  38.  ) m2
  39.  on m1.user_id = m2.user_id
  40.  group by user_id asc;
  41. ######################
  42. explain
  43. select
  44. m1.user_id,
  45. substr(GROUP_CONCAT(m2.proj_id),1,6)-100000 x1,
  46. if(substr(GROUP_CONCAT(m2.proj_id),8,6)-100000<0,'',substr(GROUP_CONCAT(m2.proj_id),8,6)-100000) x2
  47. from
  48. (select m1a.user_id from my_project m1a group by m1a.user_id) m1
  49. left join
  50. (select m2a.user_id, sum(m2a.hours) c,   (m2a.proj_id+100000) proj_id  FROM
  51.     my_project m2a
  52.     group by m2a.proj_id,m2a.user_id order by c desc
  53.  ) m2 on m1.user_id = m2.user_id
  54.  group by user_id asc;
  55.  
  56. ########################
  57. SELECT sum(hours) c
  58.     ,proj_id
  59.     ,user_id
  60. FROM `my_project`
  61. GROUP BY proj_id
  62.     ,user_id
  63. ORDER BY user_id ASC
  64.     ,c DESC;
  65. #####################
  66. select *
  67. from
  68. (select m1a.user_id from my_project m1a group by m1a.user_id) m1
  69. left join
  70. (select m2a.user_id, sum(m2a.hours) c,  m2a.proj_id  FROM
  71.     my_project m2a
  72.     group by m2a.proj_id,m2a.user_id order by c desc
  73.  ) m2 on m1.user_id = m2.user_id
  74. ;
  75. ###################
  76.  select m1.user_id, GROUP_CONCAT(m2.proj_id) x
  77. from
  78. (select m1a.user_id from my_project m1a group by m1a.user_id) m1
  79. left join
  80. (select m2a.user_id, sum(m2a.hours) c,  m2a.proj_id  FROM
  81.     my_project m2a
  82.     group by m2a.proj_id,m2a.user_id order by c desc
  83.  ) m2 on m1.user_id = m2.user_id
  84.  group by user_id asc;
  85. ##################
  86. select m1.user_id, GROUP_CONCAT(m2.proj_id) x
  87. from
  88. (select m1a.user_id from my_project m1a group by m1a.user_id) m1
  89. left join
  90. (select m2a.user_id, sum(m2a.hours) c,   (m2a.proj_id+100000) proj_id  FROM
  91.     my_project m2a
  92.     group by m2a.proj_id,m2a.user_id order by c desc
  93.  ) m2 on m1.user_id = m2.user_id
  94.  group by user_id asc;
  95.  
Advertisement
RAW Paste Data Copied
Advertisement