请教一个sql语句问题

[木 全]

我想把最新版本的两条数据取出来放到同一行上应该怎么写语句呢？

[卖水果的net]

结果要求什么样？

[木 全]

引用 1 楼 卖水果的net 的回复:

结果要求什么样？

旧项目部门 旧项目公司 新项目部门 新项目公司 4783 362 4771 362 这种格式的

[nayi_224]

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

[lysxh]

用left join，，，on语句，表自己关联自己

[碧水幽幽泉]

实现如下：

with tmp as (
select 4783 as dept_no,  362 as dept_name, 1 as version from dual union all
select 4783, 362, 2  from dual union all
select 4771, 362, 3 from dual 
)
select max(decode(t2.rn,2,dept_no,null)) as "旧项目部门",
           max(decode(t2.rn,2,dept_name,null)) as "旧项目公司",
           max(decode(t2.rn,1,dept_no,null)) as "新项目部门",
           max(decode(t2.rn,1,dept_name,null)) as "新项目公司"
 from (select  t1.*,  row_number() over(partition by t1.dept_name order by t1.version desc) as rn  from tmp t1) t2
where t2.rn <= 2;
 

[~沉O淀~]

with tmp as (
select 4783 as dept_no, 362 as dept_name, 1 as 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))
from
(select dept_no,dept_name,version,rownum rn from (select dept_no,dept_name,version from tmp order by version desc)
where rownum <=2 )

[~沉O淀~]

with tmp as (
select 4783 as dept_no,  362 as dept_name, 1 as 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))
from 
(select dept_no,dept_name,version,rownum rn from (select dept_no,dept_name,version from tmp order by version desc)
 where rownum <=2 )

[tensung10]

思路其实是： 1.先将数据用分析函数进行分组和排序，把最新的和比最新稍微旧一版本的数据给取出来； 2.因为列数是新旧4列，所以在1的基础上，用decode函数将新旧列给取出来； 3.最后根据decode和max函数结合分组将新旧信息给分别查出。 SQL如下： 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( 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 X.XH <=2 ) SELECT MAX(DECODE(XH, 2, XMBM, NULL)) 旧项目部门, MAX(DECODE(XH, 2, XMGS, NULL)) 旧项目公司, MAX(DECODE(XH, 1, XMBM, NULL)) 新项目部门, MAX(DECODE(XH, 1, XMGS, NULL)) 新项目公司 FROM TB2 GROUP BY XMGS

[wildwolv]

select case when a.项目版本 = b.new - 1 then a.项目部门 end as 旧项目部门, case when a.项目版本 = b.new - 1 then a.项目公司 end as 旧项目公司, case when a.项目版本 = b.new then a.项目部门 end as 新项目部门, case when a.项目版本 = b.new then a.项目公司 end as 新项目公司 from table a, (select 项目公司,max(项目版本) as new from table group by 项目公司) b where a.项目公司 = b.项目公司