Oracle PHP只显示一次

有两个表:</ p>

</ a> </ p>

</ p>

  $ 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“;
</ code> </ pre>

如果我运行此查询 对于id_subscription 734,它显示3次。 </ p>

如何只显示一次?</ p>
</ div>

展开原文

原文

There are two tables:

second table

first table

$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?

doujing8435
doujing8435 没有PHP,问题会更清楚。此外,您有三个表而不是两个,并且不清楚哪个表具有e_mail列。
3 年多之前 回复
doufu8588
doufu8588 如果我为id_subscription734运行此查询,它将显示3次。我该怎么做只显示一次。sry,我出去,明白你想要什么。
3 年多之前 回复
douchuifk90315
douchuifk90315 是的,但只显示我id_subscription女巫有id_type=1。如果我有一个id_type=2id_type=3的id_subscription755,它将不会显示
3 年多之前 回复
duanchuli5647
duanchuli5647 所以你的SQL工作正常。测试C.id_type=B.id_type和C.id_type=1,您将得到一个结果。
3 年多之前 回复
dongtan1009
dongtan1009 对于id_subscription734,第二个表中有3个条目。
3 年多之前 回复
dongyue5686
dongyue5686 第二个表(实际)中是否有多个734的条目?
3 年多之前 回复
duanlei20082008
duanlei20082008 是id_subscription最多可以有5个id_type
3 年多之前 回复
doubu5154
doubu5154 看看GROUPBYid_subscription函数
3 年多之前 回复
dongshao1021
dongshao1021 C.id_type=B.id_type应该只产生一个,如何在第一个表中为734提供3个条目?id_subscription可以有多个type_id吗?很不清楚。第二个表中734的当前type_id是什么?第二个表中是否有超过734的条目?你从sql获得的当前结果是什么?
3 年多之前 回复
doutan2111
doutan2111 你确定你没有在HTML标记中多次输出它吗?
3 年多之前 回复

2个回答

Short answer: you are getting one row per newsletter_subtyp_profile. Subscription 734 is linked to three newsletter types, hence three rows of output.

You have three tables, not two, and the question would be clearer if you included full descriptions and sample data, and also got rid of the irrelevant PHP aspect and focussed on the SQL.

With some detective work, I make it this:

create table newsletter_subscription
( id_subscription         integer primary key
, last_newsletter_modify  date
, e_mail                  varchar2(50) not null );

create table newsletter_type
( id_type                 integer primary key
, description             varchar2(40) not null unique );

create table newsletter_subtyp_profile
( id_subscription         references newsletter_subscription
, id_type                 references newsletter_type
, constraint nsp_pk       primary key (id_type,id_subscription) );

insert into newsletter_subscription values (600, date '2017-01-10', 'someone@somewhere.net');
insert into newsletter_subscription values (734, date '2017-02-05', 'someone@somewhereelse.net');
insert into newsletter_subscription values (800, date '2017-03-01', 'nobody@nowherewhere.net');

insert into newsletter_type values (1, 'Type One');
insert into newsletter_type values (2, 'Type Two');
insert into newsletter_type values (3, 'Type Three');

insert into newsletter_subtyp_profile values (734, 1);
insert into newsletter_subtyp_profile values (734, 2);
insert into newsletter_subtyp_profile values (734, 3);

Now run your query (I shortened the select list to simplify the output, and added b.description - a dummy column as I don't know what other columns you have on newsletter_type):

select a.id_subscription, a.e_mail
     , to_char(a.last_newsletter_modify, 'DD/MM/YYYY') as last_newsletter_modify2
     , b.description
from   newsletter_subscription a,
       newsletter_type b,
       newsletter_subtyp_profile c
where  a.id_subscription = c.id_subscription and
       c.id_type = b.id_type
order by a.e_mail, c.id_type;

ID_SUBSCRIPTION E_MAIL                     LAST_NEWSLETTER_MODIFY2 DESCRIPTION
--------------- -------------------------- ----------------------- ---------------------
            734 someone@somewhereelse.net  05/02/2017              Type One
            734 someone@somewhereelse.net  05/02/2017              Type Two
            734 someone@somewhereelse.net  05/02/2017              Type Three

btw the logic would be clearer if you used mnemonic aliases such as sub instead of a for newsletter_subscription, and also used standard ANSI joins and lost the uppercase:

select sub.id_subscription, sub.e_mail
     , to_char(sub.last_newsletter_modify, 'DD/MM/YYYY') as last_newsletter_modify
     , typ.description
from   newsletter_subscription sub
       join newsletter_subtyp_profile pro on pro.id_subscription = sub.id_subscription
       join newsletter_type typ on typ.id_type = pro.id_type
where  sub.id_subscription = 734
order  by sub.e_mail, pro.id_type;



您可以使用窗口函数 row_number </ code>为每个id_subscription获取一行max(如果更改,则为min_type) 函数中的order by子句由按id_type </ code>排序:</ p>

  $ inner_query =“SELECT * 
FROM(
SELECT SELECT * * ,
ROWNUM作为RN,
TO_CHAR(A.last_newsletter_modify,'DD / MM / YYYY')AS LAST_NEWSLETTER_MODIFY2,
row_number()over(
分区由C.id_subscription
顺序由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“;
</ code> </ pre>

另请注意我 用广泛推荐的显式JOIN语法替换旧的基于逗号的连接。</ p>
</ div>

展开原文

原文

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.

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐