A recent project doing migration, from essentially to oracle, the following statement was in trouble,

Don't know how to write the



Select
C.s. kss_swjg_dm (select top (1)
The from c
Where c.n SRSBH=b.n SRSBH
The order by c.s. wdjblx_dm)
From a, b
Where a.n srdzdah=b.n srdzdah
Group by b.n SRSBH




The subquery
C.s. kss_swjg_dm (select top (1)
The from c
Where c.n SRSBH=b.n SRSBH
The order by c.s. wdjblx_dm)

Execution order is
C a table table first, and then press swdjblx_dm sorting, deal with good results in the first row,


1. The first oracle select inside the back of the subquery can't order by operation
The order by c.s. wdjblx_dm complains

2. ORACLE does not have the concept of TOP, only rownum, rownum and TOP, TOP is judgment again after check out results, rownum if you want to achieve and the same effect as the TOP can only make out a layer, so there will be a problem is the set of a layer of the where after citigroup SRSBH=b.n SRSBH B table here to identify an item,
The select k. skss_swjg_dm from
(select c.s. kss_swjg_dm
The from c
Where c.n SRSBH=b.n SRSBH
The order by c.s. wdjblx_dm) k
Where rownum=1

CodePudding user response:

Questions quite easy, but to the results of the query field, none said, this is the biggest problem,
I only use "related fields" instead of here, you need to fill in which field, which field

 
Select a table fields related field,,,, b table fields related field 
From a, 
Related fields (select c table, table b related fields 
The from (select c.s. kss_swjg_dm, row_number () over (order by c.s. wdjblx_dm) rn 
The from c, b 
Where c.n SRSBH=b.n SRSBH 
) 
Where an rn=1 
B) 
Where a.n srdzdah=b.n srdzdah 

CodePudding user response:

Pay attention to the question of method, the biggest problem is that did not mention what kind of results!

CodePudding user response:

If we can only take a field in the subquery minimum value, you can use oracle one aggregation function of min (field name)