Ask a question of SQL statements-CodePudding

I want to put out the latest version of the two data should be how to write a statement on the same line?

CodePudding user response:

What result?

CodePudding user response:

reference 1st floor selling fruit net reply:

what result?

Old project department project company new project department for the new project company
4783 362 4771 362

The format of

CodePudding user response:

 with tab1 as (
Select 41 dept, 1 id, 1 nee from dual union all 
Select 42, 1 id, 2 nee from dual union all 
The select 43, 1 id, 3 nee from dual 
) 
Tab2 as (
The select t1. Id, t1. Dept, t1, nee, row_number () over (partition by t1. The id order by t1. Nee desc) rn from tab1 t1) 
The select t1. Id, Max (decode (t1. The rn, 1, t1. Dept, null)) new, Max (decode (t1. Rn, 2, t1. Dept, null)) old from tab2 t1 group by t1. Id 
;

CodePudding user response:

With a left join,,, on the statement, table associated with yourself

CodePudding user response:

Implementation is as follows:

 with TMP as (
Select 4783 as dept_no, 362 as dept_name, 1 as the version from dual union all 
Select 4783, 362, 2 from dual union all 
Select 4771, 362, 3 from dual 
) 
The select Max (decode (t2. Rn, 2, dept_no, null)) as "old project department," 
Max (decode (t2. Rn, 2, dept_name, null)) as "old project company," 
Max (decode (t2) rn, 1, dept_no, null)) as "new project department," 
Max (decode (t2) rn, 1, dept_name, null)) as "new project company" 
The from (select t1. *, row_number () over (partition by t1. Dept_name order by t1. Version desc) as an rn from TMP t1) t2 
Where t2. Rn & lt;=2;

CodePudding user response:

As with TMP (
Select 4783 as dept_no, 362 as dept_name, 1 as the version from dual union all
Select 4783, 362, 2 from dual union all
Select 4771, 362, 3 from dual
)
Select
Max (decode (rn), 2, dept_no, null)), Max (decode (rn), 2, dept_name, null)),
Max (decode (rn), 1, dept_no, null)), Max (decode (rn), 1, dept_name, null))
The from
(select dept_no, dept_name, version, rownum rn from (select dept_no, dept_name, version from TMP order by version desc)
Where rownum & lt;=2)

CodePudding user response:

 with TMP as (
Select 4783 as dept_no, 362 as dept_name, 1 as the version from dual union all 
Select 4783, 362, 2 from dual union all 
Select 4771, 362, 3 from dual 
) 
Select 
Max (decode (rn), 2, dept_no, null)), Max (decode (rn), 2, dept_name, null)), 
Max (decode (rn), 1, dept_no, null)), Max (decode (rn), 1, dept_name, null)) 
The from 
(select dept_no, dept_name, version, rownum rn from (select dept_no, dept_name, version from TMP order by version desc) 
Where rownum & lt;

=2)

CodePudding user response:

The idea is:
1. The first group and sort data using analysis function, the latest and slightly more old than the latest version of the data to give out;
2. Because the number of columns is old and the new four columns, so on the basis of 1, out to the old and new columns to decode function;
3. According to the decode and Max function in grouping to old and new information to find out respectively,
SQL is as follows:
WITH TB1 AS (
SELECT 4783 XMBM, 362 XMGS, 1 XMBB FROM DUAL
UNION ALL
SELECT 4783 XMBM, 362 XMGS, 2 XMBB FROM DUAL
UNION ALL
SELECT 4771 XMBM, 362 XMGS, 3 XMBB FROM DUAL
UNION ALL
SELECT 4766 XMBM, 377 XMGS, 1 XMBB FROM DUAL
UNION ALL
SELECT 4766 XMBM, 377 XMGS, 2 XMBB FROM DUAL
UNION ALL
SELECT 4799 XMBM, 377 XMGS, 3 XMBB FROM DUAL
)
, TB2 AS (
The SELECT x. * FROM
(
SELECT TB1. XMBM TB1. XMGS TB1. XMBB, ROW_NUMBER () OVER (PARTITION BY XMGS ORDER BY XMBB DESC) XH FROM TB1
) X
WHERE 7.0.x.x H & lt;=2
)
SELECT
MAX (DECODE (XH, 2, XMBM, NULL)) old project department,
MAX (DECODE (XH, 2, XMGS, NULL)) old project company,
MAX (DECODE (XMBM XH, 1, NULL)) for the new project department,
MAX (DECODE (XMGS XH, 1, NULL)) for the new project company
The FROM TB2
GROUP BY XMGS

CodePudding user response:

Select
Case the when a. project version=b.n then ew - 1 a. End as old project department, project department
Case the when a. project version=b.n then ew - 1 a. project company end as old project company,
Case the when a. project version=b.n ew then a. project end as new project departments,
Case the when a. project version=b.n ew then a. project company end as new project
The from
The table a,
(select project company, Max (project) as the new from the table group by project company) b
Where a project company=b. project company