Oracle - 为每个唯一列值生成唯一行并将行转换为列
Oracle 11g R2正在使用中。这是我的源表:Oracle - 为每个唯一列值生成唯一行并将行转换为列
ASSETNUM WONUM WODATE TYPE1 TYPE2 LOCATION
--------------------------------------------------------
W1 1001 2015-10-10 N N loc1
W1 1002 2015-10-02 Y N loc2
W1 1003 2015-10-04 Y N loc2
W1 1004 2015-10-05 N Y loc2
W1 1005 2015-10-07 N Y loc2
W2 2001 2015-10-11 N N loc1
W2 2002 2015-10-03 Y N loc2
W2 2003 2015-10-02 Y N loc2
W2 2004 2015-10-08 N Y loc3
W2 2005 2015-10-06 N Y loc3
http://sqlfiddle.com/#!4/8ee297/1
我想编写一个查询得到以下数据:
ASSETNUM LATEST LOCATION for LATEST_WODATE_FOR LATEST_WODATE_FOR
WODATE LATEST WODATE TYPE1=Y TYPE2=Y
----------------------------------------------------------------------------
W1 2015-10-10 loc1 2015-10-04 2015-10-07
W2 2015-10-11 loc1 2015-10-03 2015-10-08
我需要只有一行类似的结果集为每个独特的价值ASSETNUM。
任何帮助,将不胜感激!
分析功能救援。
http://sqlfiddle.com/#!4/8ee297/4
select assetnum,
wodate,
wonum,
location,
last_type1_wodate,
last_type2_wodate
from(select assetnum,
wodate,
wonum,
location,
rank() over (partition by assetnum order by wodate desc) rnk_wodate,
max(case when type1 = 'Y' then wodate else null end)
over (partition by assetnum) last_type1_wodate,
max(case when type2 = 'Y' then wodate else null end)
over (partition by assetnum) last_type2_wodate
from t)
where rnk_wodate = 1
走过那是什么做
-
rank() over (partition by assetnum order by wodate desc)
通吃行特定assetnum
和wodate
排序。外部where rnk_wodate = 1
上的谓词仅返回最近一行。如果可能存在关系,则可能需要使用dense_rank
或row_number
来取代rank
,具体取决于您希望如何处理关系。 -
max(case when type1 = 'Y' then wodate else null end) over (partition by assetnum)
获取特定assetnum
的所有行,并找到最大化case
表达式的值。那将是最后一排type1 = 'Y'
那assetnum
。
我认为这个概念最简单的方法是简单地查看您的问题,为3个独立的查询,其中每个做一个GROUP BY
获得一些具体的东西(最新WODATE
,最新WODATE
的类型1,以及最新的WODATE
对于Type2)。这些查询可以轻松地连接在一起,为您提供所需的输出。
SELECT T.ASSETNUM, t1.LATEST_WODATE, T.LOCATION, t2.LATEST_WODATE_TYPE1,
t3.LATEST_WODATE_TYPE2
FROM T INNER JOIN
(
SELECT ASSETNUM, MAX(WODATE) AS LATEST_WODATE
FROM T
GROUP BY ASSETNUM
) t1
ON T.ASSETNUM = t1.ASSETNUM AND T.WODATE = t1.LATEST_WODATE
INNER JOIN
(
SELECT ASSETNUM, MAX(WODATE) AS LATEST_WODATE_TYPE1
FROM T
WHERE TYPE1 = 'Y'
GROUP BY ASSETNUM
) t2
ON T.ASSETNUM = t2.ASSETNUM
INNER JOIN
(
SELECT ASSETNUM, MAX(WODATE) AS LATEST_WODATE_TYPE2
FROM T
WHERE TYPE2 = 'Y'
GROUP BY ASSETNUM
) t3
ON T.ASSETNUM = t3.ASSETNUM
点击下面的链接,运行演示:
感谢您的回答,您是对的,从概念上讲,这个答案是最容易遵循的。 – squashbuff
另一个答案,即使有点难以遵循,更适合我的要求。我已将其他答案标记为已接受的答案。对于那个很抱歉。 – squashbuff
使用聚合函数first,
查询:
select assetnum,
max(wodate),
max(wonum) keep (dense_rank first order by wodate desc) wonum,
max(case when type1 = 'Y' then wodate end) last_type1_wodate,
max(case when type2 = 'Y' then wodate end) last_type2_wodate
from t
group by
assetnum
| ASSETNUM | MAX(WODATE) | WONUM | LAST_TYPE1_WODATE | LAST_TYPE2_WODATE |
|----------|---------------------------|-------|---------------------------|---------------------------|
| W1 | October, 10 2015 00:00:00 | 1001 | October, 04 2015 00:00:00 | October, 07 2015 00:00:00 |
| W2 | October, 11 2015 00:00:00 | 2001 | October, 03 2015 00:00:00 | October, 08 2015 00:00:00 |
(dense_rank) (first) (order by wodate desc)
( 2 ) ( 3 ) ( 1 )
- 顺序在每个assetnum降序(如在指定GROUP BY子句)的日期。
- 将dense_rank赋值给它们。
- 只选择第一条记录。
在您的示例数据中,这将只选择单个记录。对应最近的日期。 但是你不能直接选择wonum,因为你使用的是GROUP BY子句。所以你必须使用一个聚合函数,它可以是MIN,MAX,SUM等。它只存在于语义上。
谢谢。你能详细解释一下这个查询吗?我不确定为什么在这里使用'max(wonum)'。 – squashbuff
谢谢,我该如何在这个查询中添加'location'? – squashbuff
max(location)keep(dense_rank first order by wodate desc) – Noel
这太棒了!谢谢你的回答。它完全符合我的要求!我只需要在select查询中添加'location'并且它工作正常。这绝对是我的首选答案,即使它有点难以理解。非常感谢演练! – squashbuff