Oracle PHP只显示一次

Question

There are two tables:

$inner_query = 
    "SELECT A.*, ROWNUM AS RN, TO_CHAR(A.last_newsletter_modify, 'DD/MM/YYYY') AS LAST_NEWSLETTER_MODIFY2
     FROM ".$db_schema_name."newsletter_subscription A,
          ".$db_schema_name."newsletter_type B,
          ".$db_schema_name."newsletter_subtyp_profile C
     WHERE A.id_subscription = C.id_subscription AND
           C.id_type = B.id_type
     ORDER BY e_mail";

If I run this query for id_subscription 734 it displays 3 times.

How can to display it just once?

Answer 1

You can use window function row_number to get one row per id_subscription with max (or min id_type if you change the order by clause in the function to order by id_type):

  $inner_query = "SELECT *
                    FROM (
                        SELECT A.*, 
                            ROWNUM AS RN,
                            TO_CHAR(A.last_newsletter_modify, 'DD/MM/YYYY') AS LAST_NEWSLETTER_MODIFY2,
                            row_number() over (
                                partition by C.id_subscription
                                order by C.id_type desc nulls last
                                ) as seqnum
                        FROM ".$db_schema_name."newsletter_subscription A
                        JOIN ".$db_schema_name."newsletter_subtyp_profile C 
                            on A.id_subscription = C.id_subscription
                        JOIN ".$db_schema_name."newsletter_type B
                            on C.id_type = B.id_type 
                        ) t 
                    WHERE seqnum = 1
                    ORDER BY e_mail";

Also notice that I have replace the old comma based join with widely recommended explicit JOIN syntax.