在连接上使用 Order By 和 Distinct (PLSQL)

发布于 2025-01-07 17:09:33 字数 2024 浏览 0 评论 0原文

我在一些表上编写了一个联接,并使用两个级别的排序对数据进行了排序 - 其中之一是一个表的主键。

现在,对这些数据进行排序后,我想使用内联视图和 DISTINCT 子句从数据中排除任何重复项 - 这就是我遇到困难的地方。

我似乎能够对数据进行排序或区分它,但不能同时进行这两者。有没有办法解决这个问题,或者我偶然发现了不确定性原理的 SQL 等价物?

此代码返回已排序的数据,但有重复项

                SELECT 
                    ada.source_tab source_tab
                  , ada.source_col source_col
                  , ada.source_value source_value
                  , ada.ada_id ada_id
                FROM 
                    are_aud_data ada
                  , are_aud_exec_checks aec
                  , are_audit_elements ael
                WHERE 
                      aec.aec_id = ada.aec_id
                  AND ael.ano_id = aec.ano_id
                  AND aec.acn_id = 123456
                  AND ael.ael_type = 1
                ORDER BY 
                  CASE
                    WHEN source_tab = 'Tab type 1' THEN 1
                    WHEN source_tab = 'Tab type 2' THEN 2
                    ELSE 3
                  END
              ,ada.ada_id ASC;

此代码删除了重复项,但我丢失了顺序...

SELECT DISTINCT source_tab, source_col, source_value FROM (
                SELECT 
                    ada.source_tab
                  , ada.source_col source_col
                  , ada.source_value source_value
                  , ada.ada_id ada_id
                FROM 
                    are_aud_data ada
                  , are_aud_exec_checks aec
                  , are_audit_elements ael
                WHERE 
                      aec.aec_id = ada.aec_id
                  AND ael.ano_id = aec.ano_id
                  AND aec.acn_id = 123456
                  AND ael.ael_type = 1
                ORDER BY 
                  CASE
                    WHEN source_tab = 'Tab type 1' THEN 1
                    WHEN source_tab = 'Tab type 2' THEN 2
                    ELSE 3
                  END
              ,ada.ada_id ASC
     )
;

如果我尝试在外部选择的末尾包含“ORDER BY ada_id”,我会收到错误消息“ORA-” 01791:不是一个选定的表达式'这让我很生气!

I have written a join on some tables and I have ordered the data using two levels of ordering - one of which is the primary key of one table.

Now, with this data sorted I want to then exclude any duplicates from my data using an in-line view and the DISTINCT clause - and this is where I am coming unstuck.

I seem to be able to either sort the data OR distinct it, but never both at the same time. Is there a way around this or have I stumbled upon the SQL equivalent of the uncertainty principle?

This code returns the data sorted, but with duplicates

                SELECT 
                    ada.source_tab source_tab
                  , ada.source_col source_col
                  , ada.source_value source_value
                  , ada.ada_id ada_id
                FROM 
                    are_aud_data ada
                  , are_aud_exec_checks aec
                  , are_audit_elements ael
                WHERE 
                      aec.aec_id = ada.aec_id
                  AND ael.ano_id = aec.ano_id
                  AND aec.acn_id = 123456
                  AND ael.ael_type = 1
                ORDER BY 
                  CASE
                    WHEN source_tab = 'Tab type 1' THEN 1
                    WHEN source_tab = 'Tab type 2' THEN 2
                    ELSE 3
                  END
              ,ada.ada_id ASC;

This code removes the duplicates, but I lose the order...

SELECT DISTINCT source_tab, source_col, source_value FROM (
                SELECT 
                    ada.source_tab
                  , ada.source_col source_col
                  , ada.source_value source_value
                  , ada.ada_id ada_id
                FROM 
                    are_aud_data ada
                  , are_aud_exec_checks aec
                  , are_audit_elements ael
                WHERE 
                      aec.aec_id = ada.aec_id
                  AND ael.ano_id = aec.ano_id
                  AND aec.acn_id = 123456
                  AND ael.ael_type = 1
                ORDER BY 
                  CASE
                    WHEN source_tab = 'Tab type 1' THEN 1
                    WHEN source_tab = 'Tab type 2' THEN 2
                    ELSE 3
                  END
              ,ada.ada_id ASC
     )
;

If I try and include 'ORDER BY ada_id' at the end of the outer select, I get the error message 'ORA-01791: not a SELECTed expression' which is infuriating me!!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

找回味觉 2025-01-14 17:09:33

为什么不在外部查询的选定字段中包含 ada_id

Why don't you include ada_id at the selected fields of the outer query?

云裳 2025-01-14 17:09:33
;WITH CTE AS
(
                  SELECT 
                    ada.source_tab source_tab
                  , ada.source_col source_col
                  , ada.source_value source_value
                  , ada.ada_id ada_id
                  , ROW_NUMBER() OVER (PARTITION BY [COLUMNS_YOU_WANT TO BE DISTINCT] 
                    ORDER BY [your_columns])  rn
                FROM 
                    are_aud_data ada
                  , are_aud_exec_checks aec
                  , are_audit_elements ael
                WHERE 
                      aec.aec_id = ada.aec_id
                  AND ael.ano_id = aec.ano_id
                  AND aec.acn_id = 356441
                  AND ael.ael_type = 1
                ORDER BY 
                  CASE
                    WHEN source_tab = 'Licensed Inventory' THEN 1
                    WHEN source_tab = 'CMDB'               THEN 2
                    ELSE 3
                  END
              ,ada.ada_id ASC
)
select * from CTE WHERE rn<2
;WITH CTE AS
(
                  SELECT 
                    ada.source_tab source_tab
                  , ada.source_col source_col
                  , ada.source_value source_value
                  , ada.ada_id ada_id
                  , ROW_NUMBER() OVER (PARTITION BY [COLUMNS_YOU_WANT TO BE DISTINCT] 
                    ORDER BY [your_columns])  rn
                FROM 
                    are_aud_data ada
                  , are_aud_exec_checks aec
                  , are_audit_elements ael
                WHERE 
                      aec.aec_id = ada.aec_id
                  AND ael.ano_id = aec.ano_id
                  AND aec.acn_id = 356441
                  AND ael.ael_type = 1
                ORDER BY 
                  CASE
                    WHEN source_tab = 'Licensed Inventory' THEN 1
                    WHEN source_tab = 'CMDB'               THEN 2
                    ELSE 3
                  END
              ,ada.ada_id ASC
)
select * from CTE WHERE rn<2
白首有我共你 2025-01-14 17:09:33

看来 ada_id 在外部查询中没有意义。
您已删除所有这些值,将其归结为不同的 source_tab 和 source_col ...

您期望的顺序是什么?

您可能希望每个表和列集的最小 ada_id 作为订单的驱动程序 - (尽管表名称似乎适合我)

在内部查询中包含最小 ada_id (您需要一个 group by 子句)
然后在外部查询中引用它并对其进行排序。

it seems that the ada_id is meaningless in the outer query.
you have removed all those values to boil it down to the distinct source_tab and source_col...

what would you expect the order to be?

you want maybe the minimum ada_id for each table and column set to be the driver for the order - (although the table name seems appropriate to me)

include the minimum ada_id in the inner query (you'll need a group by clause)
then reference that in the outer query and sort on it.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文