个人中心
gpt4 book ai didi

oracle group by id and find the rows not having value(Oracle GROUP BY ID并查找没有值的行)

转载 作者：bug小助手 更新时间：2023-10-24 19:54:55 24 4
gpt4 key购买 nike



Table

表格


Rel_ID SITE_ID   Ack   Added_date
ABC    123        Y    08/09/2023
ABC    123     (null)  08/06/2023
ABC    124     (null)  08/07/2023   
ABC    124     (null)  08/06/2023
ABC    124       N     08/05/2023
ABC    125       Y     07/06/2023
ABC    125       Y     07/07/2023
ABC    126      (null)  09/08/2023
ABC    126      (null)  09/09/2023
ABC    127      (null)  05/08/2023
ABC    127       N      05/09/2023

If a site_id is acknowledged (ack='Y') then it should not be listed otherwise if ack is null or ack is 'N' then it should be listed for Rel_id based on the added_date ascending

如果站点ID已确认(ack=‘Y’)，则不应列出该站点ID；否则，如果ack为空或ack为‘N’，则应根据添加的日期升序将其列为rel_id


Resultset

结果集


Rel_ID SITE_ID   Ack   Added_date
ABC    124       N     08/05/2023
ABC    126      (null)  09/08/2023
ABC    127      (null)  05/08/2023

What i tried is below

我尝试的内容如下


SELECT * FROM (SELECT RR.*, RANK() OVER (PARTITION BY RR.Rel_ID ,RR.SITE_ID 
ORDER BY (CASE WHEN decode(ack, null, 'N', ack) = 'Y' then 1 else 2 end) asc, 
RR.CREATE_DTE asc) AS RANK FROM  Address_data  RR WHERE (ack is null or ack= 'N')) RS 
WHERE RANK = 1

更多回答
优秀答案推荐

You can use ROW_NUMBER, rather than RANK, and can add a filter to the outer query:

您可以使用ROW_NUMBER而不是RANK，并且可以向外部查询添加筛选器：


SELECT *
FROM   (
  SELECT a.*,
         ROW_NUMBER() OVER (
           PARTITION BY rel_id, site_id
           ORDER BY CASE WHEN ack = 'Y' THEN 1 ELSE 2 END, added_date
         ) AS rn
  FROM   address_data a
)
WHERE  rn = 1
AND    (ack IS NULL OR ack != 'Y');

Which, for the sample data:

其中，对于样本数据：


CREATE TABLE address_data (Rel_ID, SITE_ID, Ack, Added_date) AS
SELECT 'ABC', 123, 'Y',  DATE '2023-08-09' FROM DUAL UNION ALL
SELECT 'ABC', 123, NULL, DATE '2023-08-06' FROM DUAL UNION ALL
SELECT 'ABC', 124, NULL, DATE '2023-08-07' FROM DUAL UNION ALL   
SELECT 'ABC', 124, NULL, DATE '2023-08-06' FROM DUAL UNION ALL
SELECT 'ABC', 124, 'N',  DATE '2023-08-05' FROM DUAL UNION ALL
SELECT 'ABC', 125, 'Y',  DATE '2023-07-06' FROM DUAL UNION ALL
SELECT 'ABC', 125, 'Y',  DATE '2023-07-07' FROM DUAL UNION ALL
SELECT 'ABC', 126, NULL, DATE '2023-09-08' FROM DUAL UNION ALL
SELECT 'ABC', 126, NULL, DATE '2023-09-09' FROM DUAL UNION ALL
SELECT 'ABC', 127, NULL, DATE '2023-05-08' FROM DUAL UNION ALL
SELECT 'ABC', 127, 'N',  DATE '2023-05-09' FROM DUAL;

Outputs:

产出：




































REL_ID SITE_ID ACK ADDED_DATE RN
ABC 124 N 2023-08-05 00:00:00 1
ABC 126 null 2023-09-08 00:00:00 1
ABC 127 null 2023-05-08 00:00:00 1


fiddle

小提琴


更多回答

24 4 0
文章推荐： How to change the unit of timestamp from milliseconds to microseconds in Apache IoTDB?(如何在ApacheIoTDB中将时间戳的单位从毫秒改为微秒？)
文章推荐： yocto how to add a error handler for a specified task?(Yocto如何为指定任务添加错误处理程序？)
文章推荐： I am having a problem trying to do a XOR with 3 variables(我在尝试对3个变量进行XOR运算时遇到了问题)
文章推荐： Using Square Brackets in Class Names with Pug and Tailwind CSS(在带有PUG和TailWind CSS的类名中使用方括号)
bug小助手
个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章
滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com