如何从结果中删除一行

发布于 2025-02-13 09:38:54 字数 331 浏览 0 评论 0原文

假设我有此查询：

 select item_display_name 
 from all_requirements 
 where state = 'submitted_pending' and id = 'mNAr9viL4='

这显示了两个结果/行：

'id proof'
'id proof'

因为all_requirements lime列中的一个值之一是不同的。

我希望查询仅返回ID证明一次。我该怎么做？

原文

Suppose I have this query:

 select item_display_name 
 from all_requirements 
 where state = 'submitted_pending' and id = 'mNAr9viL4='

This displays two results/rows:

'id proof'
'id proof'

because one of the values in column document_id of all_requirements is different.

I want the query to only return id proof once. How can I do it?

分享到QQ

分享到微博

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

发布评论

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

野生奥特曼 2025-02-20 09:38:54

为您的Select语句添加独特的使用，可与1列一起使用。

SELECT DISTINCT ITEM_DISPLAY_NAME 
FROM ALL_REQUIREMENTS 
WHERE
    STATE = 'submitted_pending'
    AND id = 'mNAr9viLD4='

如果要包含document_id列或其他列，则可以使用以下代码返回每个唯一的item_display_name的记录。按ASC顺序排序的订单将为您提供具有最低文档的记录，但是您可以将其更改为DESC订单，以为您提供具有最高文档的记录。

SELECT
    R.ITEM_DISPLAY_NAME,
    R.document_id
FROM
(
    SELECT
        ITEM_DISPLAY_NAME,
        document_id,
        ROW_NUMBER() OVER (Partition BY ITEM_DISPLAY_NAME ORDER BY document_id ASC) AS 'Ranks'
    FROM ALL_REQUIREMENTS
    WHERE
        STATE = 'submitted_pending'
        AND id = 'mNAr9viLD4='
) R
WHERE Ranks = 1

Adding DISTINCT to your Select statement works with 1 column.

SELECT DISTINCT ITEM_DISPLAY_NAME 
FROM ALL_REQUIREMENTS 
WHERE
    STATE = 'submitted_pending'
    AND id = 'mNAr9viLD4='

If you want to include the document_id column or other columns, then you can use the following code to return 1 record for each unique Item_Display_Name. The Order By statement sorted in ASC order will give you the record with the lowest document_ID, but you can change it to DESC order to give you the one record with the highest document_ID.

SELECT
    R.ITEM_DISPLAY_NAME,
    R.document_id
FROM
(
    SELECT
        ITEM_DISPLAY_NAME,
        document_id,
        ROW_NUMBER() OVER (Partition BY ITEM_DISPLAY_NAME ORDER BY document_id ASC) AS 'Ranks'
    FROM ALL_REQUIREMENTS
    WHERE
        STATE = 'submitted_pending'
        AND id = 'mNAr9viLD4='
) R
WHERE Ranks = 1

回复收藏 0 原文

~没有更多了~