Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #### tested : https://www.db-fiddle.com/f/p98Usvarz7i7HrD2WiEnEX/3
- #####SKEMA##################
- CREATE TABLE `my_project` (
- `id` int(11) NOT NULL,
- `user_id` int(11) NOT NULL,
- `proj_id` int(11) NOT NULL,
- `hours` int(11) NOT NULL,
- `month` date NOT NULL
- );
- INSERT INTO `my_project` (`id`, `user_id`, `proj_id`, `hours`, `month`) VALUES
- (1, 20, 11, 8, '2022-05-01'),
- (2, 20, 22, 8, '2022-05-01'),
- (3, 21, 31, 8, '2022-05-01'),
- (4, 20, 22, 8, '2022-05-01');
- ######## QUERY
- select
- m1.user_id `user id`,
- substr( GROUP_CONCAT(m2.proj_id),1,6)-100000 `primary`, if( substr( GROUP_CONCAT(m2.proj_id),8,6) - 100000 < 0, '-',
- substr(GROUP_CONCAT(m2.proj_id),8,6) - 100000 ) as `second`
- from
- (
- select
- m1a.user_id from my_project m1a group by m1a.user_id
- ) as m1
- left join
- (
- select
- m2a.user_id, sum(m2a.hours) c, (m2a.proj_id+100000) proj_id FROM
- my_project m2a
- group by
- m2a.proj_id,m2a.user_id order by m2a.user_id, c desc
- ) m2
- on m1.user_id = m2.user_id
- group by user_id asc;
- ######################
- explain
- select
- m1.user_id,
- substr(GROUP_CONCAT(m2.proj_id),1,6)-100000 x1,
- if(substr(GROUP_CONCAT(m2.proj_id),8,6)-100000<0,'',substr(GROUP_CONCAT(m2.proj_id),8,6)-100000) x2
- from
- (select m1a.user_id from my_project m1a group by m1a.user_id) m1
- left join
- (select m2a.user_id, sum(m2a.hours) c, (m2a.proj_id+100000) proj_id FROM
- my_project m2a
- group by m2a.proj_id,m2a.user_id order by c desc
- ) m2 on m1.user_id = m2.user_id
- group by user_id asc;
- ########################
- SELECT sum(hours) c
- ,proj_id
- ,user_id
- FROM `my_project`
- GROUP BY proj_id
- ,user_id
- ORDER BY user_id ASC
- ,c DESC;
- #####################
- select *
- from
- (select m1a.user_id from my_project m1a group by m1a.user_id) m1
- left join
- (select m2a.user_id, sum(m2a.hours) c, m2a.proj_id FROM
- my_project m2a
- group by m2a.proj_id,m2a.user_id order by c desc
- ) m2 on m1.user_id = m2.user_id
- ;
- ###################
- select m1.user_id, GROUP_CONCAT(m2.proj_id) x
- from
- (select m1a.user_id from my_project m1a group by m1a.user_id) m1
- left join
- (select m2a.user_id, sum(m2a.hours) c, m2a.proj_id FROM
- my_project m2a
- group by m2a.proj_id,m2a.user_id order by c desc
- ) m2 on m1.user_id = m2.user_id
- group by user_id asc;
- ##################
- select m1.user_id, GROUP_CONCAT(m2.proj_id) x
- from
- (select m1a.user_id from my_project m1a group by m1a.user_id) m1
- left join
- (select m2a.user_id, sum(m2a.hours) c, (m2a.proj_id+100000) proj_id FROM
- my_project m2a
- group by m2a.proj_id,m2a.user_id order by c desc
- ) m2 on m1.user_id = m2.user_id
- group by user_id asc;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement