Forum Stats

  • 3,780,536 Users
  • 2,254,407 Discussions
  • 7,879,374 Comments

Discussions

Convert orcle to mysql

0614
0614 Member Posts: 121 Red Ribbon
edited Mar 3, 2017 10:31AM in MySQL Community Space

Hi all

Please convert below query oracle to mysql

with
    test_data ( subj, student, mark ) as (
      select 'English', 'A1', 90 from dual union all
      select 'Science', 'A1', 45 from dual union all
      select 'Maths'  , 'A1', 80 from dual union all
      select 'English', 'A2', 90 from dual union all
      select 'Science', 'A2', 90 from dual union all
      select 'Maths'  , 'A2', 90 from dual union all
      select 'English', 'A3', 50 from dual union all
      select 'Science', 'A3', 42 from dual union all
      select 'Maths'  , 'A3', 40 from dual
    ),
    prep ( subj, student, rnk, t_25, t_75 ) as (
      select subj, student, rank() over (partition by subj order by mark desc),
             round(count(student) over (partition by subj) * 0.25),
             round(count(student) over (partition by subj) * 0.75)
      from   test_data
    )
select student,
      listagg(case when rnk <= t_25 then subj end, ',') within group (order by subj) as top_25,
      listagg(case when rnk >  t_25
                    and rnk <= t_75 then subj end, ',') within group (order by subj) as middle,
      listagg(case when rnk >  t_75 then subj end, ',') within group (order by subj) as bottom_25
from  prep
group by student
;

STUDENT TOP_25                    MIDDLE                    BOTTOM_25
------- ------------------------- ------------------------- -------------------------
A1      English                  Maths,Science
A2      English,Maths,Science
A3                                                          English,Maths,Science

3 rows selected.

Best Answer

  • Barbara Boehmer
    Barbara Boehmer Member Posts: 4,625
    edited Mar 2, 2017 5:08PM Accepted Answer

    The following creates a test_data table, inserts data, displays the data, then displays one level of sub-query at a time for better understanding, then the final query.  If you already have the data in a table, then all you need is the last query.  I tried displaying it in two different ways, but neither seems to align the columns properly.

    mysql> use mysql
    Database changed
    mysql> create table test_data
        ->   (subj     char(8),
        ->    student  char(7),
        ->    mark     integer);
    Query OK, 0 rows affected (0.28 sec)mysql> insert into test_data values ('English', 'A1', 90);
    Query OK, 1 row affected (0.05 sec)mysql> insert into test_data values ('Science', 'A1', 45);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Maths', 'A1', 80);
    Query OK, 1 row affected (0.15 sec)mysql> insert into test_data values ('English', 'A2', 90);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Science', 'A2', 90);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Maths', 'A2', 90);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('English', 'A3', 50);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Science', 'A3', 42);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Maths', 'A3', 40);
    Query OK, 1 row affected (0.04 sec)mysql> select * from test_data order by subj, student;
    +---------+---------+------+
    | subj    | student | mark |
    +---------+---------+------+
    | English | A1      |   90 |
    | English | A2      |   90 |
    | English | A3      |   50 |
    | Maths   | A1      |   80 |
    | Maths   | A2      |   90 |
    | Maths   | A3      |   40 |
    | Science | A1      |   45 |
    | Science | A2      |   90 |
    | Science | A3      |   42 |
    +---------+---------+------+
    9 rows in set (0.00 sec)mysql> select subj,
        ->        group_concat(mark order by mark desc) as mark_set,
        ->        round(count(student)*0.25)            as t_25,
        ->        round(count(student)*0.75)            as t_75
        -> from   test_data
        -> group  by subj;
    +---------+----------+------+------+
    | subj    | mark_set | t_25 | t_75 |
    +---------+----------+------+------+
    | English | 90,90,50 |    1 |    2 |
    | Maths   | 90,80,40 |    1 |    2 |
    | Science | 90,45,42 |    1 |    2 |
    +---------+----------+------+------+
    3 rows in set (0.00 sec)mysql> select t1.subj, t2.student, find_in_set(t2.mark,t1.mark_set) as rnk, t1.t_25, t1.t_75
        -> from   test_data t2,
        ->        (select subj,
        ->                group_concat(mark order by mark desc) as mark_set,
        ->                round(count(student)*0.25)            as t_25,
        ->                round(count(student)*0.75)            as t_75
        ->         from   test_data
        ->         group  by subj) t1
        -> where  t1.subj = t2.subj
        -> order  by t1.subj, t2.student;
    +---------+---------+------+------+------+
    | subj    | student | rnk  | t_25 | t_75 |
    +---------+---------+------+------+------+
    | English | A1      |    1 |    1 |    2 |
    | English | A2      |    1 |    1 |    2 |
    | English | A3      |    3 |    1 |    2 |
    | Maths   | A1      |    2 |    1 |    2 |
    | Maths   | A2      |    1 |    1 |    2 |
    | Maths   | A3      |    3 |    1 |    2 |
    | Science | A1      |    2 |    1 |    2 |
    | Science | A2      |    1 |    1 |    2 |
    | Science | A3      |    3 |    1 |    2 |
    +---------+---------+------+------+------+
    9 rows in set (0.02 sec)mysql>
    mysql> select student,
        ->        group_concat((case when rnk<=t_25 then subj end) order by subj) as top_25,
        ->        group_concat((case when rnk>t_25 and rnk<=t_75 then subj end) order by subj) as middle,
        ->        group_concat((case when rnk>t_75 then subj end) order by subj) as top_75
        -> from   (select t1.subj, t2.student, find_in_set(t2.mark,t1.mark_set) as rnk, t1.t_25, t1.t_75
        ->         from   test_data t2,
        ->                (select subj,
        ->                        group_concat(mark order by mark desc) as mark_set,
        ->                        round(count(student)*0.25)            as t_25,
        ->                        round(count(student)*0.75)            as t_75
        ->                 from   test_data
        ->                 group  by subj) t1
        ->         where  t1.subj = t2.subj) t3
        -> group  by student
        -> order  by student;
    +---------+-----------------------+---------------+-----------------------+
    | student | top_25                | middle        | top_75                |
    +---------+-----------------------+---------------+-----------------------+
    | A1      | English               | Maths,Science | NULL                  |
    | A2      | English,Maths,Science | NULL          | NULL                  |
    | A3      | NULL                  | NULL          | English,Maths,Science |
    +---------+-----------------------+---------------+-----------------------+
    3 rows in set (0.00 sec)MySQL>

    mysql> use mysql
    Database changed
    mysql> create table test_data
        ->  (subj    char(8),
        ->    student  char(7),
        ->    mark    integer);
    Query OK, 0 rows affected (0.28 sec)mysql> insert into test_data values ('English', 'A1', 90);
    Query OK, 1 row affected (0.05 sec)mysql> insert into test_data values ('Science', 'A1', 45);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Maths', 'A1', 80);
    Query OK, 1 row affected (0.15 sec)mysql> insert into test_data values ('English', 'A2', 90);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Science', 'A2', 90);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Maths', 'A2', 90);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('English', 'A3', 50);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Science', 'A3', 42);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Maths', 'A3', 40);
    Query OK, 1 row affected (0.04 sec)mysql> select * from test_data order by subj, student;
    +---------+---------+------+
    | subj    | student | mark |
    +---------+---------+------+
    | English | A1      |  90 |
    | English | A2      |  90 |
    | English | A3      |  50 |
    | Maths  | A1      |  80 |
    | Maths  | A2      |  90 |
    | Maths  | A3      |  40 |
    | Science | A1      |  45 |
    | Science | A2      |  90 |
    | Science | A3      |  42 |
    +---------+---------+------+
    9 rows in set (0.00 sec)mysql> select subj,
        ->        group_concat(mark order by mark desc) as mark_set,
        ->        round(count(student)*0.25)            as t_25,
        ->        round(count(student)*0.75)            as t_75
        -> from  test_data
        -> group  by subj;
    +---------+----------+------+------+
    | subj    | mark_set | t_25 | t_75 |
    +---------+----------+------+------+
    | English | 90,90,50 |    1 |    2 |
    | Maths  | 90,80,40 |    1 |    2 |
    | Science | 90,45,42 |    1 |    2 |
    +---------+----------+------+------+
    3 rows in set (0.00 sec)mysql> select t1.subj, t2.student, find_in_set(t2.mark,t1.mark_set) as rnk, t1.t_25, t1.t_75
        -> from  test_data t2,
        ->        (select subj,
        ->                group_concat(mark order by mark desc) as mark_set,
        ->                round(count(student)*0.25)            as t_25,
        ->                round(count(student)*0.75)            as t_75
        ->        from  test_data
        ->        group  by subj) t1
        -> where  t1.subj = t2.subj
        -> order  by t1.subj, t2.student;
    +---------+---------+------+------+------+
    | subj    | student | rnk  | t_25 | t_75 |
    +---------+---------+------+------+------+
    | English | A1      |    1 |    1 |    2 |
    | English | A2      |    1 |    1 |    2 |
    | English | A3      |    3 |    1 |    2 |
    | Maths  | A1      |    2 |    1 |    2 |
    | Maths  | A2      |    1 |    1 |    2 |
    | Maths  | A3      |    3 |    1 |    2 |
    | Science | A1      |    2 |    1 |    2 |
    | Science | A2      |    1 |    1 |    2 |
    | Science | A3      |    3 |    1 |    2 |
    +---------+---------+------+------+------+
    9 rows in set (0.02 sec)mysql>
    mysql> select student,
        ->        group_concat((case when rnk<=t_25 then subj end) order by subj) as top_25,
        ->        group_concat((case when rnk>t_25 and rnk<=t_75 then subj end) order by subj) as middle,
        ->        group_concat((case when rnk>t_75 then subj end) order by subj) as top_75
        -> from  (select t1.subj, t2.student, find_in_set(t2.mark,t1.mark_set) as rnk, t1.t_25, t1.t_75
        ->        from  test_data t2,
        ->                (select subj,
        ->                        group_concat(mark order by mark desc) as mark_set,
        ->                        round(count(student)*0.25)            as t_25,
        ->                        round(count(student)*0.75)            as t_75
        ->                from  test_data
        ->                group  by subj) t1
        ->        where  t1.subj = t2.subj) t3
        -> group  by student
        -> order  by student;
    +---------+-----------------------+---------------+-----------------------+
    | student | top_25                | middle        | top_75                |
    +---------+-----------------------+---------------+-----------------------+
    | A1      | English              | Maths,Science | NULL                  |
    | A2      | English,Maths,Science | NULL          | NULL                  |
    | A3      | NULL                  | NULL          | English,Maths,Science |
    +---------+-----------------------+---------------+-----------------------+
    3 rows in set (0.00 sec)MySQL>

Answers

  • Barbara Boehmer
    Barbara Boehmer Member Posts: 4,625
    edited Mar 2, 2017 5:08PM Accepted Answer

    The following creates a test_data table, inserts data, displays the data, then displays one level of sub-query at a time for better understanding, then the final query.  If you already have the data in a table, then all you need is the last query.  I tried displaying it in two different ways, but neither seems to align the columns properly.

    mysql> use mysql
    Database changed
    mysql> create table test_data
        ->   (subj     char(8),
        ->    student  char(7),
        ->    mark     integer);
    Query OK, 0 rows affected (0.28 sec)mysql> insert into test_data values ('English', 'A1', 90);
    Query OK, 1 row affected (0.05 sec)mysql> insert into test_data values ('Science', 'A1', 45);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Maths', 'A1', 80);
    Query OK, 1 row affected (0.15 sec)mysql> insert into test_data values ('English', 'A2', 90);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Science', 'A2', 90);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Maths', 'A2', 90);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('English', 'A3', 50);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Science', 'A3', 42);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Maths', 'A3', 40);
    Query OK, 1 row affected (0.04 sec)mysql> select * from test_data order by subj, student;
    +---------+---------+------+
    | subj    | student | mark |
    +---------+---------+------+
    | English | A1      |   90 |
    | English | A2      |   90 |
    | English | A3      |   50 |
    | Maths   | A1      |   80 |
    | Maths   | A2      |   90 |
    | Maths   | A3      |   40 |
    | Science | A1      |   45 |
    | Science | A2      |   90 |
    | Science | A3      |   42 |
    +---------+---------+------+
    9 rows in set (0.00 sec)mysql> select subj,
        ->        group_concat(mark order by mark desc) as mark_set,
        ->        round(count(student)*0.25)            as t_25,
        ->        round(count(student)*0.75)            as t_75
        -> from   test_data
        -> group  by subj;
    +---------+----------+------+------+
    | subj    | mark_set | t_25 | t_75 |
    +---------+----------+------+------+
    | English | 90,90,50 |    1 |    2 |
    | Maths   | 90,80,40 |    1 |    2 |
    | Science | 90,45,42 |    1 |    2 |
    +---------+----------+------+------+
    3 rows in set (0.00 sec)mysql> select t1.subj, t2.student, find_in_set(t2.mark,t1.mark_set) as rnk, t1.t_25, t1.t_75
        -> from   test_data t2,
        ->        (select subj,
        ->                group_concat(mark order by mark desc) as mark_set,
        ->                round(count(student)*0.25)            as t_25,
        ->                round(count(student)*0.75)            as t_75
        ->         from   test_data
        ->         group  by subj) t1
        -> where  t1.subj = t2.subj
        -> order  by t1.subj, t2.student;
    +---------+---------+------+------+------+
    | subj    | student | rnk  | t_25 | t_75 |
    +---------+---------+------+------+------+
    | English | A1      |    1 |    1 |    2 |
    | English | A2      |    1 |    1 |    2 |
    | English | A3      |    3 |    1 |    2 |
    | Maths   | A1      |    2 |    1 |    2 |
    | Maths   | A2      |    1 |    1 |    2 |
    | Maths   | A3      |    3 |    1 |    2 |
    | Science | A1      |    2 |    1 |    2 |
    | Science | A2      |    1 |    1 |    2 |
    | Science | A3      |    3 |    1 |    2 |
    +---------+---------+------+------+------+
    9 rows in set (0.02 sec)mysql>
    mysql> select student,
        ->        group_concat((case when rnk<=t_25 then subj end) order by subj) as top_25,
        ->        group_concat((case when rnk>t_25 and rnk<=t_75 then subj end) order by subj) as middle,
        ->        group_concat((case when rnk>t_75 then subj end) order by subj) as top_75
        -> from   (select t1.subj, t2.student, find_in_set(t2.mark,t1.mark_set) as rnk, t1.t_25, t1.t_75
        ->         from   test_data t2,
        ->                (select subj,
        ->                        group_concat(mark order by mark desc) as mark_set,
        ->                        round(count(student)*0.25)            as t_25,
        ->                        round(count(student)*0.75)            as t_75
        ->                 from   test_data
        ->                 group  by subj) t1
        ->         where  t1.subj = t2.subj) t3
        -> group  by student
        -> order  by student;
    +---------+-----------------------+---------------+-----------------------+
    | student | top_25                | middle        | top_75                |
    +---------+-----------------------+---------------+-----------------------+
    | A1      | English               | Maths,Science | NULL                  |
    | A2      | English,Maths,Science | NULL          | NULL                  |
    | A3      | NULL                  | NULL          | English,Maths,Science |
    +---------+-----------------------+---------------+-----------------------+
    3 rows in set (0.00 sec)MySQL>

    mysql> use mysql
    Database changed
    mysql> create table test_data
        ->  (subj    char(8),
        ->    student  char(7),
        ->    mark    integer);
    Query OK, 0 rows affected (0.28 sec)mysql> insert into test_data values ('English', 'A1', 90);
    Query OK, 1 row affected (0.05 sec)mysql> insert into test_data values ('Science', 'A1', 45);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Maths', 'A1', 80);
    Query OK, 1 row affected (0.15 sec)mysql> insert into test_data values ('English', 'A2', 90);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Science', 'A2', 90);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Maths', 'A2', 90);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('English', 'A3', 50);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Science', 'A3', 42);
    Query OK, 1 row affected (0.04 sec)mysql> insert into test_data values ('Maths', 'A3', 40);
    Query OK, 1 row affected (0.04 sec)mysql> select * from test_data order by subj, student;
    +---------+---------+------+
    | subj    | student | mark |
    +---------+---------+------+
    | English | A1      |  90 |
    | English | A2      |  90 |
    | English | A3      |  50 |
    | Maths  | A1      |  80 |
    | Maths  | A2      |  90 |
    | Maths  | A3      |  40 |
    | Science | A1      |  45 |
    | Science | A2      |  90 |
    | Science | A3      |  42 |
    +---------+---------+------+
    9 rows in set (0.00 sec)mysql> select subj,
        ->        group_concat(mark order by mark desc) as mark_set,
        ->        round(count(student)*0.25)            as t_25,
        ->        round(count(student)*0.75)            as t_75
        -> from  test_data
        -> group  by subj;
    +---------+----------+------+------+
    | subj    | mark_set | t_25 | t_75 |
    +---------+----------+------+------+
    | English | 90,90,50 |    1 |    2 |
    | Maths  | 90,80,40 |    1 |    2 |
    | Science | 90,45,42 |    1 |    2 |
    +---------+----------+------+------+
    3 rows in set (0.00 sec)mysql> select t1.subj, t2.student, find_in_set(t2.mark,t1.mark_set) as rnk, t1.t_25, t1.t_75
        -> from  test_data t2,
        ->        (select subj,
        ->                group_concat(mark order by mark desc) as mark_set,
        ->                round(count(student)*0.25)            as t_25,
        ->                round(count(student)*0.75)            as t_75
        ->        from  test_data
        ->        group  by subj) t1
        -> where  t1.subj = t2.subj
        -> order  by t1.subj, t2.student;
    +---------+---------+------+------+------+
    | subj    | student | rnk  | t_25 | t_75 |
    +---------+---------+------+------+------+
    | English | A1      |    1 |    1 |    2 |
    | English | A2      |    1 |    1 |    2 |
    | English | A3      |    3 |    1 |    2 |
    | Maths  | A1      |    2 |    1 |    2 |
    | Maths  | A2      |    1 |    1 |    2 |
    | Maths  | A3      |    3 |    1 |    2 |
    | Science | A1      |    2 |    1 |    2 |
    | Science | A2      |    1 |    1 |    2 |
    | Science | A3      |    3 |    1 |    2 |
    +---------+---------+------+------+------+
    9 rows in set (0.02 sec)mysql>
    mysql> select student,
        ->        group_concat((case when rnk<=t_25 then subj end) order by subj) as top_25,
        ->        group_concat((case when rnk>t_25 and rnk<=t_75 then subj end) order by subj) as middle,
        ->        group_concat((case when rnk>t_75 then subj end) order by subj) as top_75
        -> from  (select t1.subj, t2.student, find_in_set(t2.mark,t1.mark_set) as rnk, t1.t_25, t1.t_75
        ->        from  test_data t2,
        ->                (select subj,
        ->                        group_concat(mark order by mark desc) as mark_set,
        ->                        round(count(student)*0.25)            as t_25,
        ->                        round(count(student)*0.75)            as t_75
        ->                from  test_data
        ->                group  by subj) t1
        ->        where  t1.subj = t2.subj) t3
        -> group  by student
        -> order  by student;
    +---------+-----------------------+---------------+-----------------------+
    | student | top_25                | middle        | top_75                |
    +---------+-----------------------+---------------+-----------------------+
    | A1      | English              | Maths,Science | NULL                  |
    | A2      | English,Maths,Science | NULL          | NULL                  |
    | A3      | NULL                  | NULL          | English,Maths,Science |
    +---------+-----------------------+---------------+-----------------------+
    3 rows in set (0.00 sec)MySQL>
  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 369 Employee
    edited Mar 3, 2017 10:31AM

    With MySQL 8 you will have CTEs, if you prefer using them.

    Dave Stokes

    MySQL Community Manager