oracle查询时加入条件,Oracle Query：在单个查询中进行条件提取(Oracle Query: Making conditional fetch in single query)...

Oracle Query：在单个查询中进行条件提取(Oracle Query: Making conditional fetch in single query)

我在oracle db中有三个表作为newitems，itemdetails，ticketitems表。 一些虚拟数据如下：

TICKETITEMS：

id ticketid itemid quantity

1 100 9999 2

2 100 9998 5

3 100 2222 3

ITEMDETAILS：

id description col_sumthing extra_col

9999 Marlboro val_sumthing 123_op

9998 Cigar Black val_sumthing 456_pqwe

新东西：

id description col_sumthing

2222 100Pipes val_different

最初我只需要从itemdetails + ticketitems获取数据，使用简单的连接非常容易。 查询是：

SELECT "TI".*, "I"."ID" AS "ITEMID", "I"."DESCRIPTION", "I"."col_sumthing"

FROM "TICKETITEMS" "TI"

INNER JOIN "ITEMDETAILS" "I" ON TI.ITEMID = I.ID

WHERE (TI.TICKET = '100')

这样的事情。

现在引入了newitem表，也可能在ticketitems表中有一些项目。

所以我想要一个结果，如：

Final Result:

id description itemid quantity col_sumthing extra_col

1 Marlboro 9999 2 val_sumthing 123_op

2 Cigar Black 9998 5 val_sumthing 456_pqwe

3 100Pipes 2222 3 val_different

我面临的问题是，当itemdetails中没有找到详细信息时，它应该只检查NEWITEMS。 还欢迎任何其他工作。

I have three tables in oracle db as newitems, itemdetails, ticketitems table. Some dummy data as follow:

TICKETITEMS:

id ticketid itemid quantity

1 100 9999 2

2 100 9998 5

3 100 2222 3

ITEMDETAILS:

id description col_sumthing extra_col

9999 Marlboro val_sumthing 123_op

9998 Cigar Black val_sumthing 456_pqwe

NEWITEMS:

id description col_sumthing

2222 100Pipes val_different

Initially i had to fetch data only from itemdetails + ticketitems which was very easy using simple joins. Query for which was:

SELECT "TI".*, "I"."ID" AS "ITEMID", "I"."DESCRIPTION", "I"."col_sumthing"

FROM "TICKETITEMS" "TI"

INNER JOIN "ITEMDETAILS" "I" ON TI.ITEMID = I.ID

WHERE (TI.TICKET = '100')

Something like that.

Now newitem table introduced which may also have some items present in ticketitems table.

So i want a result like:

Final Result:

id description itemid quantity col_sumthing extra_col

1 Marlboro 9999 2 val_sumthing 123_op

2 Cigar Black 9998 5 val_sumthing 456_pqwe

3 100Pipes 2222 3 val_different

The problem I m facing is, it should only check in NEWITEMS when no details found in itemdetails. Any other work around is also welcomed.

原文：https://stackoverflow.com/questions/11263839

更新时间：2019-12-18 18:38

最满意答案

您可以使用两个OUTER连接：

SELECT ti.*,

nvl(i.id, n.id) itemid,

nvl(i.description, n.description) description,

nvl(i.col_sumthing, n.col_sumthing) col_sumthing

FROM ticketitems ti

LEFT JOIN itemdetails i ON ti.itemid = i.id

LEFT JOIN newsitems n ON ti.itemid = n.id

WHERE ti.ticket = '100'

只要两个详细信息表都将ID作为主键，这将起作用。 如果您有两个以上的详细信息表，请使用COALESCE 。

You could use two OUTER joins:

SELECT ti.*,

nvl(i.id, n.id) itemid,

nvl(i.description, n.description) description,

nvl(i.col_sumthing, n.col_sumthing) col_sumthing

FROM ticketitems ti

LEFT JOIN itemdetails i ON ti.itemid = i.id

LEFT JOIN newsitems n ON ti.itemid = n.id

WHERE ti.ticket = '100'

This will work as long as both detail tables have ID as a primary key. Use COALESCE if you have more than two detail tables.

2012-06-29

相关问答

对于information_schema.COLUMNS ，Oracle的等价物对于所有用户拥有的表的ALL_TAB_COLS或DBA_TAB_COLS拥有的表为USER_TAB_COLS 。 表空间不等同于模式，也不必提供表空间名称。 如果要对特定用户拥有的列OF表查询ALL_TAB_COLS或DBA_TAB_COLS ，则提供模式/用户DBA_TAB_COLS的。 在你的情况下，我会想象这个查询看起来像： String sqlStr= "

SELECT column_name

FROM a

...

原来这个问题是一个矛盾的索引。 该列中有一个旧索引，即v_ssn虚拟列的构建。 我放弃了该索引，查询开始按预期行事。 我仍然关心该索引如何影响查询，但至少我的直接问题得到解决。 谢谢你的帮助！ Turns out the problem was a conflicting index. There was an old index on the column that the v_ssn virtual column is built from. I dropped that index and

...

有两个步骤来获得这个输出：一个连接和一个枢轴。 一个例子： 首先创建您的示例表格： SQL> create table item_master (store_code,item_code,item_desc)

2 as

3 select '011', 914004, 'desccc' from dual

4 /

Table created.

SQL> create table price_master (store_code,item_code,comp_code)

2

...

您可以使用两个OUTER连接： SELECT ti.*,

nvl(i.id, n.id) itemid,

nvl(i.description, n.description) description,

nvl(i.col_sumthing, n.col_sumthing) col_sumthing

FROM ticketitems ti

LEFT JOIN itemdetails i ON ti.itemid = i.id

LEFT JO

...

您可以使用子查询 select * from ( select * from tableName order by id desc ) where ROWNUM <= 4;

You can use a sub query for this like select * from ( select * from tableName order by id desc ) where ROWNUM <= 4;

尝试： UPDATE emp

SET job = (CASE empno

WHEN 7788

THEN 'MANAGER'

WHEN 7902

THEN 'MANAGER'

ELSE NULL

END)

WHERE deptno = 20;

要么 UPDATE emp

SET job

...

此查询使用函数lead()显示stat_cd为Definite所有行以及tran_id的前一行： select row_id, tran_id, tran_date, stat_cd

from (

select data.*,

case when stat_cd='Definite'

or (lead(stat_cd) over (order by tran_id)) = 'Definite' then 1

end

...

您可以使用SQL * Plus或SQLcl来执行脚本。 请记住，您需要用分号终止您的语句： CREATE TABLE test1 (example1 Number, example2 Number);

CREATE TABLE test2 (example1 Number, example2 Number);

或者正斜杠(取决于你正在做什么)： CREATE TABLE test1 (example1 Number, example2 Number)

/

CREATE TABLE test2 (

...

您可以通过connect by(以及返回根节点的列值的函数CONNECT_BY_ROOT来实现所需的结果： SQL> WITH users AS (

2 SELECT 1 user_id, (null) PARENT FROM dual

3 UNION ALL SELECT 2, 1 FROM dual

4 UNION ALL SELECT 3, 1 FROM dual

5 UNION ALL SELECT 4, 3 FROM dual

6 )

...

我相信Oracle相当于： SELECT TOP 1 ID

INTO @FooID

FROM FOO

WHERE Bar = @InputVar ORDER BY ConditionalFlag, ID DESC

是 SELECT ID FROM

( SELECT ID

INTO FooID

FROM FOO

WHERE Bar = InputVar ORDER BY ConditionalFlag, ID DESC

) WHERE ROWNUM = 1;

I beli

...