我想把最新版本的兩條資料取出來放到同一行上應該怎么寫陳述句呢？
uj5u.com熱心網友回復：
結果要求什么樣？uj5u.com熱心網友回復：
舊專案部門 舊專案公司 新專案部門 新專案公司
4783 362 4771 362
這種格式的
uj5u.com熱心網友回復：
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
;
uj5u.com熱心網友回復：
用left join，，，on陳述句，表自己關聯自己uj5u.com熱心網友回復：
實作如下：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;
uj5u.com熱心網友回復：
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 )
uj5u.com熱心網友回復：
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 )
uj5u.com熱心網友回復：
思路其實是：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
uj5u.com熱心網友回復：
selectcase 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.專案公司
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/46490.html
標籤：開發
下一篇：輔助分類賬